Why does querying information_schema for transactions and lock_waits show a null blocking_trx_id?

(Doc ID 2287672.1)

Last updated on SEPTEMBER 17, 2017

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Symptoms

a null blocking_trx_id is shown when querying information_schema for transactions and lock_waits:

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
+----------------+----------------+----------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+----------------------+-----------------+-----------------+----------------+
| 5400 | 19 | update t1 set id=100 | 5398 | 18 | NULL |
+----------------+----------------+----------------------+-----------------+-----------------+----------------+ 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms