I've encountered a strange problem that I can't explain. MySQL 5.6.34, RDS.
delete from table where id in (...) queries are locking up indefinitely, but never truly deadlocking.
I have a table that acts as a work queue, tracking changes in the database via triggers on appropriate tables. These triggers insert rows into the table, several external processes search for these rows and do their work, and then they delete the rows when they're done. The table is pretty active, and can start backing up quickly if the external processes don't move along steadily.
My problem is that occasionally one of the delete queries, which is deleting by a contiguous list of ids in an
IN (...) clause, will block, apparently causing other delete queries to also block, which backs up the entire queue. This process has been running for a few years at this point, and I'm not quite sure exactly what has changed to trigger this. Note that it never actually deadlocks in that it shows up as a deadlock in the
SHOW ENGINE... output, it just keeps running until I kill it (as far as I know).
Create Table: CREATE TABLE `WorkQueue` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `typeId` bigint(20) NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `connectionId` bigint(20) NOT NULL, `custId` bigint(20) DEFAULT NULL,COLLATE=utf8_unicode_ci `triggerId` tinyint(4) DEFAULT NULL, `created` bigint(20) DEFAULT NULL, `associated` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `connectionId_idx` (`connectionId`) ) ENGINE=InnoDB AUTO_INCREMENT=11080019297 DEFAULT CHARSET=utf8
delete from WorkQueue where id in (11078387761,11078387762)
Originally the id list wasn't size constrained and could grow very large, but in an effort to get this under control I started partitioning it down to no more than 50 ids in a single query, and I broke on contiguous sequences to make sure there were no gaps. The problem remains.
SHOW INNODB ENGINE STATUS output:
---TRANSACTION 221900273700, ACTIVE 127 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 954 lock struct(s), heap size 144936, 16202 row lock(s), undo log entries 49 MySQL thread id 224418, OS thread handle 0x2ba07c040700, query id 45929488139 10.1.1.1 mydb updating delete from WorkQueue where id in (11078383146,11078383147,11078383148,11078383149,11078383150,11078383151,11078383152,11078383153,11078383154,11078383155,11078383156,11078383157,11078383158,11078383159,11078383160,11078383161,11078383162,11078383163,11078383164,11078383165,11078383166,11078383167,11078383168,11078383169,11078383170,11078383171,11078383172,11078383173,11078383174,11078383175,11078383176,11078383177,11078383178,11078383179,11078383180,11078383181,11078383182,11078383183,11078383184,11078383185,11078383186,11078383187,11078383188,11078383189,11078383190,11078383191,1107838 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 993249 page no 5031 n bits 304 index `PRIMARY` of table `mydb`.`WorkQueue` trx id 221900273700 lock_mode X waiting Record lock, heap no 205 PHYSICAL RECORD: n_fields 10; compact format; info bits 0 0: len 8; hex 80000002945302ca; asc S ;; 1: len 6; hex 0033aa4e9279; asc 3 N y;; 2: len 7; hex 95000080120110; asc ;; 3: len 8; hex 8000000003df0213; asc ;; 4: len 11; hex 6d796e616d65; asc myname;; 5: len 8; hex 8000000000036dca; asc m ;; 6: SQL NULL; 7: len 1; hex 84; asc ;; 8: len 8; hex 800000005ac37da8; asc Z } ;; 9: len 1; hex 81; asc ;;
If I dump the output every few seconds then I see that the
WAITING time gradually increases until it resets, at which time the
row lock(s) number also goes up (note here it's up to 16202 for <50 actual rows to delete, and the number of ids in the list has been as low as 3 when I've noticed the same issue). As such it's never actually flagged as a deadlock, it just is never able to complete, seemingly locking more-and-more records (possibly deleted records?) until I kill the query.
The deletes that subsequently back-up look similar, but with
lock_mode X locks rec but not gap waiting. There does not seem to be any overlap in the ids being deleted in the original query vs the subsequent backed-up queries.
Any idea what's going on here?
One thing I wondered was if the table was small enough at the time the original delete was issued that mysql decided to lock the whole table/index and failed to do so, repeatedly locking more-and-more rows in an effort to complete the lock, but as far as I know things don't work that way.
I can provide more detail as needed, thanks.