Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.1.67, 5.2.14, 5.3.12, 5.5.36, 10.0.10
-
None
-
None
Description
(from maria-discuss list)
Why tx_isolation=REPEATABLE-READ should have a next-record lock on record 3 rather than a record lock?
SESSION 1:
MariaDB [drupal_test]> create table x ( x bigint unsigned not null auto_increment, PRIMARY KEY x(x) ); |
Query OK, 0 rows affected (0.02 sec) |
|
|
MariaDB [drupal_test]> select @@tx_isolation; |
+-----------------+ |
| @@tx_isolation |
|
+-----------------+ |
| REPEATABLE-READ | |
+-----------------+ |
1 row in set (0.00 sec) |
|
|
|
|
MariaDB [drupal_test]> insert into x values (1),(2),(3); |
Query OK, 3 rows affected (0.00 sec) |
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [drupal_test]> begin |
-> ;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [drupal_test]> delete from x where x <= 3; |
Query OK, 3 rows affected (0.00 sec) |
---TRANSACTION 290E30, ACTIVE 12 sec
|
2 lock struct(s), heap size 376, 4 row lock(s), undo log entries 3 |
MySQL thread id 7437, OS thread handle 0x7f37307f5700, query id 3298653 localhost debian-sys-maint sleeping
|
TABLE LOCK table `drupal_test`.`x` trx id 290E30 lock mode IX |
RECORD LOCKS space id 82547 page no 3 n bits 72 index `PRIMARY` of table `drupal_test`.`x` trx id 290E30 lock_mode X |
SESSION 2:
MariaDB [drupal_test]> insert into x values (DEFAULT); |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
And it works in READ-COMMITTED
SESSION 1:
MariaDB [drupal_test]> set tx_isolation='READ-COMMITTED'; begin; delete from x where x <= 3; |
Query OK, 0 rows affected (0.00 sec) |
|
|
Query OK, 0 rows affected (0.00 sec) |
|
|
Query OK, 3 rows affected (0.00 sec) |
SESSION 2:
---TRANSACTION 290E33, ACTIVE 44 sec
|
2 lock struct(s), heap size 376, 3 row lock(s), undo log entries 3 |
MySQL thread id 7437, OS thread handle 0x7f37307f5700, query id 3298661 localhost debian-sys-maint sleeping
|
TABLE LOCK table `drupal_test`.`x` trx id 290E33 lock mode IX |
RECORD LOCKS space id 82547 page no 3 n bits 72 index `PRIMARY` of table `drupal_test`.`x` trx id 290E33 lock_mode X locks rec but not gap |
|
|
MariaDB [drupal_test]> insert into x values (DEFAULT); |
Query OK, 1 row affected (0.00 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Hi, this is desired behavior. For repeatable read we need keep result set consistent i.e. it should remain as 1,2,3 on duration of transaction doing a delete. Thus there should not be possible to insert (0) and currently there is no knowledge that something like (1.5) is not possible. Similarly, in multi-key primary keys it is not trivial to find out if <= key and the first matching row y are the same i.e. is y == key, thus we need to protect possible insert after first matchiing key => take gap lock after (3). In example case we know that next possible value is (4) and thus there is no real need to have gap lock between (3) and (4). Lets assume this same table and same data but delete with <= 8, here we would need gap lock to avoid insert with any of the possible values 4--8. Thus, theoretically we could avoid unnecessary gap locks if we have unique not null primary key consisting only simple data types and the first matching key would match perfectilely to search key and search key would use this primary key. Therefore, only in very limited cases there is real possible room for improvement and in my opinion the time to check if we are hitting one of the limited cases takes more time than (in overral, because this would effect every gap lock request) what we would really save.