3

I am baffled by this deadlock:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 150511 18:42:43 *** (1) TRANSACTION: TRANSACTION B8972CA4, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 55103359, OS thread handle 0x7f6babefb700, query id 18803072369 db.example.com 127.0.0.1 application update INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999172', '[email protected]' ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA4 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION B8972CA5, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 55103302, OS thread handle 0x7f6bf2320700, query id 18803072370 db.example.com 127.0.0.1 application update INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999170', '[email protected]' ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)

The schema:

CREATE TABLE users (id int NOT NULL AUTO_INCREMENT, external_id varchar(255) NOT NULL, external_record_type varchar(255) NOT NULL, email varchar(255) NOT NULL, created_at datetime DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY index_users_on_external_record_type_and_external_id (external_record_type(8), external_id), KEY index_users_on_email (email))

I see that T1 and T2 both want the insert intention gap lock on the same gap, but I don't see why the transactions can't proceed sequentially. Moreover, I don't see why T2 acquires a non-insert-intention lock on the gap before trying for the insert-intention lock.

I'm using MySQL 5.5 of the Percona flavor.

Improve this question
6
  • 1
    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. Commented May 11, 2015 at 19:17
  • I'm afraid these are the entire transactions. Hence my confusion! Commented May 11, 2015 at 19:20
  • Hmmm... What is the value of autocommit? Do you have explicit BEGIN..COMMIT? SHOW CREATE TABLE. Do you really need the (8) prefix? Commented May 11, 2015 at 19:22
  • 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. Commented May 11, 2015 at 19:32
  • 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. Commented May 11, 2015 at 21:16

1 Answer 1

Reset to default
1

I failed to understand the extent of the transactions.

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.

The behavior I found, and continue to find surprising, is that write locks on index gap values aren't exclusive. While that's certainly a correct way to implement serializability, it carries an attendant risk of deadlock that you would forego if write locks on gap values were exclusive as they are for record values. I wonder if I continue to miss something fundamental.

Improve this answer
5
  • Don't do a write lock, just do a FOR UPDATE: BEGIN; SELECT ... FOR UPDATE; ... UPDATE...; COMMIT; Commented May 11, 2015 at 21:51
  • SELECT FOR UPDATE acquires a write lock. That's precisely the cause of the problem. Commented May 12, 2015 at 1:06
  • No that is just part of the problem. Usually (always?) a deadlock involves two rows locked in a different order. You need the write lock to prevent other problems. Commented May 12, 2015 at 15:08
  • The essence of the problem is that the write lock obtained by SELECT FOR UPDATE does not block other write locks from being obtained on the gap. The two write locks block each other from being upgraded to an insert intent lock, leading to, consecutively, a block, then a deadlock. Commented May 12, 2015 at 15:46
  • Speaking of the gap... I am concerned about having a prefix inside a UNIQUE index. It makes my brain hurt to try to figure out what the "gap" would be when dealing with (8). Possibly the engine punts and locks a much bigger gap than "necessary". Can you remove the (8)? Commented May 12, 2015 at 15:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.