how to know when mysql INSERT is finished (multipl

Question

I have a multiple db connection situation like this:

db connection 1: INSERT xyz
(very short time passes)
db connection 2: SELECT [looking for xyz]

Sometimes the SELECT finds xyz and sometimes it does not (because it is on a different db connection than the INSERT). If I put a sleep(1) after the INSERT then the SELECT always finds xyz.

For db connection 1, how can I make it wait until the INSERT has finished and the new row is available for SELECTs running on another db connection?

My table is innodb. The use case is inserting an authenticated session ID on connection 1, then redirecting to an authenticated page, and then when the request for the authenticated page comes in (on another connection) we look for the session ID to authenticate the request. It's okay if we slow down login a little and make it wait until the INSERT has completely finished, so that the authenticated session ID is available to other requests before returning.

Answer 1

Because the insert statement is a transaction; once your insert statement has finished, it is in the database; I assume these two connections to the databases are made using two different threads; and you are issuing both commands simultaniously (at which time, there is no xyz inserted yet.

given the use case you describe, i'm unsure if this should even go over two different database connections. however; should above all prove to be false; you might want to use a lock file or something to indicate the 'transaction' is truly finished.

Answer 2

First question, why do you have two different connections for the same user ?

If you really want to keep two sessions, you can play around with table locking.

Connection 1, send these sql statement :

LOCK TABLE mytable WRITE; -- mytable is know locked, nobody else can access it
INSERT xyz; -- insert data in database
UNLOCK TABLES; -- unlock the table, the rows ARE inserted

Connection 2 :

SELECT [looking for xyz]

If the connection 2 try to access the database before the connection 1 has unlocked the table, the connection will have to wait. When the table are unlocked, the row will be inserted, so the select will return the wanted result.