Rechercher dans le manuel MySQL
15.14.2.1 Using InnoDB Transaction and Locking Information
This section describes locking information as exposed by the
Performance Schema data_locks
and
data_lock_waits
tables, which
supersede the INFORMATION_SCHEMA
INNODB_LOCKS
and
INNODB_LOCK_WAITS
tables in MySQL
8.0. For similar discussion written in terms of
the older INFORMATION_SCHEMA
tables, see
Using InnoDB Transaction and Locking Information
in MySQL 5.7 Reference Manual.
Identifying Blocking Transactions
It is sometimes helpful to identify which transaction blocks
another. The tables that contain information about
InnoDB
transactions and data locks enable
you to determine which transaction is waiting for another, and
which resource is being requested. (For descriptions of these
tables, see
Section 15.14.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.)
Suppose that three sessions are running concurrently. Each session corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these sessions have issued the following statements, but none has yet committed its transaction:
Session A:
Session B:
Session C:
In this scenario, use the following query to see which transactions are waiting and which transactions are blocking them:
Or, more simply, use the sys
schema
innodb_lock_waits
view:
- waiting_trx_id,
- waiting_pid,
- waiting_query,
- blocking_trx_id,
- blocking_pid,
- blocking_query
- FROM sys.innodb_lock_waits;
If a NULL value is reported for the blocking query, see Identifying a Blocking Query After the Issuing Session Becomes Idle.
waiting trx id | waiting thread | waiting query | blocking trx id | blocking thread | blocking query |
---|---|---|---|---|---|
A4 |
6 |
SELECT b FROM t FOR UPDATE |
A3 |
5 |
SELECT SLEEP(100) |
A5 |
7 |
SELECT c FROM t FOR UPDATE |
A3 |
5 |
SELECT SLEEP(100) |
A5 |
7 |
SELECT c FROM t FOR UPDATE |
A4 |
6 |
SELECT b FROM t FOR UPDATE |
In the preceding table, you can identify sessions by the “waiting query” or “blocking query” columns. As you can see:
Session B (trx id
A4
, thread6
) and Session C (trx idA5
, thread7
) are both waiting for Session A (trx idA3
, thread5
).Session C is waiting for Session B as well as Session A.
You can see the underlying data in the
INFORMATION_SCHEMA
INNODB_TRX
table and Performance
Schema data_locks
and
data_lock_waits
tables.
The following table shows some sample contents of the
INNODB_TRX
table.
trx id | trx state | trx started | trx requested lock id | trx wait started | trx weight | trx mysql thread id | trx query |
---|---|---|---|---|---|---|---|
A3 |
RUNNING |
2008-01-15 16:44:54 |
NULL |
NULL |
2 |
5 |
SELECT SLEEP(100) |
A4 |
LOCK WAIT |
2008-01-15 16:45:09 |
A4:1:3:2 |
2008-01-15 16:45:09 |
2 |
6 |
SELECT b FROM t FOR UPDATE |
A5 |
LOCK WAIT |
2008-01-15 16:45:14 |
A5:1:3:2 |
2008-01-15 16:45:14 |
2 |
7 |
SELECT c FROM t FOR UPDATE |
The following table shows some sample contents of the
data_locks
table.
lock id | lock trx id | lock mode | lock type | lock schema | lock table | lock index | lock data |
---|---|---|---|---|---|---|---|
A3:1:3:2 |
A3 |
X |
RECORD |
test |
t |
PRIMARY |
0x0200 |
A4:1:3:2 |
A4 |
X |
RECORD |
test |
t |
PRIMARY |
0x0200 |
A5:1:3:2 |
A5 |
X |
RECORD |
test |
t |
PRIMARY |
0x0200 |
The following table shows some sample contents of the
data_lock_waits
table.
When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the following steps to determine the blocking query:
Identify the processlist ID of the blocking transaction. In the
sys.innodb_lock_waits
table, the processlist ID of the blocking transaction is theblocking_pid
value.Using the
blocking_pid
, query the MySQL Performance Schemathreads
table to determine theTHREAD_ID
of the blocking transaction. For example, if theblocking_pid
is 6, issue this query:Using the
THREAD_ID
, query the Performance Schemaevents_statements_current
table to determine the last query executed by the thread. For example, if theTHREAD_ID
is 28, issue this query:If the last query executed by the thread is not enough information to determine why a lock is held, you can query the Performance Schema
events_statements_history
table to view the last 10 statements executed by the thread.
Sometimes it is useful to correlate internal
InnoDB
locking information with the
session-level information maintained by MySQL. For example,
you might like to know, for a given InnoDB
transaction ID, the corresponding MySQL session ID and name of
the session that may be holding a lock, and thus blocking
other transactions.
The following output from the
INFORMATION_SCHEMA
INNODB_TRX
table and Performance
Schema data_locks
and
data_lock_waits
tables is taken
from a somewhat loaded system. As can be seen, there are
several transactions running.
The following data_locks
and
data_lock_waits
tables show that:
Transaction
77F
(executing anINSERT
) is waiting for transactions77E
,77D
, and77B
to commit.Transaction
77E
(executing anINSERT
) is waiting for transactions77D
and77B
to commit.Transaction
77D
(executing anINSERT
) is waiting for transaction77B
to commit.Transaction
77B
(executing anINSERT
) is waiting for transaction77A
to commit.Transaction
77A
is running, currently executingSELECT
.Transaction
E56
(executing anINSERT
) is waiting for transactionE55
to commit.Transaction
E55
(executing anINSERT
) is waiting for transaction19C
to commit.Transaction
19C
is running, currently executing anINSERT
.
There may be inconsistencies between queries shown in the
INFORMATION_SCHEMA
PROCESSLIST
and
INNODB_TRX
tables. For an
explanation, see
Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking
Information”.
The following table shows the contents of the
PROCESSLIST
table for a system
running a heavy workload.
ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
---|---|---|---|---|---|---|---|
384 |
root |
localhost |
test |
Query |
10 |
update |
INSERT INTO t2 VALUES … |
257 |
root |
localhost |
test |
Query |
3 |
update |
INSERT INTO t2 VALUES … |
130 |
root |
localhost |
test |
Query |
0 |
update |
INSERT INTO t2 VALUES … |
61 |
root |
localhost |
test |
Query |
1 |
update |
INSERT INTO t2 VALUES … |
8 |
root |
localhost |
test |
Query |
1 |
update |
INSERT INTO t2 VALUES … |
4 |
root |
localhost |
test |
Query |
0 |
preparing |
SELECT * FROM PROCESSLIST |
2 |
root |
localhost |
test |
Sleep |
566 |
|
NULL |
The following table shows the contents of the
INNODB_TRX
table for a system
running a heavy workload.
trx id | trx state | trx started | trx requested lock id | trx wait started | trx weight | trx mysql thread id | trx query |
---|---|---|---|---|---|---|---|
77F |
LOCK WAIT |
2008-01-15 13:10:16 |
77F |
2008-01-15 13:10:16 |
1 |
876 |
INSERT INTO t09 (D, B, C) VALUES … |
77E |
LOCK WAIT |
2008-01-15 13:10:16 |
77E |
2008-01-15 13:10:16 |
1 |
875 |
INSERT INTO t09 (D, B, C) VALUES … |
77D |
LOCK WAIT |
2008-01-15 13:10:16 |
77D |
2008-01-15 13:10:16 |
1 |
874 |
INSERT INTO t09 (D, B, C) VALUES … |
77B |
LOCK WAIT |
2008-01-15 13:10:16 |
77B:733:12:1 |
2008-01-15 13:10:16 |
4 |
873 |
INSERT INTO t09 (D, B, C) VALUES … |
77A |
RUNNING |
2008-01-15 13:10:16 |
NULL |
NULL |
4 |
872 |
SELECT b, c FROM t09 WHERE … |
E56 |
LOCK WAIT |
2008-01-15 13:10:06 |
E56:743:6:2 |
2008-01-15 13:10:06 |
5 |
384 |
INSERT INTO t2 VALUES … |
E55 |
LOCK WAIT |
2008-01-15 13:10:06 |
E55:743:38:2 |
2008-01-15 13:10:13 |
965 |
257 |
INSERT INTO t2 VALUES … |
19C |
RUNNING |
2008-01-15 13:09:10 |
NULL |
NULL |
2900 |
130 |
INSERT INTO t2 VALUES … |
E15 |
RUNNING |
2008-01-15 13:08:59 |
NULL |
NULL |
5395 |
61 |
INSERT INTO t2 VALUES … |
51D |
RUNNING |
2008-01-15 13:08:47 |
NULL |
NULL |
9807 |
8 |
INSERT INTO t2 VALUES … |
The following table shows the contents of the
data_lock_waits
table for a
system running a heavy
workload.
requesting trx id | requested lock id | blocking trx id | blocking lock id |
---|---|---|---|
77F |
77F:806 |
77E |
77E:806 |
77F |
77F:806 |
77D |
77D:806 |
77F |
77F:806 |
77B |
77B:806 |
77E |
77E:806 |
77D |
77D:806 |
77E |
77E:806 |
77B |
77B:806 |
77D |
77D:806 |
77B |
77B:806 |
77B |
77B:733:12:1 |
77A |
77A:733:12:1 |
E56 |
E56:743:6:2 |
E55 |
E55:743:6:2 |
E55 |
E55:743:38:2 |
19C |
19C:743:38:2 |
The following table shows the contents of the
data_locks
table for a system
running a heavy workload.
lock id | lock trx id | lock mode | lock type | lock schema | lock table | lock index | lock data |
---|---|---|---|---|---|---|---|
77F:806 |
77F |
AUTO_INC |
TABLE |
test |
t09 |
NULL |
NULL |
77E:806 |
77E |
AUTO_INC |
TABLE |
test |
t09 |
NULL |
NULL |
77D:806 |
77D |
AUTO_INC |
TABLE |
test |
t09 |
NULL |
NULL |
77B:806 |
77B |
AUTO_INC |
TABLE |
test |
t09 |
NULL |
NULL |
77B:733:12:1 |
77B |
X |
RECORD |
test |
t09 |
PRIMARY |
supremum pseudo-record |
77A:733:12:1 |
77A |
X |
RECORD |
test |
t09 |
PRIMARY |
supremum pseudo-record |
E56:743:6:2 |
E56 |
S |
RECORD |
test |
t2 |
PRIMARY |
0, 0 |
E55:743:6:2 |
E55 |
X |
RECORD |
test |
t2 |
PRIMARY |
0, 0 |
E55:743:38:2 |
E55 |
S |
RECORD |
test |
t2 |
PRIMARY |
1922, 1922 |
19C:743:38:2 |
19C |
X |
RECORD |
test |
t2 |
PRIMARY |
1922, 1922 |
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-information-schema-examples.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.