[MDEV-18706] ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap - Jira

Details

Type: Bug
Status: In Review (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.5
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- lock
- performance

Description

mysql_ha_read: Got error 149 when reading table and ER_LOCK_DEADLOCK on concurrent reading/updating versioned partitioned table

Notes:

The test case in the given form fails for in the first few attempts, but it can vary on different machines and builds; run with --repeat=N.
There are a couple of lines in the middle of the test which are unimportant for the error, but they help with the timing, without them it fails less reliably. The can probably be replaced with a proper synchronization.
I am not sure if the deadlock and the error in the log always come together. Please not that the error in the log alone does not make an MTR test fail, because it's currently suppressed in include/mtr_warnings.sql. We are considering removing the suppression, but first we need to check how many of existing tests would be affected. Meanwhile, if you need the test to fail upon the log error, remove the suppression from the file locally.
The problem might be not related to versioning, but I couldn't remove either versioning or partitioning from the test case.

--source include/have_innodb.inc

--source include/have_partition.inc

CREATE TABLE t1 (

  pk INT,

  c CHAR(32),

  PRIMARY KEY (pk)

) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY HASH(pk) PARTITIONS 2;

INSERT INTO t1 VALUES (1,'foo'),(2,'bar');

HANDLER t1 OPEN AS h;

--connect (con1,localhost,root,,test)

--send

  UPDATE t1 SET c = 4;

--connection default

# Two statements below are not required for the scenario,

# but apparently they help with the timing, it fails better with them

--error ER_UNKNOWN_TABLE

HANDLER unknown_handler CLOSE;

--error ER_NO_SUCH_TABLE

HANDLER unknown_table OPEN;

HANDLER h READ `PRIMARY` PREV;

# Cleanup

--connection con1

--reap

--disconnect con1

--connection default

HANDLER h CLOSE;

DROP TABLE t1;

10.3 4946eb7b
mysqltest: At line 26: query 'HANDLER h READ `PRIMARY` PREV' failed: 1213: Deadlock found when trying to get lock; try restarting transaction

CURRENT_TEST: bug.error149b

2019-02-23 21:55:43 19 [ERROR] mysql_ha_read: Got error 149 when reading table 'h'

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

failing_thread.txt
4 kB
2021-07-12 20:38
tampering_thread.txt
4 kB
2021-07-12 20:38
v.test
1 kB
2021-07-26 18:43

Issue Links

includes

MDEV-22475 versioning.update failed in buildbot with wrong result code again

Closed

relates to

MDEV-13697 DB_TRX_ID is not always reset when the history is removed

Closed

MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE

Closed

MDEV-16406 Refactor the InnoDB record locks

Open

MDEV-17073 INSERT…ON DUPLICATE KEY UPDATE became more deadlock-prone

Closed

MDEV-18392 Getting DEADLOCKS frequently on Versioned TABLE

Closed

MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

Closed

MDEV-20612 Improve InnoDB lock_sys scalability

Closed

MDEV-24738 Improve the InnoDB deadlock checker

Closed

MDEV-34877 Port "Bug #11745929 Change lock priority so that the transaction holding S-lock gets X-lock first" fix from MySQL to MariaDB

Closed

MDEV-10962 Deadlock with 3 concurrent DELETEs by unique key

Closed

MDEV-14066 [Draft] Assertion failed: rec_get_deleted_flag(rec, dict_table_is_comp(cursor->index->table))

Closed

MDEV-20274 Deadlock on implicit locking and parallel read

Open

(8 relates to)

Activity

Ascending order - Click to sort in descending order

View 26 older comments

Aleksey Midenkov added a comment - 2023-08-15 10:29

Yes, transaction 1. and 3. is the same transaction trx 2. I didn't write "from step" for brevity. vlad.lesin I don't insist anything, please see task description. You can test it yourself, it does cancel SELECT.

> What exactly subsystem do you mean?

I don't mean exact subsystem. I mean user POV.

Aleksey Midenkov added a comment - 2023-08-15 10:29 Yes, transaction 1. and 3. is the same transaction trx 2. I didn't write "from step" for brevity. vlad.lesin I don't insist anything, please see task description. You can test it yourself, it does cancel SELECT. > What exactly subsystem do you mean? I don't mean exact subsystem. I mean user POV.

Aleksey Midenkov added a comment - 2023-08-15 10:33 - edited

vlad.lesin Can you show the test case with ACID violation, please?

Aleksey Midenkov added a comment - 2023-08-15 10:33 - edited vlad.lesin Can you show the test case with ACID violation, please?

Vladislav Lesin added a comment - 2023-08-15 10:59

midenok, ok, here is modified test with different locking order:

--source include/have_innodb.inc

--source include/have_debug.inc

--source include/have_debug_sync.inc

create or replace table t1 (pk int primary key, x int) engine innodb;

insert into t1 values (3, 0);

start transaction; # trx 1

update t1 set x= 1;

insert into t1 values (2, 1);

connect (con1,localhost,root,,test);

SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL trx2_suspended";

send select * from t1 for update; # trx 2

connection default;

SET DEBUG_SYNC="now WAIT_FOR trx2_suspended";

commit;

connection con1;

--echo # There is no ER_LOCK_DEADLOCK here, as expected

reap;

connection default;

disconnect con1;

drop table t1;

And there is no deadlock error, as expected. As well as there is deadlock error in the original test. What is also expected(I explained it in details in the my previous comment).

Vladislav Lesin added a comment - 2023-08-15 10:59 midenok , ok, here is modified test with different locking order: --source include/have_innodb.inc --source include/have_debug.inc --source include/have_debug_sync.inc create or replace table t1 (pk int primary key, x int ) engine innodb; insert into t1 values ( 3 , 0 ); start transaction; # trx 1 update t1 set x= 1 ; insert into t1 values ( 2 , 1 ); connect (con1,localhost,root,,test); SET DEBUG_SYNC= "lock_wait_before_suspend SIGNAL trx2_suspended" ; send select * from t1 for update; # trx 2 connection default ; SET DEBUG_SYNC= "now WAIT_FOR trx2_suspended" ; commit; connection con1; --echo # There is no ER_LOCK_DEADLOCK here, as expected reap; connection default ; disconnect con1; drop table t1; And there is no deadlock error, as expected. As well as there is deadlock error in the original test. What is also expected(I explained it in details in the my previous comment).

Vladislav Lesin added a comment - 2023-08-15 11:35

midenok,
> Can you show the test case with ACID violation, please?
Ah, I see you did some workaround by moving cursor before its storing and after it's restoring. I need some time to understand how it works with ~~MDEV-20605~~ fix.

Vladislav Lesin added a comment - 2023-08-15 11:35 midenok , > Can you show the test case with ACID violation, please? Ah, I see you did some workaround by moving cursor before its storing and after it's restoring. I need some time to understand how it works with MDEV-20605 fix.

Marko Mäkelä added a comment - 2025-01-28 05:02

vlad.lesin recently improved this area in ~~MDEV-34877~~. Would that change the outcome of the test in the Description?

Marko Mäkelä added a comment - 2025-01-28 05:02 vlad.lesin recently improved this area in MDEV-34877 . Would that change the outcome of the test in the Description?

MariaDB Server

ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap

Details

Description

mysql_ha_read: Got error 149 when reading table and ER_LOCK_DEADLOCK on concurrent reading/updating versioned partitioned table

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration