CODEWITHSUNDEEP
mysqlsqltransactions
David542
David542

Reputation: 110412

BEGIN...COMMIT vs LOCK TABLES

What is the difference between the following two sets of statements?

BEGIN;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

LOCK TABLES `table2` WRITE;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Do they do the same thing? If not, why would one be used over another?

Upvotes: 1

Views: 992

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

LOCK TABLES is global; it blocks all sessions until you use UNLOCK TABLES. It means that access to the tables you lock becomes serial. This is probably overkill for what you want to do.

Whereas InnoDB does row-level locking. Two concurrent sessions can both write to the table as long as they don't lock overlapping sets of rows.

Interaction between transactions and LOCK TABLES is a bit confusing. Both the InnoDB storage engine and the MySQL server lock a table when you use LOCK TABLES. When you COMMIT your transaction, InnoDB releases its table lock, but MySQL server does not do the same. You need to use UNLOCK TABLES to release it.

LOCK TABLES was a way to control access to tables and ensure repeatable reads, when MySQL's default storage engine was MyISAM, which does not support transactions. The cases where you should use LOCK TABLES in modern versions of MySQL are fewer and fewer. In fact, you will probably never need to use LOCK TABLES.

For more details, read https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

Upvotes: 2

Related Questions