[ACCEPTED]-mysql CREATE temporary table + Transaction causes deadlock-database-deadlocks
The Root Cause
When you combine a SELECT with a write statement 48 such as INSERT INTO... or CREATE TABLE AS..., then MySQL has to establish 47 a shared lock on the tables involved in the SELECT.
You have 46 another concurrent transaction (2) that 45 holds an exclusive lock on the table phppos_sales, so transaction 44 (1) can't get its S-lock, and transaction 43 (1) waits.
Then transaction (2) requests 42 an X-lock on tale phppos_sales_items_taxes. But transaction (1) is 41 already in queue to get its S-lock on that 40 table, and transaction (2) must wait behind 39 it in the queue.
Therefore transaction (2) is 38 waiting on transaction (1), while transaction 37 (1) is waiting on transaction (2). This 36 is a classic deadlock.
This only happens 35 once every few days because it depends on 34 transaction (2) acquiring its first lock 33 on phppos_sales before transaction (1) starts its SELECT. Then 32 transaction (2) tries to acquire its second 31 lock on phppos_sales_items_taxes after transaction (1) has its S-lock 30 requests queued.
In other words, it's a race 29 condition, and those are hard to reproduce.
The Remedy
If 28 transaction (2) were to request locks on 27 all the tables it needs as an atomic action, then 26 there would be no way for transaction (1) to 25 sneak in between the lock requests.
You can 24 achieve this by explicitly using LOCK TABLES:
START TRANSACTION
LOCK TABLES phppos_sales WRITE, phppos_sales_items WRITE,
phppos_sales_items_taxes WRITE, ...other table(s)...
INSERT INTO phppos_sales
INSERT MANY RECORDS INTO phppos_sales_items
INSERT MANY RECORDS INTO phppos_sales_items_taxes
INSERT MANY RECORDS INTO phppos_sales_payments
UNLOCK TABLES;
COMMIT;
This does 23 mean that transaction (1) that does the 22 long-running SELECT has to wait for transaction 21 (2) to finish its INSERTs and unlock its tables.
Or 20 else if the SELECT is in progress first, then 19 it means that transaction (2) has to wait 18 for that to finish.
A Workaround
You could fill your temp 17 table with no lock contention if you avoid 16 using CREATE TABLE... SELECT or INSERT INTO... SELECT. That is, fetch the result-set 15 of the SELECT back into your application, and 14 then INSERT those rows into the temp table. That 13 way the SELECT won't require any S-locks.
You could 12 do the same thing with a cursor in a stored 11 procedure.
Another Workaround
As @BrendanF comments, you can 10 also change your transaction isolation level 9 to READ-COMMITTED instead of the default 8 REPEATABLE-READ. You can either change the 7 default transaction isolation level globally, or 6 you can change isolation level on a session-by-session 5 basis. This changes the semantics of transactions 4 a bit, so you should read about the differences.
- SET TRANSACTION Syntax
- Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
But 3 it does eliminate the need for SELECT to do S-locks 2 when reading from tables during those insert/select 1 operations.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.