maria-discuss team mailing list archive

[Daniel Black <daniel.black@xxxxxxxxxxxxx>]

Anyone see 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) );
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)




-- 
-- 
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.