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 usingSELECT FOR UPDATE
only applies when autocommit is disabled (either by beginning transaction withSTART 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.
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).
No comments:
Post a Comment