Meaning of 'locks rec but not gap waiting' in deadlock report

Question

1. About the meaning of locks rec but not gap waitingin TRANSACTION(1), which one is correct?

   • Already granted gap lock, waiting for clustered index X lock?
   • Already granted clustered index X lock, waiting for gap lock?

2. There are 31 rows in Transaction(1). What is the meaning of those rows? Does this represent a gap lock?

    0: len 4; hex 800c20d6; asc     ;;
    ....
    29: SQL NULL;
    30: SQL NULL;
   

LATEST DETECTED DEADLOCK Report

    LATEST DETECTED DEADLOCK
    ------------------------
    2015-09-25 15:27:24 1b8084000


    *** (1) TRANSACTION:
    TRANSACTION 5226928, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 31 lock struct(s), heap size 6544, 548 row lock(s)
    MySQL thread id 71, OS thread handle 0x1b45be000, query id 4085356 localhost root Creating sort index
    SELECT  `rpush_notifications`.* FROM `rpush_notifications`  WHERE (processing = 0 AND delivered = 0 AND failed = 0 AND (deliver_after IS NULL OR deliver_after < '2015-09-25 07:27:24'))  ORDER BY created_at ASC LIMIT 100 FOR UPDATE


    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 10287 page no 10901 n bits 152 index `PRIMARY` of table `ct_development`.`rpush_notifications` trx id 5226928 lock_mode X locks rec but not gap waiting
    Record lock, heap no 78 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
     0: len 4; hex 800c20d6; asc     ;;
     1: len 6; hex 0000004fc1aa; asc    O  ;;
     2: len 7; hex 4c0000027b07fe; asc L   {  ;;
     3: len 30; hex 52707573683a3a436c69656e743a3a4163746976655265636f72643a3a41; asc Rpush::Client::ActiveRecord::A; (total 47 bytes);
     4: SQL NULL;
     5: len 30; hex 373838653836643365666465626262626639633464363261626433366132; asc 788e86d3efdebbbbf9c4d62abd36a2; (total 64 bytes);
     6: len 7; hex 64656661756c74; asc default;;
     7: len 7; hex 6869206d6f6d21; asc hi mom!;;
     8: len 13; hex 7b22666f6f223a22626172227d; asc {"foo":"bar"};;
     9: len 4; hex 80015180; asc   Q ;;
     10: len 1; hex 80; asc  ;;
     11: SQL NULL;
     12: len 1; hex 81; asc  ;;
     13: len 5; hex 99973276d8; asc   2v ;;
     14: SQL NULL;
     15: len 0; hex ; asc ;;
     16: SQL NULL;
     17: len 5; hex 99973276d6; asc   2v ;;
     18: len 5; hex 99973276d6; asc   2v ;;
     19: len 1; hex 80; asc  ;;
     20: SQL NULL;
     21: len 1; hex 80; asc  ;;
     22: SQL NULL;
     23: len 4; hex 80000002; asc     ;;
     24: len 4; hex 80000000; asc     ;;
     25: SQL NULL;
     26: SQL NULL;
     27: len 1; hex 80; asc  ;;
     28: SQL NULL;
     29: SQL NULL;
     30: SQL NULL;

    *** (2) TRANSACTION:

    TRANSACTION 5226922, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 0
    mysql tables in use 1, locked 1
    3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
    MySQL thread id 1446, OS thread handle 0x1b8084000, query id 4085345 localhost root updating
    UPDATE `rpush_notifications` SET processing = 0, delivered = 0, delivered_at = NULL, failed = 1, failed_at = '2015-09-25 07:27:24', error_code = NULL, error_description = '' WHERE `rpush_notifications`.`id` IN (794838, 794839)


    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 10287 page no 10901 n bits 152 index `PRIMARY` of table `ct_development`.`rpush_notifications` trx id 5226922 lock_mode X locks rec but not gap
    Record lock, heap no 78 PHYSICAL RECORD: n_fields 31; compact format; info bits 0
     0: len 4; hex 800c20d6; asc     ;;
     1: len 6; hex 0000004fc1aa; asc    O  ;;
     2: len 7; hex 4c0000027b07fe; asc L   {  ;;
     3: len 30; hex 52707573683a3a436c69656e743a3a4163746976655265636f72643a3a41; asc Rpush::Client::ActiveRecord::A; (total 47 bytes);
     4: SQL NULL;
     5: len 30; hex 373838653836643365666465626262626639633464363261626433366132; asc 788e86d3efdebbbbf9c4d62abd36a2; (total 64 bytes);
     6: len 7; hex 64656661756c74; asc default;;
     7: len 7; hex 6869206d6f6d21; asc hi mom!;;
     8: len 13; hex 7b22666f6f223a22626172227d; asc {"foo":"bar"};;
     9: len 4; hex 80015180; asc   Q ;;
     10: len 1; hex 80; asc  ;;
     11: SQL NULL;
     12: len 1; hex 81; asc  ;;
     13: len 5; hex 99973276d8; asc   2v ;;
     14: SQL NULL;
     15: len 0; hex ; asc ;;
     16: SQL NULL;
     17: len 5; hex 99973276d6; asc   2v ;;
     18: len 5; hex 99973276d6; asc   2v ;;
     19: len 1; hex 80; asc  ;;
     20: SQL NULL;
     21: len 1; hex 80; asc  ;;
     22: SQL NULL;
     23: len 4; hex 80000002; asc     ;;
     24: len 4; hex 80000000; asc     ;;
     25: SQL NULL;
     26: SQL NULL;
     27: len 1; hex 80; asc  ;;
     28: SQL NULL;
     29: SQL NULL;
     30: SQL NULL;

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 10287 page no 7992 n bits 1480 index `index_rpush_notifications_multi` of table `ct_development`.`rpush_notifications` trx id 5226922 lock_mode X locks rec but not gap waiting
    Record lock, heap no 1279 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
     0: len 1; hex 80; asc  ;;
     1: len 1; hex 80; asc  ;;
     2: len 4; hex 800c20d6; asc     ;;

    *** WE ROLL BACK TRANSACTION (2)

Table Schema

CREATE TABLE `rpush_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `badge` int(11) DEFAULT NULL,
  `device_token` varchar(64) DEFAULT NULL,
  `sound` varchar(255) DEFAULT 'default',
  `alert` varchar(255) DEFAULT NULL,
  `data` text,
  `expiry` int(11) DEFAULT '86400',
  `delivered` tinyint(1) NOT NULL DEFAULT '0',
  `delivered_at` datetime DEFAULT NULL,
  `failed` tinyint(1) NOT NULL DEFAULT '0',
  `failed_at` datetime DEFAULT NULL,
  `error_code` int(11) DEFAULT NULL,
  `error_description` text,
  `deliver_after` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `alert_is_json` tinyint(1) DEFAULT '0',
  `collapse_key` varchar(255) DEFAULT NULL,
  `delay_while_idle` tinyint(1) NOT NULL DEFAULT '0',
  `registration_ids` mediumtext,
  `app_id` int(11) NOT NULL,
  `retries` int(11) DEFAULT '0',
  `uri` varchar(255) DEFAULT NULL,
  `fail_after` datetime DEFAULT NULL,
  `processing` tinyint(1) NOT NULL DEFAULT '0',
  `priority` int(11) DEFAULT NULL,
  `url_args` text,
  `category` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_rapns_notifications_multi` (`app_id`,`delivered`,`failed`,`deliver_after`),
  KEY `index_rpush_notifications_multi` (`delivered`,`failed`)
) ENGINE=InnoDB AUTO_INCREMENT=337371 DEFAULT CHARSET=utf8;

Answer 1

Question 1: InnoDB is trying to get an exclusive lock on the row (it's actually a lock on the clustered index record, the PK), but not the surrounding gap (a gap lock). You can read more about record and gap locks here: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks

The exclusive lock request is coming from the "for update" clause added to the SELECT statement. Keep in mind that when using the repeatable-read isolation level (select @@session.tx_isolation), which is the default, the exclusive lock will be placed on every row examined for the SELECT, not just those that are returned (those that match all the predicates in the WHERE clause). So you may be able to reduce the number of rows examined, and thus the locks held which will greatly reduce the chances of conflicts, by using a composite index on: processing, delivered, failed, deliver_after

I don't know what the cardinality (number of unique values) is for those columns, but you should generally list them in the composite index from highest cardinality to lowest.

 

Question 2: 31 is the number of fields in the index record (or row, logically) that it's trying/waiting to get an exclusive lock on. InnoDB uses a clustered PK, so the records in the clustered index (the PK) contain the fields for each user defined column--29 fields in the case of your push_notifications table: id ... category--and there are always two additional internal fields for each InnoDB table: a 6-byte transaction ID field and a 7-byte roll pointer field (these are used for MVCC). So the 31 items you see listed are the fields contained within the index record (or the row, logically) that we're waiting for the lock on.

 

Just FYI: You can get further breakdowns of InnoDB locks using new Information_Schema tables in 5.6+: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-transactions.html

There's also some great reports in the new SYS schema, which is included with the server in MySQL 5.7+ (hasn't quite made its way into the online manual yet though): https://github.com/MarkLeith/mysql-sys#innodb_lock_waits--xinnodb_lock_waits

You can install the latest SYS schema in MySQL 5.6 manually, if it's of interest: https://github.com/MarkLeith/mysql-sys/blob/master/sys_56.sql

For more info on it: http://www.slideshare.net/Leithal/performance-schema-and-sys-schema-in-mysql-57

 

I hope this helps!