Monitoring deadlocks with Pt-deadlock-logger

The Percona-toolkit provided by Percona provides a number of useful features, highlighting how to monitor deadlocks.

Pt-deadlock-logger Basic Usage

Usage:pt-deadlock-logger [OPTIONS] DSN

Explanation: Specify the DSN deadlock information, it can be standard output to the screen can also be entered into the table or log files, options is to add the parameters.

Here is a simulation of a deadlock test, examples are as follows:

Session1:

Mysql> CREATE TABLE Lock1 (ID int,name char (), address varchar (50));
Query OK, 0 rows affected (0.15 sec)

Mysql> CREATE TABLE Lock2 (ID int,name char (), address varchar (50));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO LOCK1 values (1, ' AA ', ' Beijing ');
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO LOCK1 values (2, ' BB ', ' Shanghai ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT into LOCK1 values (3, ' cc ', ' Shenzhen ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Lock2 values (1, ' DD ', ' Beijing ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Lock2 values (2, ' ee ', ' Tianjin ');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT into Lock2 values (3, ' ff ', ' Henan ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from Lock1;
+------+------+----------+
| ID | name | Address |
+------+------+----------+
| 1 | AA | Beijing |
| 2 | bb | Shanghai |
| 3 | CC | shenzhen |
+------+------+----------+
3 rows in Set (0.01 sec)

Mysql> select * from Lock2;
+------+------+---------+
| ID | name | Address |
+------+------+---------+
| 1 | DD | Beijing |
| 2 | EE | Tianjin |
| 3 | FF | Henan |
+------+------+---------+
3 Rows in Set (0.00 sec)

Mysql> set autocommit=0;
Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from Lock1 where id=1 for update;
+------+------+---------+
| ID | name | Address |
+------+------+---------+
| 1 | AA | Beijing |
+------+------+---------+
1 row in Set (0.00 sec)

Session2
mysql> use test;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> set autocommit=0;
Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from Lock2 where id=1 for update;
+------+------+---------+
| ID | name | Address |
+------+------+---------+
| 1 | DD | Beijing |
+------+------+---------+
1 row in Set (0.00 sec)

Session1
Mysql> SELECT * from Lock2 where id=1 for update; ---lock wait

Session2
Mysql> SELECT * from Lock1 where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction

OK, the deadlock has already been created. Now analyze it with Pt-deadlock-logger.
[Email protected] ~]# pt-deadlock-logger--run-time=10--interval=3--create-dest-table--dest d=test,t=deadlocks u= Root,p=xxxxx
Server TS thread txn_id txn_time user hostname IP db tbl idx lock_type lock_mode wait_hold victim Query
Localhost.localdomain 2014-01-23t16:04:02 0 root localhost test lock2 gen_clust_index RECORD X w 0 select * from l Ock2 where id=1 for update
Localhost.localdomain 2014-01-23t16:04:02 0 The root localhost test lock1 gen_clust_index RECORD X w 1 select * from lo CK1 where id=1 for update

The parameters are explained as follows:
--create-dest-table: Creates a table for the specified information.
--dest: Creates a table that stores the deadlock information.
--database:-D, specify the database.
--table:-T, specifying the table name.
--log: Specifies that deadlock log information is written to the file.
--run-time: Number of runs, default permanent.
--interval: Run time interval, default 30s.
--u,p,: Link database information.

Continue to see if the deadlock table stores information
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| Deadlocks |
| Lock1 |
| Lock2 |
+----------------+
3 Rows in Set (0.00 sec)

Mysql> SELECT * from Deadlocks\g
1. Row ***************************
Server:localhost.localdomai
Ts:2014-01-23 16:04:02
Thread:22
txn_id:0
txn_time:100
User:root
Hostname:localhost
Ip:
Db:test
Tbl:lock2
Idx:gen_clust_index
Lock_type:record
Lock_mode:x
Wait_hold:w
victim:0
Query:select * from Lock2 where id=1 for update
2. Row ***************************
Server:localhost.localdomai
Ts:2014-01-23 16:04:02
Thread:23
txn_id:0
txn_time:54
User:root
Hostname:localhost
Ip:
Db:test
Tbl:lock1
Idx:gen_clust_index
Lock_type:record
Lock_mode:x
Wait_hold:w
Victim:1
Query:select * from Lock1 where id=1 for update
2 rows in Set (0.00 sec)

Mysql>

The test indicates that the deadlock information has been written to the table, with 2 records of the SQL that caused the deadlock, as well as detailed information including libraries, users, and so on. We can use Pt-deadlock-logger to see exactly which SQL causes deadlocks to be conveniently located.

Monitoring deadlocks with Pt-deadlock-logger