MySQL Bugs: #55788: innodb_locks information_schema table doesn't show all locks

Bug #55788	innodb_locks information_schema table doesn't show all locks
Submitted:	5 Aug 2010 17:28	Modified:	11 Aug 2010 13:07
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.5.5, 5.6.0-m4	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Information_schema table is not 100% accurate.  I'm aware of the 0.1 second buffer, but it seems broken.  See test case.

How to repeat:
# initial data
CREATE TABLE t1 (a INT NOT NULL primary key auto_increment, b int) engine=innodb;
INSERT INTO t1 VALUES (1,1), (2,1), (3, 1);

session1> start transaction;
session2> start transaction;

session1> UPDATE t1 SET b=99 where a = 2;

session3> SELECT * FROM information_schema.innodb_locks; -- should not be empty
Empty set (0.00 sec)

session2>  update t1 set b=100 where a =2; -- tries to acquire lock, lock_wait starts.

# suddenly there are two locks in the system
session2> SELECT * FROM information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 3220F:1208:3:3
lock_trx_id: 3220F
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: `PRIMARY`
 lock_space: 1208
  lock_page: 3
   lock_rec: 3
  lock_data: 2
*************************** 2. row ***************************
    lock_id: 3220E:1208:3:3
lock_trx_id: 3220E
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: `PRIMARY`
 lock_space: 1208
  lock_page: 3
   lock_rec: 3
  lock_data: 2
2 rows in set (0.00 sec)

I didn't save the output, but it will show in information_schema.innodb_lock_waits that there is now a lock wait (as expected).

Suggested fix:
Be aware when testing, the behavior seems to have changed recently. When I was on 5.5.2, it would never show the locks here after the lock wait was acquired.

Verified just as described. This is what I've got in session 3 after each stage of the test:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.0-m4-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  SELECT * FROM information_schema.innodb_locks;
Empty set (0.00 sec)

mysql>  SELECT * FROM information_schema.innodb_locks;
+-------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3F30:22:3:3 | 3F30        | X         | RECORD    | `test`.`t1` | `PRIMARY`  |         22 |         3 |        3 | 2         | 
| 3F2F:22:3:3 | 3F2F        | X         | RECORD    | `test`.`t1` | `PRIMARY`  |         22 |         3 |        3 | 2         | 
+-------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 3F30              | 3F30:22:3:3       | 3F2F            | 3F2F:22:3:3      | 
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)

The result seems to be correct, according to the code and manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-transactions.html#innodb-...

For every blocked transaction, INNODB_LOCKS contains one row that describes each lock the transaction has requested, and for which it is waiting. INNODB_LOCKS also contains one row for each lock that is blocking another transaction, whatever the state of the transaction that holds the lock ('RUNNING', 'LOCK WAIT', 'ROLLING BACK' or 'COMMITTING'). The lock that is blocking a transaction is always held in a mode (read vs. write, shared vs. exclusive) incompatible with the mode of requested lock. 

So what you have here is the lock a blocked trx is requesting, and a lock the blocking trx is holding.

This is what our code do:
add_trx_relevant_locks_to_cache()
{
        ....
        /* If transaction is waiting we add the wait lock and all locks
        from another transactions that are blocking the wait lock. */ 
       ...
}

Jimmy, thank you for your analysis.  If that is the case, feel free to change this to a feature request.

I believe the behavior I described is more helpful.  I would like to locate transactions which hold a series of locks as this might be able to tell me ahead of time what problems I should expect when my application has higher concurrency later on.