• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table

by admin

While the column mention “tables”, it does not literally mean table locks, but rather the number of tables in which the transaction holds one or more InnoDB row locks. From the MySQL Reference Manual:

TRX_TABLES_LOCKED: Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.)

That it is not actually table locks is also easy to verify with a small test:

1. In one connection run a long running query, e.g. (using the world sample database):

UPDATE world.City SET Population = Population * 1.1 + SLEEP(30) WHERE id = 130;

This will take 30 seconds to run due to the SLEEP(30).

2. In another connection verify that trx_tables_locked is 1 for the ongoing transaction:

mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 1216776
                 trx_state: RUNNING
               trx_started: 2013-08-05 12:29:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 2
                 trx_query: UPDATE world.City SET Population = Population * 1.1 + SLEEP(30) WHERE id = 130
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

3. In the same connection as in 2. and while the ongoing transaction is still running, update another row in the same table:

mysql> UPDATE world.City SET Population = Population * 1.1 WHERE id = 131;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The query in 3. will not block.

Filed Under: mysql

Some more articles you might also be interested in …

  1. Can MySQL Cluster Run Multiple Nodes On a Single Server
  2. MySQL : How To Find the Slowest Queries
  3. Beginners Guide to Management of MySQL Cluster Log Files
  4. Beginners Guide to Implementing Table Maintenance in MySQL
  5. MySQL Error “Too many connections” and how to resolve it
  6. How To Configure Separate Override.conf For Multiple MySQL Instances Using Systemd
  7. MySQL Server Error – “Can’t Create A New Thread (errno 11)”
  8. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  9. What’s a good process to find and eliminate slow queries in MySQL
  10. Understanding MySQL Pluggable Authentication

You May Also Like

Primary Sidebar

Recent Posts

  • qemu-system-x86_64: command not found
  • timedatectl: command not found
  • mpirun.openmpi: command not found
  • startkde: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright