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

LOCK IN SHARE MODE does not acquire lock correctly

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.2
    • None
    • Locking

    Description

      The schema is:

      create table table0 (pkId integer, pkAttr0 integer, pkAttr1 integer, coAttr0_0 integer, coAttr0_1 varchar(140), primary key(pkAttr0, pkAttr1));
      alter table table0 add index table0index_pk(pkAttr0, pkAttr1);
      alter table table0 add index table0index_commAttr0(coAttr0_0, coAttr0_1);
      insert into table0 (pkId,pkAttr0,pkAttr1,coAttr0_0,coAttr0_1) values ("29","147","177","73609","aWVjQJkyE5");

      Then execution queries below:

      session1 > begin;
      session1 > select * from table0 where pkAttr1=177 for update; --acquire exclusive lock on the record
      session2 > select pkAttr0 , pkAttr1 , coAttr0_0 , coAttr0_1 from table0 where pkAttr1 = 177 lock in share mode; – execute without blocking
      session2 > select * from table0 where pkAttr1 = 177 lock in share mode; – select the same record, but blocked

      Since session1 acquire a lock on the record(pkAttr1=177), both of select in session2 should be blocked(because they use lock in share mode), but the first one is not blocked.

      We explain the plans of queries in session2 and find that the first one uses index but the second one does not use index. Maybe LOCK IN SHARE MODE does not lock the index entry correctly.

      Attachments

        Activity

          People

            Unassigned Unassigned
            wengsy Weng Siyang
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.