[MDEV-31489] Strange occurance of Deadlocks while using different where clause Created: 2023-06-16  Updated: 2023-06-16

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Storage Engine - InnoDB
Affects Version/s: 10.5.18, 10.11.2, 11.0.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alexey Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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


Attachments: File deadlock_issue.tar.gz    

 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



 Comments   
Comment by Alexey [ 2023-06-16 ]

This strange behavior occurred on mariadb and mysql of all versions.

Generated at Thu Feb 08 10:24:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.