CODEWITHSUNDEEP
mysqlselectinsert
Mike Scanlin
Mike Scanlin

Reputation: 61

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

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.

Upvotes: 5

Views: 2967

Answers (2)

krtek
krtek

Reputation: 26597

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.

Upvotes: 2

nido
nido

Reputation: 531

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.

Upvotes: 0

Related Questions