|
The documentation for innodb_show_verbose_locks says the following:
If set to 1, the traditional InnoDB behavior is followed and locked records will be shown in SHOW INNODB STATUS output. If set to 0, the default, only high-level information about the lock is shown. XtraDB only.
|
https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_show_verbose_locks
This does not mention that innodb_status_output_locks needs to be set to ON in order for this lock information to be printed to SHOW ENGINE INNODB STATUS.
Additionally, the documentation for innodb_status_output_locks does not mention SHOW ENGINE INNODB STATUS:
Enable InnoDB lock monitor output to the error log. Requires innodb_status_output=ON.
|
https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_status_output_locks
It might be a good idea to document how these two variables interact.
e.g.:
MariaDB [db1]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_show_locks_held', 'innodb_show_verbose_locks', 'innodb_status_output_locks');
|
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| innodb_show_locks_held | 10 |
|
| innodb_show_verbose_locks | 0 |
|
| innodb_status_output_locks | OFF |
|
+----------------------------+-------+
|
3 rows in set (0.00 sec)
|
No locks are printed:
---TRANSACTION 17159, ACTIVE 981 sec
|
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 2
|
MySQL thread id 3, OS thread handle 0x7fdf01d6db00, query id 33 localhost root init
|
show engine innodb status
|
Trx #rec lock waits 0 #table lock waits 0
|
Trx total rec lock wait time 0 SEC
|
Trx total table lock wait time 0 SEC
|
MariaDB [db1]> SET GLOBAL innodb_show_verbose_locks=1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [db1]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_show_locks_held', 'innodb_show_verbose_locks', 'innodb_status_output_locks');
|
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| innodb_show_locks_held | 10 |
|
| innodb_show_verbose_locks | 1 |
|
| innodb_status_output_locks | OFF |
|
+----------------------------+-------+
|
3 rows in set (0.00 sec)
|
Still, no locks are printed:
---TRANSACTION 17159, ACTIVE 1074 sec
|
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 2
|
MySQL thread id 3, OS thread handle 0x7fdf01d6db00, query id 36 localhost root init
|
show engine innodb status
|
Trx #rec lock waits 0 #table lock waits 0
|
Trx total rec lock wait time 0 SEC
|
Trx total table lock wait time 0 SEC
|
MariaDB [db1]> SET GLOBAL innodb_status_output_locks=ON;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [db1]> SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_show_locks_held', 'innodb_show_verbose_locks', 'innodb_status_output_locks');
|
+----------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------+-------+
|
| innodb_show_locks_held | 10 |
|
| innodb_show_verbose_locks | 1 |
|
| innodb_status_output_locks | ON |
|
+----------------------------+-------+
|
3 rows in set (0.00 sec)
|
Now we can finally see verbose locks:
---TRANSACTION 17159, ACTIVE 1118 sec
|
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 2
|
MySQL thread id 3, OS thread handle 0x7fdf01d6db00, query id 39 localhost root init
|
show engine innodb status
|
Trx #rec lock waits 0 #table lock waits 0
|
Trx total rec lock wait time 0 SEC
|
Trx total table lock wait time 0 SEC
|
TABLE LOCK table `db1`.`tab` trx id 17159 lock mode IX lock hold time 1118 wait time before grant 0
|
RECORD LOCKS space id 239 page no 3 n bits 72 index `PRIMARY` of table `db1`.`tab` trx table locks 1 total table locks 1 trx id 17159 lock_mode X locks rec but not gap lock hold time 223 wait time before grant 0
|
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
|
0: len 4; hex 80000001; asc ;;
|
1: len 6; hex 000000004307; asc C ;;
|
2: len 7; hex 06000001410c40; asc A @;;
|
3: len 5; hex 7374723120; asc str1 ;;
|
|