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', 'user1@example.com' )
*** (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', 'user2@example.com' )
*** (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.

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.
    – Rick James
    May 11 '15 at 19:17
  • I'm afraid these are the entire transactions. Hence my confusion! May 11 '15 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?
    – Rick James
    May 11 '15 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. May 11 '15 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. May 11 '15 at 21:16
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.

5
  • Don't do a write lock, just do a FOR UPDATE: BEGIN; SELECT ... FOR UPDATE; ... UPDATE...; COMMIT;
    – Rick James
    May 11 '15 at 21:51
  • SELECT FOR UPDATE acquires a write lock. That's precisely the cause of the problem. May 12 '15 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.
    – Rick James
    May 12 '15 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. May 12 '15 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)?
    – Rick James
    May 12 '15 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.