1

Under high concurrency when running a daily process overnight we see deadlocks occur on a particular table, which seems to reference that it was holding a gap lock at the time. However I'm unable to reproduce this in a sane way.

Simplified version of the code that is run during the transaction that locks for update (as accuracy of insert into this table for a particular "account" is important) is:

select created_at, new_balance from (select created_at, id, 
new_balance from bad_table where account_type = 'subscription' and 
account_id = '123456' and currency_id = '1' for update) AS 
latest_entry order by `created_at` desc, `id` desc limit 1

followed by a single insert into that table during the same transaction:

insert into `bad_table` (`event_type`, `account_id`,`transaction_type`,
`transaction_id`, `currency_id`, `credit`, `debit`, `account_type`, 
`new_balance`, `created_at`) values ('fee_paid', '123456', 
'subscription_invoice', '123456', '1', '0', '0', 'subscription', '0', 
IF('2018-07-13 01:30:12' > CURRENT_TIMESTAMP, '2018-07-13 01:30:12', 
CURRENT_TIMESTAMP))

The deadlock occurs when another insert statement happens at the same time, and causes a rollback:

2018-07-13 01:30:12 0x2b76b84277002018-07-13 01:30:12 0x2b76b8427700
*** (1) TRANSACTION:
TRANSACTION 6790213636, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 31163514, OS thread handle 47736279553792, query id 2405537502 172.15.6.242 appserver update
insert into `bad_table ` (`event_type`, `account_id`, `transaction_type`, `transaction_id`, `currency_id`, `credit`, `debit`, `account_type`, `new_balance`, `created_at`) values ('payment_received', '123456', 'subscription_invoice', '123456', '1', '0', '0', 'subscription', '0', IF('0000-00-00 00:00:00' > CURRENT_TIMESTAMP, '0000-00-00 00:00:00', CURRENT_TIMESTAMP))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6392 page no 198703 n bits 0 index bad_table_account_currency_created_at_index of table `company`.`bad_table` trx id 6790213636 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len=12; bufptr=0x2b6b2f40e674; hex= 737562736372697074696f6e; asc subscription;;
1: len=4; bufptr=0x2b6b2f40e680; hex= 0020f3d8; asc ;;
2: len=4; bufptr=0x2b6b2f40e684; hex= 00000002; asc ;;
3: len=4; bufptr=0x2b6b2f40e688; hex= 5b46f398; asc [F ;;
4: len=4; bufptr=0x2b6b2f40e68c; hex= 01a7fec7; asc ;;
*** (2) TRANSACTION:
TRANSACTION 6790213437, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1136, 11 row lock(s), undo log entries 3
MySQL thread id 31163518, OS thread handle 47738958657280, query id 2405537549 172.15.4.186 appserver update
insert into `bad_table` (`event_type`, `account_id`, `transaction_type`, `transaction_id`, `currency_id`, `credit`, `debit`, `account_type`, `new_balance`, `created_at`) values ('tax_paid', '987654', 'subscription_invoice', '987654', '1', '0', '0', 'subscription', '0', IF('2018-07-13 01:30:11' > CURRENT_TIMESTAMP, '2018-07-13 01:30:11', CURRENT_TIMESTAMP))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6392 page no 198703 n bits 0 index bad_table_account_currency_created_at_index of table `company`.`bad_table` trx id 6790213437 lock_mode X locks gap before rec
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len=12; bufptr=0x2b6b2f40e674; hex= 737562736372697074696f6e; asc subscription;;
1: len=4; bufptr=0x2b6b2f40e680; hex= 0020f3d8; asc ;;
2: len=4; bufptr=0x2b6b2f40e684; hex= 00000002; asc ;;
3: len=4; bufptr=0x2b6b2f40e688; hex= 5b46f398; asc [F ;;
4: len=4; bufptr=0x2b6b2f40e68c; hex= 01a7fec7; asc ;;
[bitmap of 256 bytes in hex: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 18 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ]
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6392 page no 198703 n bits 0 index bad_table_account_currency_created_at_index of table `company`.`bad_table` trx id 6790213437 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 288 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len=12; bufptr=0x2b6b2f40e674; hex= 737562736372697074696f6e; asc subscription;;
1: len=4; bufptr=0x2b6b2f40e680; hex= 0020f3d8; asc ;;
2: len=4; bufptr=0x2b6b2f40e684; hex= 00000002; asc ;;
3: len=4; bufptr=0x2b6b2f40e688; hex= 5b46f398; asc [F ;;
4: len=4; bufptr=0x2b6b2f40e68c; hex= 01a7fec7; asc ;;

And the table itself looks like this:

CREATE TABLE `bad_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transaction_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`transaction_id` int(10) unsigned DEFAULT NULL,
`account_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`account_id` int(10) unsigned NOT NULL,
`event_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`currency_id` int(10) unsigned NOT NULL,
`debit` decimal(18,9) DEFAULT NULL,
`credit` decimal(18,9) DEFAULT NULL,
`new_balance` decimal(18,9) NOT NULL,
`related_bad_table` int(11) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `bad_table_currency_id_foreign` (`currency_id`),
KEY `bad_table_transaction_type_transaction_id_index` (`transaction_type`,`transaction_id`),
KEY `bad_table_account_currency_created_at_index` (`account_type`,`account_id`,`currency_id`,`created_at`),
CONSTRAINT `bad_table_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

It seems on the face of it it's the way a lock is being taken using the bad_table_account_currency_created_at index, however we've not been able to see how that would lock a row unrelated to the account_id that's being inserted.

1

Simplify query: Speeding up queries helps avoid deadlocks.

SELECT  created_at, new_balance
    FROM  bad_table
    WHERE  account_type = 'subscription'
      and  account_id = '123456'
      and  currency_id = '1'
    ORDER BY  `created_at` desc, `id` desc
    LIMIT  1
    FOR UPDATE

What is that query for? Presumably it is part of a larger transaction; it may be that the real villain in this deadlock involves some of the other statements.

Optimal index Also a speedup. (You already have this.)

INDEX(account_type, account_id, currency_id -- first, in any order
      created_at, id)                       -- last, in this order

Live with it

Deadlocks happen. All you can do is

  • Make them less frequent (above), and
  • Recover from them -- by restarting the transaction.

Daily process

Tell us about the daily overnight process. Perhaps it is a Summary table operation that can be improved?

9 decimal places? What's with DECIMAL(18,9); perhaps cryptocurrency? The _type fields are VARCHAR(50); could they be normalized? How big is that table? (A smaller table may run faster, hence less likely to deadlock.)

  • Thanks for the response Rick! The FOR UPDATE query is an attempt to make sure no other processes are attempting to write for that account ID at the same time (it's possible multiple instances might be processing that particular account's updates) to ensure totals are accurate. – Dave Marsland Jul 17 '18 at 14:17
  • I'm still slightly lost on how it's deadlocking at all though, as the output from mysql shows two statements that wouldn't conflict during insert... – Dave Marsland Jul 17 '18 at 14:26
  • @DaveMarsland - "Gap" locks are strange beings. They tend to lock the space between two rows -- to guard against two inserts into that 'gap' that would conflict. I do not understand why you got a "gap" lock. In any case, gap locks can be somewhat pessimistic in that they might block something that might or might not happen. "Gap locking" comes under the heading of "live with it" (even if you don't understand it). – Rick James Jul 17 '18 at 18:19

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.