[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
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.
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
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.
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.
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