CODEWITHSUNDEEP
mysqlselectsql-update
John James
John James

Reputation: 657

Block MySQL reads while updating

I have two simple queries one reading and one updating a table. How can I lock the read for the select query while the update one is running. Right now in MySQL InnoDB I noticed that the write/update is by default locked but the read/select is getting the old data before the transaction.

I tried using a transaction in the update query and then SELECT ... FOR UPDATE - outside of the transaction - but it didnt seem to do the trick. Also I was wondering, for testing purposes, how to slow down the UPDATE query. I came across SLEEP(X), but I dont know how to implement it inside the update query.

How can I make every query wait read/write until a write is completed.

Upvotes: 2

Views: 4572

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

Using a READ-COMMITTED transaction will view the latest committed. changes, and using SELECT ... LOCK IN SHARE MODE will make the read block until any outstanding updates are committed.

Try this. In one screen, start a transaction and an UPDATE. No SLEEP() required, just don't commit the transaction. The lock created by the UPDATE will continue to exist until you commit.

BEGIN;
UPDATE MyTable SET something = '1234' WHERE id = 3;

Don't commit yet.

Meanwhile, in a second screen, set your transaction isolation to read-committed transaction. No need to start an explicit transaction, because InnoDB queries use a transaction, even if it's auto-commit.

SET tx_isolation='READ-COMMITTED';

SELECT * FROM MyTable WHERE id = 3 LOCK IN SHARE MODE;
<hangs>

The LOCK IN SHARE MODE makes it wait because there's still an outstanding exclusive lock created by the UPDATE.

In the first screen:

COMMIT;

In the second screen, voila! The blocking read becomes unblocked, and you immediately see the result of the UDPATE without having to refresh your transaction.

Upvotes: 3

Related Questions