unwanted insert delay

Hi,

I am doing an INSERT (not delayed) query with C API, then starting
another program thread, the thread reads the data that was just inserted
with a SELECT query via C Api.

This worked fine as long as there is just one thread running.

With 2 threats it sometimes happens that the data is not there in that
short time.

Inserting a delay of 1 second before the SELECT query solves the problem
for now.

But actually I don't do an "INSERT DELAYED" and wonder why the data is
not there immediately.

I read that an insert waits until no more selects are running but that
should cause a delay when doing the "INSERT" query

Does the C Api not wait for an insert to be finished?

Tobias Bergmann wrote:
> Hi,
>
> I am doing an INSERT (not delayed) query with C API, then starting
> another program thread, the thread reads the data that was just inserted
> with a SELECT query via C Api.
>
> This worked fine as long as there is just one thread running.
>
> With 2 threats it sometimes happens that the data is not there in that
> short time.
>
> Inserting a delay of 1 second before the SELECT query solves the problem
> for now.
>
> But actually I don't do an "INSERT DELAYED" and wonder why the data is
> not there immediately.
>
> I read that an insert waits until no more selects are running but that
> should cause a delay when doing the "INSERT" query
>
> Does the C Api not wait for an insert to be finished?
>
>

myisam tables use table level locking. so if you're doing an insert, the
table will be locked for the duration of the insert (no matter how short
that period may be). once the lock is removed, any other statements can
be executed against that table. table level locking has limitations and
this is one of those limitations. now, you can convert your table to
innodb and utilize what is called row-level locking where you can insert
as many records as you like and the engine won't lock the rest of your
rows while it's inserting new rows.

you also need to look at isolation levels which is specified in the .cnf
or .ini file by the statement transaction-isolation.

hope this helps.

I have to say, using a database to synchronize data between 2 programs
has more problems than the one you illustrated. A database is for
storing data, IMHO, not transferring from one user to another.