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

Strange occurance of Deadlocks while using different where clause

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5.18, 10.11.2, 11.0.2
    • None
    • None
    • Ubuntu 22.04.1 LTS
      Docker version 20.10.21, build baeda1f
      mysql-client: mysql Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

    Description

      I have a table created with this query.

      CREATE TABLE `example` (
        `a` int(11) DEFAULT NULL,
        KEY `a` (`a`)
      ) ENGINE=InnoDB;
      

      This table contains data:

      INSERT INTO `example` VALUES (1),(2);
      

      When I am trying to run two queries at the same time from the different connections I've got Deadlock.
      Console1:

      SET autocommit=0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;start transaction;select a from example where a=4;
      

      Console2:

      SET autocommit=0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;start transaction;select a from example where a=4;
      

      After transaction is started I am running the following in both consoles:

      insert into example (a) values (4);
      

      But if I use a=0 or for example a='v' in WHERE clause deadlock doesn't occur.

      I attached docker-compose and test.sh script to demonstrate this issue.
      test.sh runs two queries from specified file as two different processes 1000 times by default.
      Files named deadlocks_[1,2,3] contains queries causes deadlocks.
      Just run

      docker compose up -d
      ./test.sh local deadlocks_1
      

      And you will get something like

      ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction
      Failed runs: 347

      But

      ./test.sh local no_deadlocks_1
      

      will print

      Failed runs: 0

      Attachments

        Activity

          People

            Unassigned Unassigned
            lebensgefahr Alexey
            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.