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

innodb.skip_locked_nowait test failing line 185 - SELECT NOWAIT getting ER_LOCK_WAIT_TIMEOUT

    XMLWordPrintable

Details

    • Can result in unexpected behaviour

    Description

      Since MDEV-38019, according to CR, the following is a test failure is occuring (that has not occurred before):

      This is challenging the previous behaviour of correct locking practices.

      CURRENT_TEST: innodb.skip_locked_nowait
      mysqltest: At line 185: query 'SELECT seat_id, state, ST_AsText(pos) FROM t1
      WHERE seat_id = 4 FOR UPDATE NOWAIT' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction
       
      The result from queries just before the failure was:
      < snip >
      ERROR HY000: Lock wait timeout exceeded; try restarting transaction
      SELECT seat_id, state, ST_AsText(pos) FROM t1
      WHERE state = 0 FOR UPDATE SKIP LOCKED;
      seat_id	state	ST_AsText(pos)
      5	0	POINT(3 0)
      6	0	POINT(3 1)
      COMMIT;
      connection default;
      COMMIT;
      connection con1;
      SET @g = ST_GeomFromText('POLYGON((0 0,0 3,3 3,0 3,0 0))');
      SELECT seat_id, state, ST_AsText(pos) FROM t1 FORCE INDEX (pos)
      WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE;
      seat_id	state	ST_AsText(pos)
      4	0	POINT(2 1)
      2	0	POINT(1 1)
      connection default;
      BEGIN;
      SELECT seat_id, state, ST_AsText(pos) FROM t1
      WHERE seat_id = 4 FOR UPDATE NOWAIT;
      

      In this test the WHERE state = 0 AND MBRWithin(pos, @g) FOR UPDATE; query should be obtaining exclusive access on the rows where seat =4 and 2 per the results.

      The concurrent transaction on default connection {{SELECT seat_id, state, ST_AsText(pos) FROM t1
      WHERE seat_id = 4 FOR UPDATE NOWAIT}} should be returning row seat=4 according to the result file. Its now returning ER_LOCK_WAIT_TIMEOUT multiple times per day across many builders. The test cases from MDEV-13115 (10.6 feature of FOR UPDATE NOWAIT).

      The implementation of MDEV-13115 replaces the error DB_LOCK_WAIT_TIMEOUT with DB_SUCCESS in many cases to acheive its functionality.

      Tests where 100% modelled of MySQL tests.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              danblack Daniel Black
              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.