-
Notifications
You must be signed in to change notification settings - Fork 131
Lock Visualization in TokuDB
TokuDB uses key range locks to implement serializable transactions. Locks are acquired as the transaction progresses and are released when the transaction commits or aborts. This lock protocol implements the two phase locking algorithm.
TokuDB stores these locks in a data structure called the lock tree. The lock tree stores the set of range locks granted to each transaction. In addition, the lock tree stores the set of locks that are not granted due to a conflict with locks granted to some other transaction. When these other transactions are retired, these pending lock requests are retried. If a pending lock request is not granted before the lock timer expires, then the lock request is aborted.
Lock visualization in TokuDB exposes the state of the lock tree with tables in the informations schema. We also provide a mechanism that may be used by a database client to retrieve details about lock conflicts that it encountered while executing a transaction.
The 'tokudb_trx' table in the information schema maps TokuDB transaction identifiers to MySQL client identifiers. This mapping allows one to associate a TokuDB transaction with a MySQL client operation.
This query returns the MySQL clients that have a live TokuDB transaction.
select * from information_schema.tokudb_trx,information_schema.processlist
where trx_mysql_thread_id = id;
The 'tokudb_locks' information schema table contains the set of locks granted to TokuDB transactions.
This query returns all of the locks granted to some TokuDB transaction.
select * from information_schema.tokudb_locks;
This query returns the locks granted to some MySQL client.
select id from information_schema.tokudb_locks,information_schema.processlist
where locks_mysql_thread_id = id;
The 'tokudb_lock_waits' information schema table contains the set of lock requests that are not granted due to a lock conflict with some other transaction.
This query returns the locks that are waiting to be granted due to a lock conflict with some other transaction.
select * from information_schema.tokudb_lock_waits;
The 'tokudb_lock_timeout_debug' session variable controls how lock timeouts and lock deadlocks seen by the database client are reported.
When 'tokudb_lock_timeout_debug=0', no lock timeouts or lock deadlocks are reported.
When 'tokudb_lock_timeout_debug=1' (or bit 0 is set), a JSON document that describes the lock conflict is stored in the 'tokudb_last_lock_timeout' session variable.
When 'tokudb_lock_timeout_debug=2' (or bit 1 is set), a JSON document that describes the lock conflict is printed to the MySQL error log.
When 'tokudb_lock_timoue_debug=3', the 'tokudb_last_lock_timeout' variable is updated and the lock timeout is printed to the MySQL error log.
The 'tokudb_last_lock_timeout' session variable contains a JSON document that describes the last lock conflict seen by the current MySQL client. It gets set when a blocked lock request times out or a lock deadlock is detected. The 'tokudb_lock_timeout_debug' session variable should have bit 0 set.
Suppose that we create a table with a single column that is the primary key.
mysql> show create table t;
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
Suppose that we have 2 MySQL clients with ID's 1 and 2 respectively. Suppose that MySQL client 1 inserts some values into the table 't'. TokuDB transaction 51 is created for the insert statement. Since autocommit is off, transaction 51 is still live after the insert statement completes, and we can query the 'tokudb_locks' information schema table to see the locks that are held by the transaction.
mysql 1> set autocommit=off;
mysql 1> insert into t values (1),(10),(100);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 1> select * from information_schema.tokudb_locks;
+--------------+-----------------------+---------------+----------------+-----------------+
| locks_trx_id | locks_mysql_thread_id | locks_dname | locks_key_left | locks_key_right |
+--------------+-----------------------+---------------+----------------+-----------------+
| 51 | 1 | ./test/t-main | 0001000000 | 0001000000 |
| 51 | 1 | ./test/t-main | 000a000000 | 000a000000 |
| 51 | 1 | ./test/t-main | 0064000000 | 0064000000 |
+--------------+-----------------------+---------------+----------------+-----------------+
mysql 1> select * from information_schema.tokudb_lock_waits;
Empty set (0.00 sec)
The keys are currently hex dumped. There is a ticket to pretty print the keys.
Now we switch to the other MySQL client with ID 2.
mysql 2> insert into t values (2),(20),(100);
The insert gets blocked since there is a conflict on the primary key with value 100.
The granted TokuDB locks are:
mysql 1> select * from information_schema.tokudb_locks;
+--------------+-----------------------+---------------+----------------+-----------------+
| locks_trx_id | locks_mysql_thread_id | locks_dname | locks_key_left | locks_key_right |
+--------------+-----------------------+---------------+----------------+-----------------+
| 51 | 1 | ./test/t-main | 0001000000 | 0001000000 |
| 51 | 1 | ./test/t-main | 000a000000 | 000a000000 |
| 51 | 1 | ./test/t-main | 0064000000 | 0064000000 |
| 62 | 2 | ./test/t-main | 0002000000 | 0002000000 |
| 62 | 2 | ./test/t-main | 0014000000 | 0014000000 |
+--------------+-----------------------+---------------+----------------+-----------------+
The locks that are pending due to a conflict are:
mysql 1> select * from information_schema.tokudb_lock_waits;
+-------------------+-----------------+------------------+---------------------+----------------------+-----------------------+
| requesting_trx_id | blocking_trx_id | lock_waits_dname | lock_waits_key_left | lock_waits_key_right | lock_waits_start_time |
+-------------------+-----------------+------------------+---------------------+----------------------+-----------------------+
| 62 | 51 | ./test/t-main | 0064000000 | 0064000000 | 1380656990910 |
+-------------------+-----------------+------------------+---------------------+----------------------+-----------------------+
Eventually, the lock for client 2 times out, and we can retrieve a JSON document that describes the conflict.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql 2> select @@tokudb_last_lock_timeout;
+----------------------------------------------------------------------------------------------------------------+
| @@tokudb_last_lock_timeout |
+----------------------------------------------------------------------------------------------------------------+
| {"mysql_thread_id":2, "dbname":"./test/t-main", "requesting_txnid":62, "blocking_txnid":51, "key":"0064000000"} |
+----------------------------------------------------------------------------------------------------------------+
mysql 2> rollback;
Since transaction 62 was rolled back, all of the locks taken by it are released.
mysql 1> select * from information_schema.tokudb_locks;
+--------------+-----------------------+---------------+----------------+-----------------+
| locks_trx_id | locks_mysql_thread_id | locks_dname | locks_key_left | locks_key_right |
+--------------+-----------------------+---------------+----------------+-----------------+
| 51 | 1 | ./test/t-main | 0001000000 | 0001000000 |
| 51 | 1 | ./test/t-main | 000a000000 | 000a000000 |
| 51 | 1 | ./test/t-main | 0064000000 | 0064000000 |
+--------------+-----------------------+---------------+----------------+-----------------+
create table d (id int primary key) engine=tokudb;
OK
mysql 1> begin;
mysql 1> insert into t values (1);
OK
mysql 2> begin;
mysql 2> insert into t values (2);
OK
mysql 1> insert into t values (2);
blocked since key=2 is locked by mysql 2.
mysql 2> insert into t values (1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql 2> select @@tokudb_last_lock_timeout;
| {"mysql_thread_id":2, "dbname":"./test/d-main", "requesting_txnid":529, "blocking_txnid":531, "key":"0001000000"} |
which shows that mysql 2 would have created a deadlock with mysql 1.
create table a (id int primary key, c int not null default 0);
insert into a values (1,0);
create table b (id int primary key, c int not null default 0);
insert into b values (2,0)
mysql 1>
begin;
update a set c=c+1 where id=1;
update b set c=c-1 where id=2;
mysql 2>
begin;
update b set c=c-1 where id=2;
update a set c=c+1 where id=1;
If mysql 1 and mysql 2 are executed concurrently, they deadlock. TokuDB does not detect deadlocks across multiple fractal trees, so the second update statements get a lock timeout. The transactions must be aborted and retried.
As noted previously, we hexdump the lock keys which makes them difficult to decode. Here is some information that may help.
The first byte is called the infinity byte and has three valid codes. 'ff' codes minus infinity, '01' codes plus infinity, and '00' codes 0.
The next bytes are the null bytes which code whether or not any of the values for a column in the key is null. The null bytes are optional.
Finally, the values for all of the columns in the key are concatenated from left to right. The encoding of each value is dependent on its type. Integer types are encoded in native byte format, which is little endian for Intel x86 processors.
If the key is a secondary key, the final value is the value for the primary key. If there is no primary key defined for the table, a hidden big integer value is used for the primary key.
For example, if there is a key of type 'int not null', and a range is described by 'ff0a000000' through '010b000000', then the range (10,-infinity) through (11,+infinity) is locked.