| Bug #79883 | RO transaction that hold S lock is not printed by `show engine innodb status` | ||
|---|---|---|---|
| Submitted: | 8 Jan 2016 9:26 | Modified: | 11 Jan 2016 12:20 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7, 5.7.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Jan 2016 9:37]
zhai weixiang
Another example: SELECT... IN SHARE MODE
[11 Jan 2016 12:20]
MySQL Verification Team
Hello Zhai, Thank you for the report and test case. Verified as described with 5.7.10. Thanks, Umesh
[11 Jan 2016 12:21]
MySQL Verification Team
-- with 5.7.10
// Session 1
[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL,
-> `b` int(11) DEFAULT NULL,
-> PRIMARY KEY (`a`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1,4),(2,4),(4,5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+---+------+
| a | b |
+---+------+
| 1 | 4 |
| 2 | 4 |
| 4 | 5 |
+---+------+
3 rows in set (0.00 sec)
mysql> set session tx_isolation = SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 1;
+---+------+
| a | b |
+---+------+
| 1 | 4 |
+---+------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+-------------------------------------+
| 38 | root | localhost | test | Query | 0 | starting | show processlist |
| 39 | root | localhost | test | Query | 9 | updating | update t1 set b = b + 1 where a = 1 |
+----+------+-----------+------+---------+------+----------+-------------------------------------+
2 rows in set (0.00 sec)
mysql> show engine innodb status\G
*************************** 1. row ***************************
------------
TRANSACTIONS
------------
Trx id counter 215981
Purge done for trx's n:o < 215979 undo n:o < 0 state: running but idle
History list length 1333
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 215980, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 39, OS thread handle 139981886146304, query id 6667 localhost root updating
update t1 set b = b + 1 where a = 1
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 534 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 215980 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000034ba6; asc K ;;
2: len 7; hex f60000011f0110; asc ;;
3: len 4; hex 80000004; asc ;;
.
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in 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 |
+-------------------------+-----------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 215980:534:3:2 | 215980 | X | RECORD | `test`.`t1` | PRIMARY | 534 | 3 | 2 | 1 |
| 421459517294416:534:3:2 | 421459517294416 | S | RECORD | `test`.`t1` | PRIMARY | 534 | 3 | 2 | 1 |
+-------------------------+-----------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.01 sec)
// Session 2
[root@cluster-repo ~]# mysql -uroot -p test
Enter password:
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 39
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update t1 set b = b + 1 where a = 1; -- block
[2 Feb 2016 22:18]
Sveta Smirnova
This is regression: in version 5.6 blocking transaction printed in the SHOW ENGINE INNODB STATUS output
Description: In MySQL 5.7. The `SHOW ENGINE INNODB STATUS` command will print two kind of transaction list (lock_print_info_all_transactions): 1. First print all transactions that's not started (trx->state == TRX_STATE_NOT_STARTED) 2. Print transactions in trx_sys->rw_trx_list list. If SERIALIZABLE isolation is used, a readonly transaction (Not autocommit) will hold S lock on records it has queried. It's better to print such transaction too. How to repeat: session 1: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from t1; +---+------+ | a | b | +---+------+ | 1 | 4 | | 2 | 4 | | 4 | 5 | +---+------+ 3 rows in set (0.00 sec) mysql> set session tx_isolation = SERIALIZABLE;Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1 where a = 1; +---+------+ | a | b | +---+------+ | 1 | 4 | +---+------+ 1 row in set (0.00 sec) session 2: mysql> update t1 set b = b + 1 where a = 1; -- block session 1: mysql> show processlist; +------+------+-----------+------+---------+------+----------+-------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------+------+---------+------+----------+-------------------------------------+ | 1035 | root | localhost | test | Query | 0 | starting | show processlist | | 1077 | root | localhost | test | Query | 14 | updating | update t1 set b = b + 1 where a = 1 | +------+------+-----------+------+---------+------+----------+-------------------------------------+ 2 rows in set (0.00 sec) show engine innodb status --- only print one transaction. ------------ TRANSACTIONS ------------ Trx id counter 255171398 Purge done for trx's n:o < 255171396 undo n:o < 0 state: running but idle History list length 552 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 255171397, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1077, OS thread handle 47199948384000, query id 6003443 localhost root updating update t1 set b = b + 1 where a = 1 ------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 213 page no 3 n bits 72 index PRIMARY of table `test`.`t1` trx id 255171397 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000f359b42; asc 5 B;; 2: len 7; hex 770000000d231d; asc w # ;; 3: len 4; hex 80000004; asc ;; ------------------ 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 | +-------------------------+-----------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ | 255171397:213:3:2 | 255171397 | X | RECORD | `test`.`t1` | PRIMARY | 213 | 3 | 2 | 1 | | 328637985425232:213:3:2 | 328637985425232 | S | RECORD | `test`.`t1` | PRIMARY | 213 | 3 | 2 | 1 | +-------------------------+-----------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.00 sec) Suggested fix: Print all transactions that hold innodb locks . Check function lock_print_info_all_transactions and this bug should be very easy to be fixed.