简介

TokuDB使用LockTree(ft-index/locktree)来维护事务的锁状态(row-lock和range-lock)，LockTree的数据结构是一个Binary Tree。
本篇将通过几个“栗子”来谈谈TokuDB的row-lock和range-lock。
表t:

  1. mysql> show create table t\G
  2. *************************** 1. row ***************************
  3.        Table: t
  4. Create Table: CREATE TABLE `t` (
  5.   `id` int(11) NOT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=TokuDB DEFAULT CHARSET=latin1

row-lock

  1. mysql> set autocommit=off;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> insert into t values (1),(10),(100);
  5. Query OK, 3 rows affected (0.00 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  8. mysql> select * from information_schema.tokudb_locks\G
  9. *************************** 1. row ***************************
  10.                locks_trx_id: 238
  11.       locks_mysql_thread_id: 3
  12.                 locks_dname: ./test/t-main
  13.              locks_key_left: 0001000000
  14.             locks_key_right: 0001000000
  15.          locks_table_schema: test
  16.            locks_table_name: t
  17. locks_table_dictionary_name: main
  18. *************************** 2. row ***************************
  19.                locks_trx_id: 238
  20.       locks_mysql_thread_id: 3
  21.                 locks_dname: ./test/t-main
  22.              locks_key_left: 000a000000
  23.             locks_key_right: 000a000000
  24.          locks_table_schema: test
  25.            locks_table_name: t
  26. locks_table_dictionary_name: main
  27. *************************** 3. row ***************************
  28.                locks_trx_id: 238
  29.       locks_mysql_thread_id: 3
  30.                 locks_dname: ./test/t-main
  31.              locks_key_left: 0064000000
  32.             locks_key_right: 0064000000
  33.          locks_table_schema: test
  34.            locks_table_name: t
  35. locks_table_dictionary_name: main
  36. 3 rows in set (0.00 sec)

从tokudb_locks表可以查询到，生成了3条row-lock(locks_key_left和locks_key_right相等)。
为了存储和显示方便，locks_key_left/locks_key_right取key的hash值。

range-lock

  1. mysql> set autocommit=off;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> delete from t where id<100;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> select * from information_schema.tokudb_locks\G
  8. *************************** 1. row ***************************
  9.                locks_trx_id: 280
  10.       locks_mysql_thread_id: 12
  11.                 locks_dname: ./test/t-main
  12.              locks_key_left: -infinity
  13.             locks_key_right: ff64000000
  14.          locks_table_schema: test
  15.            locks_table_name: t
  16. locks_table_dictionary_name: main
  17. 1 row in set (0.00 sec)

从tokudb_locks表可以查询到，where条件的rang-lock区间为[-infinity, ff64000000]，只要其他事务的锁区间跟这个有任何重叠，则需要等待。

锁冲突

client1执行如下操作:

  1. mysql1> set autocommit=off;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql1> insert into t values (1),(10),(100);
  5. Query OK, 3 rows affected (0.00 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  8. mysql1> select * from information_schema.tokudb_locks\G
  9. *************************** 1. row ***************************
  10.                locks_trx_id: 283
  11.       locks_mysql_thread_id: 14
  12.                 locks_dname: ./test/t-main
  13.              locks_key_left: 0001000000
  14.             locks_key_right: 0001000000
  15.          locks_table_schema: test
  16.            locks_table_name: t
  17. locks_table_dictionary_name: main
  18. *************************** 2. row ***************************
  19.                locks_trx_id: 283
  20.       locks_mysql_thread_id: 14
  21.                 locks_dname: ./test/t-main
  22.              locks_key_left: 000a000000
  23.             locks_key_right: 000a000000
  24.          locks_table_schema: test
  25.            locks_table_name: t
  26. locks_table_dictionary_name: main
  27. *************************** 3. row ***************************
  28.                locks_trx_id: 283
  29.       locks_mysql_thread_id: 14
  30.                 locks_dname: ./test/t-main
  31.              locks_key_left: 0064000000
  32.             locks_key_right: 0064000000
  33.          locks_table_schema: test
  34.            locks_table_name: t
  35. locks_table_dictionary_name: main
  36. 3 rows in set (0.00 sec)

client2执行如下操作:

  1. mysql2> set autocommit=off;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql2> insert into t values (2),(100);
  5. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  6. mysql2> select * from information_schema.tokudb_locks\G
  7. *************************** 1. row ***************************
  8.                locks_trx_id: 283
  9.       locks_mysql_thread_id: 14
  10.                 locks_dname: ./test/t-main
  11.              locks_key_left: 0001000000
  12.             locks_key_right: 0001000000
  13.          locks_table_schema: test
  14.            locks_table_name: t
  15. locks_table_dictionary_name: main
  16. *************************** 2. row ***************************
  17.                locks_trx_id: 283
  18.       locks_mysql_thread_id: 14
  19.                 locks_dname: ./test/t-main
  20.              locks_key_left: 000a000000
  21.             locks_key_right: 000a000000
  22.          locks_table_schema: test
  23.            locks_table_name: t
  24. locks_table_dictionary_name: main
  25. *************************** 3. row ***************************
  26.                locks_trx_id: 283
  27.       locks_mysql_thread_id: 14
  28.                 locks_dname: ./test/t-main
  29.              locks_key_left: 0064000000
  30.             locks_key_right: 0064000000
  31.          locks_table_schema: test
  32.            locks_table_name: t
  33. locks_table_dictionary_name: main
  34. *************************** 4. row ***************************
  35.                locks_trx_id: 289
  36.       locks_mysql_thread_id: 16
  37.                 locks_dname: ./test/t-main
  38.              locks_key_left: 0002000000
  39.             locks_key_right: 0002000000
  40.          locks_table_schema: test
  41.            locks_table_name: t
  42. locks_table_dictionary_name: main
  43. 4 rows in set (0.00 sec)
  45. mysql2> select @@tokudb_last_lock_timeout;
  46. +--------------------------------------------------------------------------------------------------------------------+
  47. | @@tokudb_last_lock_timeout                                                                                         |
  48. +--------------------------------------------------------------------------------------------------------------------+
  49. | {"mysql_thread_id":16, "dbname":"./test/t-main", "requesting_txnid":289, "blocking_txnid":283, "key":"0064000000"} |
  50. +--------------------------------------------------------------------------------------------------------------------+
  51. 1 row in set (0.00 sec)

锁等待超时了，通过参数tokudb_last_lock_timeout得知，hash为0064000000的row-lock已经被txnid为283（client1)抢占。

总结

在使用TokuDB过程中，如果show processlist里有锁等待语句，可以通过tokudb_locks表获取到当前所有事务的锁信息，以快速定位到问题。
TokuDB提供tokudb_lock_timeout_debug参数，可以设置不同值(默认值为1)来记录锁冲突信息，说明如下：

  1. tokudb_lock_timeout_debug = 0: No lock timeouts or lock deadlocks are reported.
  2. tokudb_lock_timeout_debug = 1: A JSON document that describes the lock conflict is stored in the tokudb_last_lock_timeout session variable
  3. tokudb_lock_timeout_debug = 2: A JSON document that describes the lock conflict is printed to the MySQL error log.
  4. tokudb_lock_timeout_debug = 3: A JSON document that describes the lock conflict is stored in the tokudb_last_lock_timeout session variable and is printed to the MySQL error log.