Prevent MySQL from reading when updating

Question

I have two simple queries, one to read the table and one to update the table. How to lock the read of a select query while the update query is running. Now in MySQL InnoDB, I noticed that writes/updates are locked by default, but reads/selects get old data before the transaction.

I tried using a transaction in the update query and then SELECT ... FOR UPDATE - outside the transaction - but it didn't seem to be successful. Also, for testing purposes, I'd like to know how to slow down UPDATE queries. I came across SLEEP(X) but I don't know how to implement it in an update query.

How to make each query wait for read/write until the write is completed.

Answer 1

Using READ-COMMITTED transactions will view the latest committed transaction. changes, and using SELECT ... LOCK IN SHARE MODE will cause reads to block until any outstanding updates are committed.

Try this. In one screen, start transactions and updates. No SLEEP() is required, the transaction is just not committed. The lock created by UPDATE will continue to exist until you commit.

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

Don’t submit yet.

Meanwhile, in the second screen, set transaction isolation to read-commit transactions. There is no need to start an explicit transaction because InnoDB queries use transactions even if it is autocommitted.

SET tx_isolation='READ-COMMITTED';

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

The lock in shared mode makes it wait because there is still an outstanding exclusive lock created by the update.

In the first screen:

COMMIT;

In the second screen, voila! The blocking read will unblock and you can see the results of the UDPATE immediately without flushing the transaction.