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.