How to know when mysql INSERT is finished (multiple connections)

Question

I have a situation with multiple db connection:

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

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

To connect db 1, how can I make it wait for INSERT end and a new line is available for SELECTs running on another db connection?

My innodb table. A use case is to insert the identifier of the authenticated connection 1, then redirect to the authentication page, and then, when the request for the authenticated page arrives (in another connection), we look for the session identifier for authenticating the request. This is normal if we slow down the login a bit and do it until the INSERT is completely completed so that the identifier of the verified one is available to other requests before returning.

Answer 1

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

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

Connection 1, send these sql instructions:

 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 

Compound 2:

 SELECT [looking for xyz] 

If connection 2 tries to access the database before connection 1 unlocks the table, connection will have to wait. When the table is unlocked, the row will be inserted, so the selection will return the desired result.

Answer 2

Because the insert statement is a transaction; as soon as your insert statement completes, it is in the database; I assume that these two database connections are created using two different threads; and you simultaneously execute both commands (at this time xyz is not yet inserted.

given your use case, I'm not sure if this should go through even two different database connections. But; must be primarily false; you can use a lock file or something to indicate that the transaction has actually completed.