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

SELECT LOCK IN SHARE MODE on partition succeeds while SELECT FOR UPDATE in affect

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.2.37, 10.3.28, 10.6.0
    • Fix Version/s: N/A
    • Component/s: Locking, Partitioning
    • Labels:
      None

      Description

      $ mysql-test/mtr --port-base=8000  partition_locking
      Logging: /home/dan/repos/mariadb-server-10.2/mysql-test/mysql-test-run.pl  --port-base=8000 partition_locking
      vardir: /home/dan/repos/build-mariadb-server-10.2/mysql-test/var
      Checking leftover processes...
      Removing old var directory...
      Creating var directory '/home/dan/repos/build-mariadb-server-10.2/mysql-test/var'...
      Checking supported features...
      MariaDB Version 10.2.38-MariaDB
       - SSL connections supported
       - binaries built with wsrep patch
      Collecting tests...
      Installing system database...
       
      ==============================================================================
       
      TEST                                      RESULT   TIME (ms) or COMMENT
      --------------------------------------------------------------------------
       
      worker[1] Using MTR_BUILD_THREAD -200, with reserved ports 6000..6019
      set @start_read_only= @@global.read_only;
      set @start_autocommit= @@global.autocommit;
      set @@global.autocommit= 0;
      CREATE USER test@localhost;
      grant CREATE, SELECT, UPDATE on *.* to test@localhost;
      CREATE USER test2@localhost;
      grant CREATE, SELECT, UPDATE on *.* to test2@localhost;
      connection default;
      CREATE TABLE t1 (
      a char(2) NOT NULL,
      b char(2) NOT NULL,
      c int(10) unsigned NOT NULL,
      d varchar(255) DEFAULT NULL,
      e varchar(1000) DEFAULT NULL,
      PRIMARY KEY (a, b, c),
      KEY (a),
      KEY (a, b)
      ) charset latin1 PARTITION BY KEY (a) PARTITIONS 20;
      INSERT INTO t1 (a, b, c, d, e) VALUES
      ('07', '03', 343, '1', '07_03_343'),
      ('01', '04', 343, '2', '01_04_343'),
      ('01', '06', 343, '3', '01_06_343'),
      ('01', '07', 343, '4', '01_07_343'),
      ('01', '08', 343, '5', '01_08_343'),
      ('01', '09', 343, '6', '01_09_343'),
      ('03', '03', 343, '7', '03_03_343'),
      ('03', '06', 343, '8', '03_06_343'),
      ('03', '07', 343, '9', '03_07_343'),
      ('04', '03', 343, '10', '04_03_343'),
      ('04', '06', 343, '11', '04_06_343'),
      ('05', '03', 343, '12', '05_03_343'),
      ('11', '03', 343, '13', '11_03_343'),
      ('11', '04', 343, '14', '11_04_343')
      ;
      CREATE TABLE t2 (a int, name VARCHAR(50), purchased DATE)
      PARTITION BY RANGE (a)
      (PARTITION p0 VALUES LESS THAN (3),
      PARTITION p1 VALUES LESS THAN (7),
      PARTITION p2 VALUES LESS THAN (9),
      PARTITION p3 VALUES LESS THAN (11));
      INSERT INTO t2 VALUES
      (1, 'desk organiser', '2003-10-15'),
      (2, 'CD player', '1993-11-05'),
      (3, 'TV set', '1996-03-10'),
      (4, 'bookcase', '1982-01-10'),
      (5, 'exercise bike', '2004-05-09'),
      (6, 'sofa', '1987-06-05'),
      (7, 'popcorn maker', '2001-11-22'),
      (8, 'acquarium', '1992-08-04'),
      (9, 'study desk', '1984-09-16'),
      (10, 'lava lamp', '1998-12-25');
      CREATE TABLE t3 SELECT * FROM t1;
      ALTER TABLE t3 ADD PRIMARY KEY (d);
      ALTER TABLE t3 ADD KEY (a);
      ALTER TABLE t3 ADD KEY (a, b);
      ANALYZE TABLE t3;
      Table	Op	Msg_type	Msg_text
      test.t3	analyze	status	OK
      ########################################################################
      connect  con1,localhost,test,,test;
      BEGIN;
      SELECT d,a,b,c FROM t1 partition (p0);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p1);
      d	a	b	c
      7	03	03	343
      8	03	06	343
      9	03	07	343
      SELECT d,a,b,c FROM t1 partition (p2);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p3);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p4);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p5);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p6);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p7);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p8);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p9);
      d	a	b	c
      2	01	04	343
      3	01	06	343
      4	01	07	343
      5	01	08	343
      6	01	09	343
      10	04	03	343
      11	04	06	343
      12	05	03	343
      SELECT d,a,b,c FROM t1 partition (p10);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p11);
      d	a	b	c
      13	11	03	343
      14	11	04	343
      SELECT d,a,b,c FROM t1 partition (p12);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p13);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p14);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p15);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p16);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p17);
      d	a	b	c
      1	07	03	343
      SELECT d,a,b,c FROM t1 partition (p18);
      d	a	b	c
      SELECT d,a,b,c FROM t1 partition (p19);
      d	a	b	c
      SELECT * FROM t1 WHERE a='01' FOR UPDATE ;
      a	b	c	d	e
      01	04	343	2	01_04_343
      01	06	343	3	01_06_343
      01	07	343	4	01_07_343
      01	08	343	5	01_08_343
      01	09	343	6	01_09_343
      SELECT * FROM t3 FORCE INDEX(a) WHERE a='01' FOR UPDATE ;
      a	b	c	d	e
      01	04	343	2	01_04_343
      01	06	343	3	01_06_343
      01	07	343	4	01_07_343
      01	08	343	5	01_08_343
      01	09	343	6	01_09_343
      connect  con2,localhost,test,,test;
      BEGIN;
      SET SESSION innodb_lock_wait_timeout=1;
      #
      #  SHARE ...
      SELECT * FROM t1 LOCK IN SHARE MODE;
      main.partition_locking 'innodb'          [ fail ]
              Test ended at 2021-03-25 16:59:50
       
      CURRENT_TEST: main.partition_locking
      mysqltest: At line 103: query 'SELECT * FROM t1 LOCK IN SHARE MODE' succeeded - should have failed with errno 1205...
      

      Test largely based off https://github.com/mysql/mysql-server/blob/8.0/mysql-test/t/locking_part.test

        Attachments

          Activity

            People

            Assignee:
            danblack Daniel Black
            Reporter:
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration