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

Deadlock detected on SELECT when only one record being processed

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.14
    • None
    • None
    • None
    • Vanilla Docker image of MariaDb

    Description

      I have a complex situation that I have boiled down to a relatively simple testcase. I have been unable to use any MySQL Unit Test infrastructure as the issue involves having 3 simultaneous clients. I am attaching a JDBC program which can be used as described below to demonstrate the problem. I have confirmed this is not a JDBC issue as I have an equivalent ODBC program that exhibits the same behaviour.

      I have one client session that has successfully done a 'SELECT ... FOR UPDATE' on a record (with autocommit off), and I have two other sessions (with autocommit off) that are doing the same select on the same key. These two other sessions are blocked, waiting for a lock. The session that has the lock, then does a delete of the selected record (in JDBC, it does a resultSet.deleteRow(), in ODBC it does a SQLSetPos(..., SQL_DELETE)). That is fine. Finally, that session does a commit. At this point the two blocked sessions come to life. I am expecting both of the 'SELECT' statements to return successfully with 'no rows found'. The bug is that instead of both doing that, one of them returns successfully with 'no rows found', but the other one produces a 'Deadlock found when trying to get lock; try restarting transaction'.
      The session that produces the 'deadlock' error is always the second item in the waiter queue.
      [Please see at the bottom for more specifics about when the problem occurs and when it doesn't]

      Setup:
      ====
      I have a simple table defined as

      MariaDB [clouddb]> describe BUG_DEMO;
      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | C1    | int(11) | NO   | PRI | NULL    |       |
      | C2    | int(11) | NO   | UNI | NULL    |       |
      +-------+---------+------+-----+---------+-------+
      2 rows in set (0.001 sec)
      

      created using 'CREATE TABLE BUG_DEMO (C1 INTEGER NOT NULL PRIMARY KEY, C2 INTEGER NOT NULL UNIQUE KEY)'

      I then insert a single row into this table using for example "INSERT INTO BUG_DEMO(C1,C2) VALUES(12345, 12345)"

      (These two operations can be done with my attached program by running 'make create' and 'make insert'.

      Reproduce:
      =======
      Create 3 client Unix terminal windows. In each of them type 'make delete' and press enter. The 3 windows each connect to the database and await user action. In all three windows, now press enter. This issues the 'SELECT' in all 3 sessions. The first will execute immediately, and the other two will block. The first of the windows now prompts for user action; and you should press enter. This then does a delete and does another prompt. Notice the other windows remain unchanged. Then pressing enter on the active window causes a commit() to occur. At this point the two 'block'ed windows come to like and exhibit the undesired behaviour.

      Problem Specifics:
      The code in the given reproduces the problem. Some slight variations do change things.
      a) The datatypes of the two columns seems to have no effect on the issue.
      b) Having more columns does not seem to affect the issue
      c) If I remove the 'UNIQUE KEY' from C2, the problem does not occur.
      d) If the select is done on C1 (the primary key) instead of C2, the problem does not occur.
      So, it seems to be specific to when a SELECT is done on a UNIQUE KEYed column that is NOT the primary key.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              stevem Steve Millington
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.