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.