CODEWITHSUNDEEP
mysqlmultithreadinglockingmariadb
Tomas M
Tomas M

Reputation: 7343

MySQL/MariaDB lock over previous lock

I have the following full lock on tables:

LOCK TABLES t1 WRITE, t2 WRITE;

I would like to add one more table to the lock some time later (in the same session). So I execute

LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;

This seems to work just fine. My question is: is this safe? I mean, what internally happens in the database? Does it unlock the t1,t2 tables first and then lock t1,t2,t3 at once, making a possibility of unwanted access to any of the tables before the three are actually locked?

I am using MariaDB and I was reading the documentation, but didn't find the answer there.

Upvotes: 1

Views: 807

Answers (1)

Rick James
Rick James

Reputation: 142296

LOCK TABLES is for MyISAM tables. If you are using InnoDB, switch to BEGIN...COMMIT and learn about "transactions" and don't use LOCK.

When using LOCK TABLES, you get one chance to lock all the tables you need. In your example, the locks will be released on the 2 tables before acquiring the locks on the 3 tables. During that time, some other connection could grab t1 and/or t2. This is likely to mess up your design.

Since MariaDB is based on MySQL, this documentation provides the explicit answer:

If a session issues a LOCK TABLES statement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are granted.

(If you are using the Aria engine, I don't know what the answer is.)

Upvotes: 2

Related Questions