systemadmin.es > DBA > MySQL: Listar transacciones bloqueantes

MySQL: Listar transacciones bloqueantes

Anteriormente hemos visto cómo sacar los datos del SHOW PROCESSLIST y además el estado de la transacción InnoDB si es el caso, pero aun así deberemos interpretar la salida para buscar las transacciones que están bloqueando. Si tenemos mucha concurrencia nos pude llegar a ser imposible. Mediante la tabla information_schema.innodb_lock_waits podemos resolver dicho problema

Mediante la tabla information_schema.innodb_lock_waits obtenemos la relación de qué transacción esta bloquenado a que otra:

mysql> desc information_schema.innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |
| requested_lock_id | varchar(81) | NO   |     |         |       |
| blocking_trx_id   | varchar(18) | NO   |     |         |       |
| blocking_lock_id  | varchar(81) | NO   |     |         |       |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Por lo tanto, simplemente deberemos hacer JOINS con information_schema.processlist y information_schema.innodb_trx para obtener la información que nos interesa:

SELECT ps.id, ps.user,ps.host, ps.db, ps.command, ps.time, ps.state, trx.trx_state, ps.info 
FROM information_schema.innodb_lock_waits lw 
INNER JOIN information_schema.innodb_trx trx ON trx.trx_id = lw.blocking_trx_id 
INNER JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.ID;

Por ejemplo, una transacción que se ha quedado sin hacer COMMIT ni ROLLBACK veríamos:

mysql> SELECT ps.id, ps.user,ps.host, ps.db, ps.command, ps.time, ps.state, trx.trx_state, ps.info FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_trx trx ON trx.trx_id = lw.blocking_trx_id INNER JOIN information_schema.processlist ps on trx.trx_mysql_thread_id = ps.ID;
+----+------+-----------+-------+---------+------+-------+-----------+------+
| id | user | host      | db    | command | time | state | trx_state | info |
+----+------+-----------+-------+---------+------+-------+-----------+------+
| 37 | root | localhost | jordi | Sleep   |  293 |       | RUNNING   | NULL |
+----+------+-----------+-------+---------+------+-------+-----------+------+
1 row in set (0.00 sec)

O bien, una que esta aún enviando datos al cliente veríamos:

mysql> SELECT ps.id, ps.user,ps.host, ps.db, ps.command, ps.time, ps.state, trx.trx_state, ps.info  FROM information_schema.innodb_lock_waits lw  INNER JOIN information_schema.innodb_trx trx ON trx.trx_id = lw.blocking_trx_id  INNER JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.ID;
+-------+-------+---------------------+--------------------+---------+------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id    | user  | host                | db                 | command | time | state        | trx_state | info |
+-------+-------+---------------------+--------------------+---------+------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 99117 | datos | 192.168.254.2:51791 | information_schema | Query |  305 | Sending data | RUNNING   | UPDATE BI.zt_products p
SET
  p.NetRevenue = IFNULL(
    (
      SELECT ps.finantialMargin
      FROM fuckyeah.productScale ps
      WHERE
        (
          (ps.levelType = 'idVariationItem' AND ps.levelValue = p.vai_id)
          OR (ps.levelType = 'idVariation' AND ps.levelValue = p.var_id)
          OR (ps.levelType = 'idProduct' AND ps.levelValue = p.pro_id)
        )
        AND (p.quantityRefunded + p.quantityCancelled + p.quantityTotal) BETWEEN ps.minRange AND IF(ps.maxRange = -1, 9999999,ps.maxRange)
      ORDER BY ps.id DESC
      LIMIT 1
    )
    ,0
  )
WHERE p.NetRevenue IS NULL |
+-------+-------+---------------------+--------------------+---------+------+--------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>