Timeline for Why do these concurrent MySQL inserts deadlock?
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 11, 2015 at 21:36 | answer | added | Donald ball | timeline score: 1 | |
| May 11, 2015 at 21:22 | comment | added | Rick James |
Perhaps a FOR UPDATE is needed?
|
|
| May 11, 2015 at 21:16 | comment | added | Donald ball | Okay, I'm a lying liar and I have lied to you, I apologize! This isn't the extent of the transaction. Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed. | |
| May 11, 2015 at 19:32 | comment | added | Donald ball | These are running in explicit transactions using the default REPEATABLE READ isolation level. The (8) suffix on external_record_type should be irrelevant to this problem, albeit necessary for performance for the values we have. It's worth noting the problem is timing dependent. I can't reproduce this manually in an offline session with two concurrent transactions. | |
| May 11, 2015 at 19:22 | comment | added | Rick James |
Hmmm... What is the value of autocommit? Do you have explicit BEGIN..COMMIT? SHOW CREATE TABLE. Do you really need the (8) prefix?
|
|
| May 11, 2015 at 19:20 | comment | added | Donald ball | I'm afraid these are the entire transactions. Hence my confusion! | |
| May 11, 2015 at 19:17 | comment | added | Rick James |
Let's see the entire transaction. That pair of INSERTs should "lock wait", not deadlock, so I am thinking that something else in the transactions is involved.
|
|
| May 11, 2015 at 19:14 | review | First posts | |||
| May 11, 2015 at 19:40 | |||||
| May 11, 2015 at 19:10 | history | asked | Donald ball | CC BY-SA 3.0 |