/
innodb_locked_transactions.html
114 lines (96 loc) · 7.89 KB
/
innodb_locked_transactions.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
<h3>NAME</h3>
innodb_locked_transactions: List locked transactions, the locks they are waiting on and the transactions holding those locks.
<h3>TYPE</h3>
View
<h3>DESCRIPTION</h3>
<p>InnoDB Plugin provides with easy to query INFORMATION_SCHEMA tables. In particular, it offers information on running transactions,
locked transactions and locks.</p>
<p><i>innodb_locked_transactions</i> makes the obvious connection for blocked transactions: it lists blocked transactions, what they
attempt to do, the locks on which they block, the transactions holding those locks and the queries they are executing.
</p>
<p>
The view makes for a simple analysis of <i>"Why do I seem to have so many locks? What's locking what?"</i>
</p>
<h3>STRUCTURE</h3>
<blockquote><pre>
mysql> DESC common_schema.innodb_locked_transactions;
+------------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+---------------------+-------+
| locked_trx_id | varchar(18) | NO | | | |
| locked_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| locked_trx_wait_started | datetime | YES | | NULL | |
| locked_trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| locked_trx_query | varchar(1024) | YES | | NULL | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
| locking_trx_id | varchar(18) | NO | | | |
| locking_trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| locking_trx_wait_started | datetime | YES | | NULL | |
| locking_trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| locking_trx_query | varchar(1024) | YES | | NULL | |
| trx_wait_seconds | bigint(21) | YES | | NULL | |
| sql_kill_blocking_query | varbinary(31) | NO | | | |
| sql_kill_blocking_connection | varbinary(25) | NO | | | |
+------------------------------+---------------------+------+-----+---------------------+-------+
</pre></blockquote>
<h3>SYNOPSIS</h3>
<p>Columns of this view:</p>
<ul>
<li><strong>locked_trx_id</strong>: InnoDB locked transaction ID</li>
<li><strong>locked_trx_started</strong>: time at which locked transaction started</li>
<li><strong>locked_trx_wait_started</strong>: time at which locked transaction got blocked on this lock</li>
<li><strong>locked_trx_mysql_thread_id</strong>: thread ID (mapped to PROCESSLIST)</li>
<li><strong>locked_trx_query</strong>: current blocked query</li>
<li><strong>requested_lock_id</strong>: ID of lock on which transaction is blocked</li>
<li><strong>blocking_lock_id</strong>: ID of lock preventing transaction from getting requested lock</li>
<li><strong>locking_trx_id</strong>: InnoDB blocking transaction ID</li>
<li><strong>locking_trx_started</strong>: time at which blocking transaction started</li>
<li><strong>locking_trx_wait_started</strong>: time at which blocking transaction got blocked (it, too, may be blocked)</li>
<li><strong>locking_trx_mysql_thread_id</strong>: blocking thread ID (mapped to PROCESSLIST)</li>
<li><strong>locking_trx_query</strong>: current blocking query</li>
<li><strong>trx_wait_seconds</strong>: number of seconds blocked transaction is waiting</li>
<li>
<strong>sql_kill_blocking_query</strong>: a <strong>KILL QUERY</strong> statement for the blocking transaction.
<br/>Use with <a href="eval.html">eval()</a> to apply statement.
</li>
<li>
<strong>sql_kill_blocking_connection</strong>: a <strong>KILL</strong> statement for the blocking transaction.
<br/>Use with <a href="eval.html">eval()</a> to apply statement.
</li>
</ul>
<h3>EXAMPLES</h3>
<p>Show info on locked transactions:
<blockquote><pre>mysql> SELECT * FROM common_schema.innodb_locked_transactions;
+---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+
| locked_trx_id | locked_trx_started | locked_trx_wait_started | locked_trx_mysql_thread_id | locked_trx_query | requested_lock_id | blocking_lock_id | locking_trx_id | locking_trx_started | locking_trx_wait_started | locking_trx_mysql_thread_id | locking_trx_query | trx_wait_seconds | sql_kill_blocking_query | sql_kill_blocking_connection |
+---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+
| 9AD2D1811 | 2012-09-28 10:40:25 | 2012-09-28 10:40:25 | 609205 | NULL | 9AD2D1811:499850:82:113 | 9AD2D0E1A:499850:82:113 | 9AD2D0E1A | 2012-09-28 10:40:01 | NULL | 609159 | NULL | 1 | KILL QUERY 609159 | KILL 609159 |
| 9AD2D0FBA | 2012-09-28 10:40:03 | 2012-09-28 10:40:03 | 609196 | UPDATE events SET ts = NOW() WHERE alias = 'all' | 9AD2D0FBA:499850:88:108 | 9AD2D0E1A:499850:88:108 | 9AD2D0E1A | 2012-09-28 10:40:01 | NULL | 609159 | NULL | 23 | KILL QUERY 609159 | KILL 609159 |
+---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+
</pre></blockquote>
In the above example we are unable to catch the query blocking the 2rd transaction. In the first transaction we are also unable to realize the blocked query.
We don't always get all we want...
</p>
<p>See which transactions are blocking, and how many are being blocked:
<blockquote><pre>mysql> SELECT locking_trx_id, COUNT(*) FROM innodb_locked_transactions GROUP BY locking_trx_id;
+----------------+----------+
| locking_trx_id | COUNT(*) |
+----------------+----------+
| 9AD30296C | 2 |
| 9AD30296E | 1 |
+----------------+----------+
</pre></blockquote>
</p>
<p>Kill transactions causing other transactions to block for <strong>30</strong> seconds or more:
<blockquote><pre>mysql> CALL eval('SELECT sql_kill_blocking_query FROM innodb_locked_transactions WHERE trx_wait_seconds >= 30 GROUP BY sql_kill_blocking_query');
</pre></blockquote>
</p>
<h3>ENVIRONMENT</h3>
MySQL 5.1 with InnoDB Plugin installed (with InnoDB INFORMATION_SCHEMA plugins enabled), or MySQL >= 5.5
<h3>SEE ALSO</h3>
<a href="innodb_simple_locks.html">innodb_simple_locks</a>,
<a href="innodb_transactions.html">innodb_transactions</a>,
<a href="innodb_transactions_summary.html">innodb_transactions_summary</a>
<h3>AUTHOR</h3>
Shlomi Noach