MySQL InnoDB: Monitorando Locks

Sessão 1:

mysql> create database test;
Query OK, 1 row affected (0,17 sec)

mysql> use test;
Database changed
mysql> create table t1 (c1 int) Engine=InnoDB;
Query OK, 0 rows affected (2,66 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0,01 sec)

mysql> start transaction;
Query OK, 0 rows affected (0,00 sec)

mysql> update t1 set c1 = 2;
Query OK, 1 row affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Sessão 3, por enquanto não vê nenhum Lock:

mysql> SELECT
    -> TRX.TRX_MYSQL_THREAD_ID,
    -> TRX.TRX_ISOLATION_LEVEL,
    -> TRX.TRX_STARTED,
    -> TRX.TRX_STATE,
    -> PROCESSLIST.USER,
    -> PROCESSLIST.HOST,
    -> PROCESSLIST.DB,
    -> PROCESSLIST.COMMAND,
    -> PROCESSLIST.TIME,
    -> PROCESSLIST.STATE,
    -> LOCK_WAITS.REQUESTING_TRX_ID,
    -> LOCK_WAITS.BLOCKING_TRX_ID,
    -> LOCK_WAITS.BLOCKING_LOCK_ID
    -> FROM
    -> INFORMATION_SCHEMA.INNODB_LOCKS LOCKS,
    -> INFORMATION_SCHEMA.INNODB_TRX TRX,
    -> INFORMATION_SCHEMA.PROCESSLIST PROCESSLIST,
    -> INFORMATION_SCHEMA.INNODB_LOCK_WAITS LOCK_WAITS
    -> WHERE
    -> LOCKS.LOCK_TRX_ID = TRX.TRX_ID
    -> AND TRX.TRX_MYSQL_THREAD_ID = PROCESSLIST.ID;
Empty set, 2 warnings (0,00 sec)

mysql>

Sessão 2, o UPDATE fica em Lock.

mysql> start transaction;
Query OK, 0 rows affected (0,00 sec)

mysql> update t1 set c1 = 3;

Sessão 3, agora vê o Lock:

mysql> SELECT
    -> TRX.TRX_MYSQL_THREAD_ID,
    -> TRX.TRX_ISOLATION_LEVEL,
    -> TRX.TRX_STARTED,
    -> TRX.TRX_STATE,
    -> PROCESSLIST.USER,
    -> PROCESSLIST.HOST,
    -> PROCESSLIST.DB,
    -> PROCESSLIST.COMMAND,
    -> PROCESSLIST.TIME,
    -> PROCESSLIST.STATE,
    -> LOCK_WAITS.REQUESTING_TRX_ID,
    -> LOCK_WAITS.BLOCKING_TRX_ID,
    -> LOCK_WAITS.BLOCKING_LOCK_ID
    -> FROM
    -> INFORMATION_SCHEMA.INNODB_LOCKS LOCKS,
    -> INFORMATION_SCHEMA.INNODB_TRX TRX,
    -> INFORMATION_SCHEMA.PROCESSLIST PROCESSLIST,
    -> INFORMATION_SCHEMA.INNODB_LOCK_WAITS LOCK_WAITS
    -> WHERE
    -> LOCKS.LOCK_TRX_ID = TRX.TRX_ID
    -> AND TRX.TRX_MYSQL_THREAD_ID = PROCESSLIST.ID;
+---------------------+---------------------+---------------------+-----------+------+-----------+------+---------+------+----------+-------------------+-----------------+-------------------+
| TRX_MYSQL_THREAD_ID | TRX_ISOLATION_LEVEL | TRX_STARTED         | TRX_STATE | USER | HOST      | DB   | COMMAND | TIME | STATE    | REQUESTING_TRX_ID | BLOCKING_TRX_ID | BLOCKING_LOCK_ID  |
+---------------------+---------------------+---------------------+-----------+------+-----------+------+---------+------+----------+-------------------+-----------------+-------------------+
|              931580 | REPEATABLE READ     | 2017-01-31 10:12:11 | LOCK WAIT | root | localhost | test | Query   |   15 | updating | 465111676         | 465111523       | 465111523:823:3:2 |
|              931501 | REPEATABLE READ     | 2017-01-31 10:11:57 | RUNNING   | root | localhost | test | Sleep   |   29 |          | 465111676         | 465111523       | 465111523:823:3:2 |
+---------------------+---------------------+---------------------+-----------+------+-----------+------+---------+------+----------+-------------------+-----------------+-------------------+
2 rows in set, 2 warnings (0,00 sec)

mysql>

One comment

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.