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

-
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?

-
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

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!

StackExchange Database Administrators Q#116113, answer score: 26