Wednesday 17 July 2019

MYSQL INNODB ROW level locks

MYSQL INNODB Row level locks

Innodb perform row level locks on UPDATE,DELETE, INSERT.

When the first transaction/query performs UPDATE,DELETE, INSERT the corrresponding rows will be locked. The concurrent queries/transaction can not UPDATE,DELETE, INSERT the same rows.
However, those rows can still be selected.


Transaction it self does not perform any locking, each query can perform row level lock on innodb on UPDATE,DELETE, INSERT., and it will be auto commited when query ends.

Transaction disables autocommit, it commits through commits command, and it can row back.


SELECT .... FOR UPDATE only works in transaction, and it will perform UPDATE row level locks on these rows until transaction commits or rollback. The concurrent transaction with SELECT...FOR UPDATE can not the same rows.


If columns for SELECT FOR UPDATE are not indexed, all rows will be locked, as SELECT FOR UPDATE locks all rows it reads.



Interesting key comments:

"You want a SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE inside a transaction, as you said, since normally SELECTs, no matter whether they are in a transaction or not, will not lock a table. "

Alison R.
https://stackoverflow.com/questions/4226766/mysql-transactions-vs-locking-tables


Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.
In other words, if you don't execute your first SELECT FOR UPDATE inside a transaction, no rows are locked.

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).
Francisco de Castro
https://stackoverflow.com/questions/48963451/mysql-select-for-update-it-is-not-locking-the-target-rows-how-do-i-make-sure




You need to add an index on the id column to ensure that you get a row-level lock. SELECT ... FOR UPDATE locks all the rows that are read to perform the query, not just the rows that are actually selected. Without an index, it has to perform a full table scan, so every row is locked as a result.
With an index, it just puts a lock in that index entry, it doesn't have to read any other rows, so no other rows will be locked.








No comments:

Post a Comment