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

Range Locking: individual rows are locked when scanning PK

    XMLWordPrintable

Details

    Description

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int primary key);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t1 (
        pk int primary key,
        a int
      );
      insert into t1 select a,a from one_k;
      

      begin;
      update t1 set a=a+10 where pk between 10 and 20;
      select * from information_schema.rocksdb_locks;
      

      +------------------+----------------+-----------------------------------------+------+                                                       
      | COLUMN_FAMILY_ID | TRANSACTION_ID | KEY                                     | MODE |                                                       
      +------------------+----------------+-----------------------------------------+------+                                                       
      |                0 |              6 | 000000010680000015                      | X    |                                                       
      |                0 |              6 | 000000010680000014                      | X    |                                                       
      |                0 |              6 | 000000010680000014                      | X    |                                                       
      |                0 |              6 | 000000010680000013                      | X    |                                                       
      |                0 |              6 | 000000010680000013                      | X    |                                                       
      |                0 |              6 | 000000010680000012                      | X    |                                                       
      |                0 |              6 | 000000010680000012                      | X    |                                                       
      |                0 |              6 | 000000010680000011                      | X    |                                                       
      |                0 |              6 | 000000010680000011                      | X    |                                                       
      |                0 |              6 | 000000010680000010                      | X    |                                                       
      |                0 |              6 | 000000010680000010                      | X    |                                                       
      |                0 |              6 | 00000001068000000f                      | X    |                                                       
      |                0 |              6 | 00000001068000000f                      | X    |                                                       
      |                0 |              6 | 00000001068000000e                      | X    |                                                       
      |                0 |              6 | 00000001068000000e                      | X    |                                                       
      |                0 |              6 | 00000001068000000d                      | X    |
      |                0 |              6 | 00000001068000000d                      | X    |
      |                0 |              6 | 00000001068000000c                      | X    |
      |                0 |              6 | 00000001068000000c                      | X    |
      |                0 |              6 | 00000001068000000b                      | X    |
      |                0 |              6 | 00000001068000000b                      | X    |
      |                0 |              6 | 00000001068000000a                      | X    |
      |                0 |              6 | 00000001068000000a                      | X    |
      |                0 |              6 | 00000001068000000a - 010000010680000014 | X    |
      +------------------+----------------+-----------------------------------------+------+
      

      The output shows overlapping ranges because we're in STO-mode.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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