Bug #74569 Description for LOCK_DATA not complete
Submitted: 26 Oct 2014 8:59 Modified: 12 Nov 2014 15:24
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.21, 5.6.22 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: information_schema, innodb, innodb_locks, lock_data

[26 Oct 2014 8:59] Daniël van Eeden
Description:
Page: http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html

"LOCK_DATA	Primary key of the locked record if LOCK_TYPE='RECORD', otherwise NULL. This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands). If there is no primary key then the InnoDB internal unique row ID number is used. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL."

Example:
mysql [information_schema] > select * from innodb_locks\G
*************************** 1. row ***************************
    lock_id: 8022:18:3:1
lock_trx_id: 8022
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: GEN_CLUST_INDEX
 lock_space: 18
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
*************************** 2. row ***************************
    lock_id: 8020:18:3:1
lock_trx_id: 8020
  lock_mode: S
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: GEN_CLUST_INDEX
 lock_space: 18
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
2 rows in set (0.00 sec)

> "Primary key of the locked record if LOCK_TYPE='RECORD', otherwise NULL."
lock_type is RECORD, so it should be the primary key.

> "This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands)."
I don't think "supremum pseudo-record" is a valid primary key.

How to repeat:
=== Session 1 ===
mysql [test] > create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql [test] > insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [test] > create temporary table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql [test] > start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql [test] > insert into t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

=== Session 2 ===
mysql [test] > insert into t1 values(4);

=== Session 3 ===
mysql [information_schema] > select * from innodb_trx\G
[26 Oct 2014 8:59] Daniël van Eeden
Added tags
[29 Oct 2014 14:34] MySQL Verification Team
Hello Daniël,

Thank you for the report.

Thanks,
Umesh
[29 Oct 2014 14:34] MySQL Verification Team
//

mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 1805:6:3:1
lock_trx_id: 1805
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: GEN_CLUST_INDEX
 lock_space: 6
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
*************************** 2. row ***************************
    lock_id: 1804:6:3:1
lock_trx_id: 1804
  lock_mode: S
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: GEN_CLUST_INDEX
 lock_space: 6
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
[12 Nov 2014 15:24] Daniel Price
"If a gap lock is taken for key values or ranges above the largest value in
the index, "LOCK_DATA" reports "supremum pseudo-record"

The INNODB_LOCKS.LOCK_DATA description has been updated. The revised content
should appear within the next 24 hours.

http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html

Thank you for the bug report.