| Bug #102973 | Record locks are not unlocked when the statement is failed | ||
|---|---|---|---|
| Submitted: | 15 Mar 2021 7:46 | Modified: | 17 Mar 2021 12:59 |
| Reporter: | Cheng Zhou | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[15 Mar 2021 7:46]
Cheng Zhou
[16 Mar 2021 9:25]
Cheng Zhou
transaction_isolation is READ-COMMITTED
[16 Mar 2021 11:29]
Jakub Lopuszanski
Hello!
What version of MySQL are you using?
I can't reproduce it on the latest version.
Here's my attempt:
Client 1:
mysql> drop database test;
Query OK, 5 rows affected (0.23 sec)
mysql> create database db1
-> ;
Query OK, 1 row affected (0.02 sec)
mysql> use db1;
Database changed
mysql> create table t1(a int primary key, b int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select a from t1 where a=5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
mysql> select THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 51 | 17 | db1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 51 | 17 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 |
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)
Client 2:
mysql> use db1;
Database changed
mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
Client 1:
mysql> select THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 51 | 17 | db1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 51 | 17 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | NULL |
+-----------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
[16 Mar 2021 13:39]
MySQL Verification Team
Hi Mr. Zhou, Thank you for your bug report. I am not capable of repeating it either. I am using 8.0.24.
[17 Mar 2021 2:12]
Cheng Zhou
I used 8.0.23. The statement which would fail due to lock timeout should be in a transaction or in non-autocommit mode. The statement in Client 2 in your attempt is auto-committed.
[17 Mar 2021 10:47]
Jakub Lopuszanski
Oops, thank you, Cheng Zhou! Indeed I forgot to run Client 2 in a transaction, so MySQL was smart enough to figure out it doesn't need locking if it's going to autocommitt soon, anyway. I've retried the scenario with the missing `start transaction;` and here's what I've found. Indeed the select THREAD_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; returns many locks, held by the Connection 2. BTW. beware that THREAD_ID column DOES NOT indicate who OWNS the lock - it's about who CREATED the lock - more precisely: the `struct` which holds the lock, as there might be many locks per `struct`. This is actually documented in https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-data-locks-ta... The column indicating who OWNS the lock is ENGINE_TRANSACTION_ID. The difference comes from the implicit-to-explicit lock conversions which a THREAD_ID can perform on behalf of ENGINE_TRANSACTION_ID to convert an implicit lock held by ENGINE_TRANSACTION_ID by writing its TRX_ID into record, into an explicit lock `struct` in memory. This doesn't matter much for your scenario, though, as indeed ENGINE_TRANSACTION_ID indicates that there are locks held by both transactions: ``` mysql> select ENGINE_TRANSACTION_ID,EVENT_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------------------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | ENGINE_TRANSACTION_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------------------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 7203 | 8 | db1 | t1 | NULL | TABLE | IX | GRANTED | NULL | | 7203 | 8 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 | | 7203 | 8 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 | | 7203 | 8 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 | | 7203 | 8 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 | | 7202 | 14 | db1 | t1 | NULL | TABLE | IX | GRANTED | NULL | | 7202 | 14 | db1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 5 | +-----------------------+----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ 7 rows in set (0.00 sec) ``` This seems to be because both transactions are still in RUNNING state: ``` mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 7203 trx_state: RUNNING trx_started: 2021-03-17 11:31:09 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 9 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1200 trx_rows_locked: 5 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL *************************** 2. row *************************** trx_id: 7202 trx_state: RUNNING trx_started: 2021-03-17 11:30:01 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 8 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1200 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 trx_schedule_weight: NULL 2 rows in set (0.00 sec) ``` That is, the transaction of Connection 2 wasn't rolled back. We perform ROLLBACK; from Connection 2, then the locks of 7203 gets released/disappear. That is a lock wait timeout doesn't automatically rollback the transaction, just the most recent query. This is actually documented in https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_tim... > When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). > To have the entire transaction roll back, start the server with the --innodb-rollback-on-timeout option. So IMHO this is not a bug. Let me know if you think otherwise.
[17 Mar 2021 12:59]
MySQL Verification Team
Hi All, I have tested it too and this is not a bug.
