1

I'm writing some code that uses row level locking with MySQL (innodb backend).

The pseudocode is:

START TRANSACTION
SELECT * FROM foo WHERE foocondition FOR UPDATE
UPDATE foo set bar=value WHERE foocondition
COMMIT

I can't find on the mysql documentation info on the locks held AFTER the commit.

Do I have to execute a "UNLOCK TABLES" after the COMMIT or is it implicit? The answer should be "NO", but I'd like to have feedback on that.

SimoneLazzaris
  • 329
  • 2
  • 9
  • 2
    Your assumption is correct, and also the documentation makes it clear: [Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.](https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html), (and in case you are specifically wondering about locking read: [All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html)) – Solarflare Jan 14 '19 at 16:07
  • @Solarflare, you are correct. I've made some tests and the lock is released on commit. Thanks – SimoneLazzaris Jan 15 '19 at 14:08

1 Answers1

1

UNLOCK TABLES has nothing to do with locking reads that you are using in your example. You use UNLOCK TABLES when you have previously locked a table with LOCK TABLES command. Even if you use locking reads to set range locks so that no one can INSERT new rows to the table, the table itself is not locked.

Locks set by a locking read like SELECT ... FOR UPDATE and DML statements like UPDATE ... WHERE are released automatically when the transaction ends.

Croco
  • 326
  • 1
  • 12