Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11971

Improve documentation of innodb_show_verbose_locks and innodb_status_output_locks variables

    XMLWordPrintable

    Details

      Description

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

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            GeoffMontee Geoff Montee
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: