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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With