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

unexpected X lock on Supremum in READ COMMITTED

    XMLWordPrintable

    Details

      Description

      Affects/is observed on 821808c45dd

      Transaction that update,insert,delete a distinctive single record specified with a PK value
      should not have any gap lock. E.g such one as call foo1(); where the table and procedure
      are defined as follows

      CREATE TABLE `t2_text` (
        `a` int(11) NOT NULL,
        `b` int(11) DEFAULT NULL,
        `c` text DEFAULT NULL,
        PRIMARY KEY (`a`)
      ) ENGINE=InnoDB
       
       
      create procedure foo1()
      begin
           declare av int;
           declare ni int;
           declare mk int;
           declare u0,u1,d int;
           declare skip_max int;
       
           set av = ceil(rand()*100);
           set ni = ceil(rand()*10);
           set skip_max = 2;
           set d = av + mod(ceil(rand()*100), skip_max*ni);
           set u0 = av + mod(ceil(rand()*100), skip_max*ni);
           set u1 = av + mod(ceil(rand()*100), skip_max*ni);
       
           while (ni > 0) do
             set mk = mod(ceil(rand()*100),4);
             replace into t2_text values (av, av, repeat('a', mk*1024));
             set ni = ni - 1;
             set av = av + 1 + mod(ceil(rand()*100), skip_max);
           end while;  
           delete from t2_text where a = d;
           update t2_text set a=u1,b=u1 where a = u0;
      end|
      delimiter ;
      

      However a X lock on Supremum record appears when the above `call foo1()` runs concurrently, like

      --connection one
      --send \
           select sleep(0.1);xa start '1'; call foo1(); xa end '1'; xa prepare '1'; select sleep(0.05); xa commit '1'; select sleep(0.1);
       
      --connection two
      --send \
           select sleep(0.1);xa start '2'; call foo1(); xa end '2'; xa prepare '2'; select sleep(0.05); xa commit '2'; select sleep(0.1);
      
      

      so an assert, see the diff file attached, fires after few repeats.

      It also survives MDEV-26682 commit's action to clear the user XA out of gap locks.
      The latter, as it was in MDEV-26682 context, may have as a consequence seemingly non-conflicting XA transactions to become actually conflicting.
      when they replayed on slave.

      The attached diff suggests how to fix this though its idea is merely to work around xa replication issues, and not possible
      isolation ones (when the presence of X on supremum indeed 'unexpected' and can't be justified).

      (Edited to remove unnecessary Unique constraint from `b`)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              vlad.lesin Vladislav Lesin
              Reporter:
              Elkin Andrei Elkin
              Votes:
              2 Vote for this issue
              Watchers:
              12 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.