Mysql select for update - it is not locking the target rows. How do I make sure it does?

Question

So the syntax for select for update is something like

SELECT *     //1st query
FROM test
WHERE id = 4 FOR UPDATE;

UPDATE test    //2nd query
SET parent = 100
WHERE id = 4;

I am guessing the locking part is the first line.

So when the first set of queries executes, I should not be able to select and modify the row with id = 4 (it is primary key by the way). However, I am still able to select row with id = 4 before I update anything, meaning another thread could probably come in and try to select and update the same row before second row hits, leading to concurrency problem.

But when I lock the entire table like below

LOCK TABLES test WRITE;

Other transactions are pending and waits until the lock is released. Only reason why I would like to use SELECT FOR UPDATE instead of table lock is because of the reason referenced here https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

If I just quote them here, it is as below

LOCK TABLES does not play well with transactions. Even if you use the "SET autommit=0" syntax you can find undesired side effects. For instance, issuing a second LOCK TABLES query within a transaction will COMMIT your pending changes:

SET autocommit=0;
LOCK TABLES foo WRITE;
INSERT INTO foo (foo_name) VALUES ('John');
LOCK TABLES bar WRITE; -- Implicit commit
ROLLBACK; -- No effect: data already committed

In many cases, LOCK TABLES can be replaced by SELECT ... FOR UPDATE which is fully transaction aware and doesn't need any special syntax:

START TRANSACTION;
SELECT COUNT(*) FROM foo FOR UPDATE; -- Lock issued
INSERT INTO foo (foo_name) VALUES ('John');
SELECT COUNT(*) FROM bar FOR UPDATE; -- Lock issued, no side effects
ROLLBACK; -- Rollback works as expected

So if I can access the rows selected for update BEFORE actual update occurs, what exactly is SELECT FOR UPDATE locking? Also how can I test that the rows are being locked in my application? (it is obviously not working in the first set of queries that I have written)

The table is created with InnoDB engine

Francisco's solution

Both the solutions below results in parent being 1

UPDATE test
SET parent = 99
WHERE id = 4;
COMMIT;

START TRANSACTION;
SELECT *
FROM test
WHERE id = 4 FOR UPDATE;
UPDATE test
SET parent = 98
WHERE id = 4;   //don't commit 

START TRANSACTION;
SELECT *
FROM test
WHERE parent = 98 FOR UPDATE; //commit did not happens so the id=4 document would still be parent = 99
UPDATE test
SET parent = 1
WHERE id = 4;
COMMIT;           //parent = 1 where id = 4

Another one just change the parent conditional to 99 instead of 98

UPDATE test
SET parent = 99
WHERE id = 4;
COMMIT;

START TRANSACTION;
SELECT *
FROM test
WHERE id = 4 FOR UPDATE;
UPDATE test
SET parent = 98
WHERE id = 4;      //Don't commit

START TRANSACTION;
SELECT *
FROM test
WHERE parent = 99 FOR UPDATE;     //targets parent = 99 this time but id=4 still results in parent =1
UPDATE test
SET parent = 1
WHERE id = 4;
COMMIT;

The first sets of query runs as if id=4 document has been committed to parent = 98 first. However, the second sets of query runs as if id=4 document has NOT been committed to parent = 99. How do I maintain consistency here?

Answer 1

A SELECT FOR UPDATE locks the row you selected for update until the transaction you created ends. Other transactions can only read that row but they cannot update it as long as the select for update transaction is still open.

In order to lock the row(s):

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Run whatever logic you want to do
COMMIT;

The transaction above will be alive and will lock the row until it is committed.

In order to test it, there are different ways. I tested it using two terminal instances with the MySQL client opened in each one.

On the first terminal you run the SQL:

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Do not COMMIT to keep the transaction alive

On the second terminal you can try to update the row:

UPDATE test SET parent = 100 WHERE id = 4;

Since you create a select for update on the first terminal the query above will wait until the select for update transaction is committed or it will timeout.

Go back to the first terminal and commit the transaction:

COMMIT;

Check the second terminal and you will see that the update query was executed (if it did not timed out).