• 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. How to configure resource groups for MySQL Server running on Linux
  2. What are Reserved User Accounts in MySQL
  3. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  4. Beginners Guide to Implementing Table Maintenance in MySQL
  5. What is the purpose of “system user” in MySQL Replication
  6. How To Reset MySQL 8.0 Root Password On Windows
  7. How to List Tables Per Tablespace In MySQL Cluster
  8. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  9. How to Troubleshoot InnoDB Lock Issues
  10. MySQL Error “Too many connections” and how to resolve it

You May Also Like

Primary Sidebar

Recent Posts

  • raw: command not found
  • raw Command Examples in Linux
  • rankmirrors Command Examples in Linux
  • radeontop: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright