Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6063

overlocking next-record in REPEATABLE-READ with delete from where x <= num

    XMLWordPrintable

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

          Activity

            People

              jplindst Jan Lindström (Inactive)
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.