[MDEV-36112] Deadlock Issue with Foreign Key Constraints on concurrent execution - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Not a Bug
Affects Version/s: 10.5.12, 10.11.10
Fix Version/s: N/A
Component/s: Locking, Storage Engine - InnoDB
Labels:
- Concurrency
- deadlock
- foreign-keys
- index
- innodb
Environment:
Operating System: RHEL
InnoDB Storage Engine enabled.

Description

Problem Summary:
We are experiencing a deadlock issue in MariaDB when executing concurrent INSERT and UPDATE operations on two tables. TO give you a sample of what we are doing. Assume having two tables (PRODUCT and PRODUCT_LEDGER) that are related via a foreign key. The problem occurs when two different sessions are running the same stored procedure concurrently, which inserts data into PRODUCT_LEDGER and updates a PRODUCT record. Despite the operations being logically ordered, MariaDB detects a deadlock and rolls back one of the transactions.

Details of the Tables and Procedure:
Tables:
CREATE TABLE `PRODUCT` (
`PRODUCT_NUM` VARCHAR(35) NOT NULL COLLATE 'latin1_general_ci',
`PRODUCT_NAME` VARCHAR(128) NULL COLLATE 'latin1_general_ci',
`BALANCE_DUE` DECIMAL(20,2) NULL,
PRIMARY KEY (`PRODUCT_NUM`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB;

CREATE TABLE `PRODUCT_LEDGER` (
`PRODUCT_LEDGER_NUM` VARCHAR(35) NOT NULL COLLATE 'latin1_general_ci',
`PAY_TO_PRODUCT_NUM` VARCHAR(35) NULL COLLATE 'latin1_general_ci',
PRIMARY KEY (`PRODUCT_LEDGER_NUM`),
INDEX `fk_ple_pay_to_product` (`PAY_TO_PRODUCT_NUM`),
CONSTRAINT `fk_ple_pay_to_product` FOREIGN KEY (`PAY_TO_PRODUCT_NUM`) REFERENCES `PRODUCT` (`PRODUCT_NUM`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB;

Stored Procedure:
DELIMITER //

CREATE DEFINER=`root`@`%` PROCEDURE `TestDeadlock`(
IN `vEntryNum` VARCHAR(30)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE i INT DEFAULT 0;

DECLARE exit handler FOR sqlexception
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @text, @errno;
ROLLBACK;
END;

START TRANSACTION;

INSERT INTO product_ledger(PRODUCT_LEDGER_NUM, PAY_TO_PRODUCT_NUM)
VALUES (vEntryNum, '2020');

SELECT SLEEP(5);

UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020';

COMMIT;
END //
DELIMITER ;

Steps to Reproduce:
Execute TestDeadlock('234235') in Session 1.
Execute TestDeadlock('546674') in Session 2 concurrently.
Both sessions will run the procedure and attempt to insert into PRODUCT_LEDGER and update PRODUCT.
Deadlock occurs, and MariaDB rolls back one of the transactions.

Observations:
The deadlock occurs despite the logical order of the operations.
Both sessions seem to be waiting for each other on the same row in the PRODUCT table, but MariaDB detects a deadlock and chooses to roll back one transaction instead of waiting.
The issue appears to be related to InnoDB's row-level locking and foreign key constraints.

Questions:
Why does MariaDB not simply wait for the lock instead of detecting a deadlock?
Are there any specific configurations or best practices to avoid deadlocks in this scenario without changing the query logic?

MariaDB Version:
MariaDB 10.5.12, 10.11

Attachments

Issue Links

relates to

MDEV-35957 Defer Foreign key constraint check

Open

Activity

Descending order - Click to sort in ascending order

Marko Mäkelä added a comment - 2025-02-21 08:39

This is something that we need to retest when implementing deferred referential constraint checks (MDEV-35957).

Marko Mäkelä added a comment - 2025-02-21 08:39 This is something that we need to retest when implementing deferred referential constraint checks ( MDEV-35957 ).

Marko Mäkelä added a comment - 2025-02-21 07:33

I am not very familiar with how other RDBMS handle referential constraints, but I could imagine that this is a limitation of the InnoDB implementation. Before Heikki Tuuri started to develop InnoDB in 1994, he was an early employee of Solid. Their RDBMS when I used it for a university course back in 1995 or 1996 featured deferred constraint checks, that is, referential constraints were not enforced immediately, but you could get errors at COMMIT time. InnoDB would not allow a referential constraint to be violated at any time, that is, a transaction would not be allowed to first insert into a child table and then into a parent. In the version of Solid that I used, this would be fine.

I can imagine that in a database that supports deferred checks of referential constraints, it is not necessary for an INSERT into a child table to acquire a shared lock on the record in the parent table. Such locking reads could be deferred to transaction COMMIT time, in a “certification phase”. In InnoDB, COMMIT is basically instantaneous, merely changing the state of the transaction in the undo page header.

In the scenario of this bug report, with an alternative implementation of referential constraints, the first lock that each transaction would acquire on the parent table would be an exclusive lock. There would not be any deadlock then, because the transactions would not be blocking each other (via the shared locks).

I think that the deadlocks could be avoided if you swap the INSERT and UPDATE operations. In that case, during the INSERT into the child table, the transaction would already hold an exclusive lock on the parent table. If the application logic does not easily allow swapping the operations, then you could add an extra step of SELECT…FOR UPDATE on the parent table to have the parent row exclusively locked before the INSERT of the child row. In general, one recipe for avoiding deadlocks is to use coarser locking. In this case, we’re only talking about acquiring an exclusive lock on the parent table record upfront, instead of a shared lock. In some other case, you might need to escalate to table level locking. That is a bit tricky in MariaDB; in MDEV-24813 we found out that you’d need SET autocommit=0 in order to use LOCK TABLE within an InnoDB transaction.

Marko Mäkelä added a comment - 2025-02-21 07:33 I am not very familiar with how other RDBMS handle referential constraints, but I could imagine that this is a limitation of the InnoDB implementation. Before Heikki Tuuri started to develop InnoDB in 1994, he was an early employee of Solid . Their RDBMS when I used it for a university course back in 1995 or 1996 featured deferred constraint checks, that is, referential constraints were not enforced immediately, but you could get errors at COMMIT time. InnoDB would not allow a referential constraint to be violated at any time, that is, a transaction would not be allowed to first insert into a child table and then into a parent. In the version of Solid that I used, this would be fine. I can imagine that in a database that supports deferred checks of referential constraints, it is not necessary for an INSERT into a child table to acquire a shared lock on the record in the parent table. Such locking reads could be deferred to transaction COMMIT time, in a “certification phase”. In InnoDB, COMMIT is basically instantaneous, merely changing the state of the transaction in the undo page header. In the scenario of this bug report, with an alternative implementation of referential constraints, the first lock that each transaction would acquire on the parent table would be an exclusive lock. There would not be any deadlock then, because the transactions would not be blocking each other (via the shared locks). I think that the deadlocks could be avoided if you swap the INSERT and UPDATE operations. In that case, during the INSERT into the child table, the transaction would already hold an exclusive lock on the parent table. If the application logic does not easily allow swapping the operations, then you could add an extra step of SELECT…FOR UPDATE on the parent table to have the parent row exclusively locked before the INSERT of the child row. In general, one recipe for avoiding deadlocks is to use coarser locking. In this case, we’re only talking about acquiring an exclusive lock on the parent table record upfront, instead of a shared lock. In some other case, you might need to escalate to table level locking. That is a bit tricky in MariaDB; in MDEV-24813 we found out that you’d need SET autocommit=0 in order to use LOCK TABLE within an InnoDB transaction.

Jaswanth added a comment - 2025-02-21 07:08

If it is a classical deadlock issue, how come sqlserver/mssql (considering as RDBMS database) handles it without showing any deadlock ? Is it something the issue with mariadb handling of locking ? If that is the case, how can we resolve our logic to avoid deadlocks while running concurrently.

Jaswanth added a comment - 2025-02-21 07:08 If it is a classical deadlock issue, how come sqlserver/mssql (considering as RDBMS database) handles it without showing any deadlock ? Is it something the issue with mariadb handling of locking ? If that is the case, how can we resolve our logic to avoid deadlocks while running concurrently.

Vladislav Lesin added a comment - 2025-02-20 16:51 - edited

I agree with marko's analysis. ~~MDEV-34877~~ case is the following: S1 X2 X1, and we grant X1, as X2 is waiting for S1 anyway, so we can avoid unnecessary deadlock. The current case is the following: S1 S2 X1 X2. S1 and S2 don't conflict each other, so they are both granted. X1 conflicts with S2, we can't grant X1, as S2 has already been granted. As well as we can't grant X2, as it conflicts with granted S1. So this is classical deadlock, and ~~MDEV-34877~~ fix doesn't cover it.

Vladislav Lesin added a comment - 2025-02-20 16:51 - edited I agree with marko 's analysis. MDEV-34877 case is the following: S1 X2 X1, and we grant X1, as X2 is waiting for S1 anyway, so we can avoid unnecessary deadlock. The current case is the following: S1 S2 X1 X2. S1 and S2 don't conflict each other, so they are both granted. X1 conflicts with S2, we can't grant X1, as S2 has already been granted. As well as we can't grant X2, as it conflicts with granted S1. So this is classical deadlock, and MDEV-34877 fix doesn't cover it.

Marko Mäkelä added a comment - 2025-02-20 10:24

I ran elenst’s test with ./mtr --rr, did killall -ABRT mariadbd and analyzed the first reported deadlock in rr replay. It is between Thread 2 (transaction ID 0x1a) and Thread 13 (transaction ID 0x1b) in my trace. One of the locks was acquired in a foreign key check:

11.4 ef966af801afc2a07222b5df65dddd52c77431dd
Thread 2 hit Hardware watchpoint 8: -location (byte)(c_lock_info.conflicting+1)

Old value = 0x0
New value = 0x4
lock_rec_set_nth_bit (i=0x2, lock=0x7f7330201780) at /home/marko/11.4/storage/innobase/include/lock0priv.inl:100
100 lock->trx->lock.n_rec_locks++;
(rr) bt
#0 lock_rec_set_nth_bit (i=0x2, lock=0x7f7330201780) at /home/marko/11.4/storage/innobase/include/lock0priv.inl:100
#1 lock_rec_create (c_lock_info=@0x63dbc68b4f10: {conflicting = 0x0, insert_after = 0x0}, type_mode=type_mode@entry=0x402, page_id=<optimized out>, page_id@entry={m_id = 0x800000003}, page=<optimized out>,
heap_no=heap_no@entry=0x2, index=index@entry=0x7f7300041dc8, trx=0x7f7330201680, holds_trx_mutex=0x0) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1551
#2 0x000063dbc64a548a in lock_rec_lock (impl=impl@entry=0x0, mode=0x402, block=0x7f732be01e00, heap_no=0x2, index=0x7f7300041dc8, thr=0x7f7308049b10) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:2020
#3 0x000063dbc64a65bd in lock_clust_rec_read_check_and_lock (flags=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, mode=<optimized out>,
gap_mode=0x400, thr=0x7f7308049b10) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:6569
#4 0x000063dbc64f7c8a in row_ins_set_shared_rec_lock (thr=<optimized out>, offsets=<optimized out>, index=<optimized out>, rec=<optimized out>, block=<optimized out>, type=<optimized out>)
at /home/marko/11.4/storage/innobase/row/row0ins.cc:1410
#5 row_ins_set_shared_rec_lock (type=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, thr=<optimized out>)
at /home/marko/11.4/storage/innobase/row/row0ins.cc:1395
#6 0x000063dbc64fa363 in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0x1, foreign=foreign@entry=0x7f730003e578, table=table@entry=0x7f7300144588, entry=<optimized out>,
thr=thr@entry=0x7f7308049b10) at /home/marko/11.4/storage/innobase/row/row0ins.cc:1712
#7 0x000063dbc64fbab0 in row_ins_check_foreign_constraints (table=0x7f7300144588, index=index@entry=0x7f730004d818, pk=pk@entry=0x0, entry=entry@entry=0x7f730804b6f8, thr=thr@entry=0x7f7308049b10)
at /home/marko/11.4/storage/innobase/row/row0ins.cc:1953
#8 0x000063dbc64fc011 in row_ins_sec_index_entry (index=0x7f730004d818, entry=entry@entry=0x7f730804b6f8, thr=thr@entry=0x7f7308049b10, check_foreign=check_foreign@entry=0x1)
at /home/marko/11.4/storage/innobase/row/row0ins.cc:3338
#9 0x000063dbc64fe29d in row_ins_index_entry (thr=0x7f7308049b10, entry=<optimized out>, index=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3429
#10 row_ins_index_entry_step (thr=<optimized out>, node=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3593
#11 row_ins (thr=0x7f7308049b10, node=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3710
#12 row_ins_step (thr=thr@entry=0x7f7308049b10) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3839
#13 0x000063dbc650c69e in row_insert_for_mysql (mysql_rec=mysql_rec@entry=0x7f7308047bf8 "\376\005a-100", prebuilt=0x7f7308049408, ins_mode=ROW_INS_NORMAL)
at /home/marko/11.4/storage/innobase/row/row0mysql.cc:1295
#14 0x000063dbc6476817 in ha_innobase::write_row (this=0x7f73080473e0, record=0x7f7308047bf8 "\376\005a-100") at /home/marko/11.4/storage/innobase/handler/ha_innodb.cc:7832

This is part of call TestDeadLock('a-100'), and the record is (PRODUCT_LEDGER_NUM,DB_TRX_ID,DB_ROLL_PTR,PAY_TO_PRODUCT_NUM)=('2020',0x14,(insert),'pro') in the clustered index of the table product_ledger. Next, Thread 2 hits a conflict with a shared lock that was acquired by Thread 13 (transaction ID 0x1b) on the parent table product when it had executed the INSERT statement into the child table product_ledger:

11.4 ef966af801afc2a07222b5df65dddd52c77431dd
Thread 2 hit Hardware watchpoint 6: -location cycle.lock.wait_lock.m._M_b

Old value = {_M_p = 0x0}
New value = {_M_p = 0x7f7330201800}
lock_rec_create (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=<optimized out>, type_mode@entry=0x503, page_id=<optimized out>, page_id@entry={m_id = 0x800000003},
page=<optimized out>, heap_no=<optimized out>, index=<optimized out>, trx=<optimized out>, holds_trx_mutex=<optimized out>) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1577
(rr) bt
#0 lock_rec_create (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=<optimized out>, type_mode@entry=0x503, page_id=<optimized out>,
page_id@entry={m_id = 0x800000003}, page=<optimized out>, heap_no=<optimized out>, index=<optimized out>, trx=<optimized out>, holds_trx_mutex=<optimized out>)
at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1577
#1 0x000063dbc64a4f04 in lock_rec_enqueue_waiting (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=type_mode@entry=0x403, id=id@entry={m_id = 0x800000003},
page=<optimized out>, heap_no=heap_no@entry=0x2, index=index@entry=0x7f7300041dc8, thr=0x7f7300040408, prdt=0x0) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1639
#2 0x000063dbc64a54b2 in lock_rec_lock (impl=impl@entry=0x0, mode=0x403, block=0x7f732be01e00, heap_no=0x2, index=0x7f7300041dc8, thr=<optimized out>)
at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1977
#3 0x000063dbc64a65bd in lock_clust_rec_read_check_and_lock (flags=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, mode=<optimized out>,
gap_mode=0x400, thr=0x7f7300040408) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:6569
#4 0x000063dbc65220d0 in sel_set_rec_lock (mtr=0x7f73306b0c00, thr=0x7f7300040408, type=<optimized out>, mode=<optimized out>, offsets=<optimized out>, index=0x7f7300041dc8, rec=0x7f732bec4080 "2020",
pcur=0x7f730003fbf0) at /home/marko/11.4/storage/innobase/row/row0sel.cc:1354
#5 row_search_mvcc (buf=buf@entry=0x7f7300147890 "\377", mode=PAGE_CUR_GE, prebuilt=0x7f730003fa28, match_mode=<optimized out>, direction=direction@entry=0x0)
at /home/marko/11.4/storage/innobase/row/row0sel.cc:5244
#6 0x000063dbc6475f92 in ha_innobase::index_read (this=0x7f7300147078, buf=0x7f7300147890 "\377", key_ptr=0x7f7308055630 "\004", key_len=0x25, find_flag=<optimized out>)
at /home/marko/11.4/storage/innobase/handler/ha_innodb.cc:9056
#7 0x000063dbc622a17a in handler::ha_index_read_map (this=this@entry=0x7f7300147078, buf=0x7f7300147890 "\377", key=0x7f7308055630 "\004", keypart_map=<optimized out>, find_flag=<optimized out>)
at /home/marko/11.4/sql/handler.cc:3792
#8 0x000063dbc622f444 in handler::read_range_first (this=0x7f7300147078, start_key=0x7f73001471c8, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>)
at /home/marko/11.4/sql/handler.cc:6949
#9 0x000063dbc611c28d in handler::multi_range_read_next (this=0x7f7300147078, range_info=range_info@entry=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:590
#10 0x000063dbc611c304 in Mrr_simple_index_reader::get_next (this=0x7f7300147710, range_info=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:627
#11 0x000063dbc611d692 in DsMrr_impl::dsmrr_next (this=0x7f73001475c0, range_info=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:1731
#12 0x000063dbc5ee046a in QUICK_RANGE_SELECT::get_next (this=0x7f73080553f0) at /home/marko/11.4/sql/opt_range.cc:13265
#13 0x000063dbc5f0b2cd in rr_quick (info=0x7f73306b1300) at /home/marko/11.4/sql/records.cc:402
#14 0x000063dbc60a43c7 in READ_RECORD::read_record (this=0x7f73306b1300) at /home/marko/11.4/sql/records.h:81
#15 Sql_cmd_update::update_single_table (this=0x7f73080344d8, thd=0x7f7308004ce8) at /home/marko/11.4/sql/sql_update.cc:929

This lock creation is part of the UPDATE product statement. Both transactions have executed an INSERT INTO product_ledger statement and Thread 2 is hitting a conflict while executing UPDATE product. At the time this wait starts, Thread 13 is still executing SELECT SLEEP(5). A little later, Thread 13 starts to execute UPDATE product and will be chosen a deadlock victim.

To me, this looks like a classical deadlock anti-pattern that the RDBMS cannot do anything about, other than reporting the deadlock. Both transactions are first acquiring a shared lock on the only record of the parent table product as part of a FOREIGN KEY check during the INSERT into the child table, and they will get into a deadlock when trying to upgrade their shared lock on the parent table to an exclusive one for the UPDATE statement.

The scenario of ~~MDEV-34877~~ is simpler, because one of the participating transactions would start right away with an exclusive lock request, instead of starting with a shared lock and then trying to upgrade to exclusive. Unlike in ~~MDEV-34877~~, both transactions are in fact blocked by each other here, and a deadlock is inevitable. In ~~MDEV-34877~~, the reasonable course of action is to let the shared-lock holder proceed with its lock upgrade to exclusive, even though the other transaction, which is not holding any conflicting lock, started to wait for an exclusive lock earlier.

Marko Mäkelä added a comment - 2025-02-20 10:24 I ran elenst ’s test with ./mtr --rr , did killall -ABRT mariadbd and analyzed the first reported deadlock in rr replay . It is between Thread 2 (transaction ID 0x1a) and Thread 13 (transaction ID 0x1b) in my trace. One of the locks was acquired in a foreign key check: 11.4 ef966af801afc2a07222b5df65dddd52c77431dd Thread 2 hit Hardware watchpoint 8: -location *(byte*)(c_lock_info.conflicting+1) Old value = 0x0 New value = 0x4 lock_rec_set_nth_bit (i=0x2, lock=0x7f7330201780) at /home/marko/11.4/storage/innobase/include/lock0priv.inl:100 100 lock->trx->lock.n_rec_locks++; (rr) bt #0 lock_rec_set_nth_bit (i=0x2, lock=0x7f7330201780) at /home/marko/11.4/storage/innobase/include/lock0priv.inl:100 #1 lock_rec_create (c_lock_info=@0x63dbc68b4f10: {conflicting = 0x0, insert_after = 0x0}, type_mode=type_mode@entry=0x402, page_id=<optimized out>, page_id@entry={m_id = 0x800000003}, page=<optimized out>, heap_no=heap_no@entry=0x2, index=index@entry=0x7f7300041dc8, trx=0x7f7330201680, holds_trx_mutex=0x0) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1551 #2 0x000063dbc64a548a in lock_rec_lock (impl=impl@entry=0x0, mode=0x402, block=0x7f732be01e00, heap_no=0x2, index=0x7f7300041dc8, thr=0x7f7308049b10) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:2020 #3 0x000063dbc64a65bd in lock_clust_rec_read_check_and_lock (flags=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, mode=<optimized out>, gap_mode=0x400, thr=0x7f7308049b10) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:6569 #4 0x000063dbc64f7c8a in row_ins_set_shared_rec_lock (thr=<optimized out>, offsets=<optimized out>, index=<optimized out>, rec=<optimized out>, block=<optimized out>, type=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:1410 #5 row_ins_set_shared_rec_lock (type=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, thr=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:1395 #6 0x000063dbc64fa363 in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0x1, foreign=foreign@entry=0x7f730003e578, table=table@entry=0x7f7300144588, entry=<optimized out>, thr=thr@entry=0x7f7308049b10) at /home/marko/11.4/storage/innobase/row/row0ins.cc:1712 #7 0x000063dbc64fbab0 in row_ins_check_foreign_constraints (table=0x7f7300144588, index=index@entry=0x7f730004d818, pk=pk@entry=0x0, entry=entry@entry=0x7f730804b6f8, thr=thr@entry=0x7f7308049b10) at /home/marko/11.4/storage/innobase/row/row0ins.cc:1953 #8 0x000063dbc64fc011 in row_ins_sec_index_entry (index=0x7f730004d818, entry=entry@entry=0x7f730804b6f8, thr=thr@entry=0x7f7308049b10, check_foreign=check_foreign@entry=0x1) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3338 #9 0x000063dbc64fe29d in row_ins_index_entry (thr=0x7f7308049b10, entry=<optimized out>, index=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3429 #10 row_ins_index_entry_step (thr=<optimized out>, node=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3593 #11 row_ins (thr=0x7f7308049b10, node=<optimized out>) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3710 #12 row_ins_step (thr=thr@entry=0x7f7308049b10) at /home/marko/11.4/storage/innobase/row/row0ins.cc:3839 #13 0x000063dbc650c69e in row_insert_for_mysql (mysql_rec=mysql_rec@entry=0x7f7308047bf8 "\376\005a-100", prebuilt=0x7f7308049408, ins_mode=ROW_INS_NORMAL) at /home/marko/11.4/storage/innobase/row/row0mysql.cc:1295 #14 0x000063dbc6476817 in ha_innobase::write_row (this=0x7f73080473e0, record=0x7f7308047bf8 "\376\005a-100") at /home/marko/11.4/storage/innobase/handler/ha_innodb.cc:7832 This is part of call TestDeadLock('a-100') , and the record is (PRODUCT_LEDGER_NUM,DB_TRX_ID,DB_ROLL_PTR,PAY_TO_PRODUCT_NUM)=('2020',0x14,(insert),'pro') in the clustered index of the table product_ledger . Next, Thread 2 hits a conflict with a shared lock that was acquired by Thread 13 (transaction ID 0x1b) on the parent table product when it had executed the INSERT statement into the child table product_ledger : 11.4 ef966af801afc2a07222b5df65dddd52c77431dd Thread 2 hit Hardware watchpoint 6: -location cycle.lock.wait_lock.m._M_b Old value = {_M_p = 0x0} New value = {_M_p = 0x7f7330201800} lock_rec_create (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=<optimized out>, type_mode@entry=0x503, page_id=<optimized out>, page_id@entry={m_id = 0x800000003}, page=<optimized out>, heap_no=<optimized out>, index=<optimized out>, trx=<optimized out>, holds_trx_mutex=<optimized out>) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1577 (rr) bt #0 lock_rec_create (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=<optimized out>, type_mode@entry=0x503, page_id=<optimized out>, page_id@entry={m_id = 0x800000003}, page=<optimized out>, heap_no=<optimized out>, index=<optimized out>, trx=<optimized out>, holds_trx_mutex=<optimized out>) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1577 #1 0x000063dbc64a4f04 in lock_rec_enqueue_waiting (c_lock_info=@0x7f73306b0440: {conflicting = 0x7f7330202280, insert_after = 0x0}, type_mode=type_mode@entry=0x403, id=id@entry={m_id = 0x800000003}, page=<optimized out>, heap_no=heap_no@entry=0x2, index=index@entry=0x7f7300041dc8, thr=0x7f7300040408, prdt=0x0) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1639 #2 0x000063dbc64a54b2 in lock_rec_lock (impl=impl@entry=0x0, mode=0x403, block=0x7f732be01e00, heap_no=0x2, index=0x7f7300041dc8, thr=<optimized out>) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:1977 #3 0x000063dbc64a65bd in lock_clust_rec_read_check_and_lock (flags=<optimized out>, block=<optimized out>, rec=<optimized out>, index=<optimized out>, offsets=<optimized out>, mode=<optimized out>, gap_mode=0x400, thr=0x7f7300040408) at /home/marko/11.4/storage/innobase/lock/lock0lock.cc:6569 #4 0x000063dbc65220d0 in sel_set_rec_lock (mtr=0x7f73306b0c00, thr=0x7f7300040408, type=<optimized out>, mode=<optimized out>, offsets=<optimized out>, index=0x7f7300041dc8, rec=0x7f732bec4080 "2020", pcur=0x7f730003fbf0) at /home/marko/11.4/storage/innobase/row/row0sel.cc:1354 #5 row_search_mvcc (buf=buf@entry=0x7f7300147890 "\377", mode=PAGE_CUR_GE, prebuilt=0x7f730003fa28, match_mode=<optimized out>, direction=direction@entry=0x0) at /home/marko/11.4/storage/innobase/row/row0sel.cc:5244 #6 0x000063dbc6475f92 in ha_innobase::index_read (this=0x7f7300147078, buf=0x7f7300147890 "\377", key_ptr=0x7f7308055630 "\004", key_len=0x25, find_flag=<optimized out>) at /home/marko/11.4/storage/innobase/handler/ha_innodb.cc:9056 #7 0x000063dbc622a17a in handler::ha_index_read_map (this=this@entry=0x7f7300147078, buf=0x7f7300147890 "\377", key=0x7f7308055630 "\004", keypart_map=<optimized out>, find_flag=<optimized out>) at /home/marko/11.4/sql/handler.cc:3792 #8 0x000063dbc622f444 in handler::read_range_first (this=0x7f7300147078, start_key=0x7f73001471c8, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) at /home/marko/11.4/sql/handler.cc:6949 #9 0x000063dbc611c28d in handler::multi_range_read_next (this=0x7f7300147078, range_info=range_info@entry=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:590 #10 0x000063dbc611c304 in Mrr_simple_index_reader::get_next (this=0x7f7300147710, range_info=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:627 #11 0x000063dbc611d692 in DsMrr_impl::dsmrr_next (this=0x7f73001475c0, range_info=0x7f73306b1108) at /home/marko/11.4/sql/multi_range_read.cc:1731 #12 0x000063dbc5ee046a in QUICK_RANGE_SELECT::get_next (this=0x7f73080553f0) at /home/marko/11.4/sql/opt_range.cc:13265 #13 0x000063dbc5f0b2cd in rr_quick (info=0x7f73306b1300) at /home/marko/11.4/sql/records.cc:402 #14 0x000063dbc60a43c7 in READ_RECORD::read_record (this=0x7f73306b1300) at /home/marko/11.4/sql/records.h:81 #15 Sql_cmd_update::update_single_table (this=0x7f73080344d8, thd=0x7f7308004ce8) at /home/marko/11.4/sql/sql_update.cc:929 This lock creation is part of the UPDATE product statement. Both transactions have executed an INSERT INTO product_ledger statement and Thread 2 is hitting a conflict while executing UPDATE product . At the time this wait starts, Thread 13 is still executing SELECT SLEEP(5) . A little later, Thread 13 starts to execute UPDATE product and will be chosen a deadlock victim. To me, this looks like a classical deadlock anti-pattern that the RDBMS cannot do anything about, other than reporting the deadlock. Both transactions are first acquiring a shared lock on the only record of the parent table product as part of a FOREIGN KEY check during the INSERT into the child table, and they will get into a deadlock when trying to upgrade their shared lock on the parent table to an exclusive one for the UPDATE statement. The scenario of MDEV-34877 is simpler, because one of the participating transactions would start right away with an exclusive lock request, instead of starting with a shared lock and then trying to upgrade to exclusive. Unlike in MDEV-34877 , both transactions are in fact blocked by each other here, and a deadlock is inevitable. In MDEV-34877 , the reasonable course of action is to let the shared-lock holder proceed with its lock upgrade to exclusive, even though the other transaction, which is not holding any conflicting lock, started to wait for an exclusive lock earlier.

Elena Stepanova added a comment - 2025-02-18 11:34

The error is fairly easy to reproduce. Basically as described, only in MTR terms:

--source include/have_innodb.inc

CREATE TABLE `product` (

`PRODUCT_NUM` VARCHAR(35) NOT NULL COLLATE 'latin1_general_ci',

`PRODUCT_NAME` VARCHAR(128) NULL COLLATE 'latin1_general_ci',

`BALANCE_DUE` DECIMAL(20,2) NULL,

PRIMARY KEY (`PRODUCT_NUM`)

COLLATE='latin1_general_ci'

ENGINE=InnoDB;

INSERT INTO product VALUES (2020,'product',0);

CREATE TABLE `product_ledger` (

`PRODUCT_LEDGER_NUM` VARCHAR(35) NOT NULL COLLATE 'latin1_general_ci',

`PAY_TO_PRODUCT_NUM` VARCHAR(35) NULL COLLATE 'latin1_general_ci',

PRIMARY KEY (`PRODUCT_LEDGER_NUM`),

INDEX `fk_ple_pay_to_product` (`PAY_TO_PRODUCT_NUM`),

CONSTRAINT `fk_ple_pay_to_product` FOREIGN KEY (`PAY_TO_PRODUCT_NUM`) REFERENCES `product` (`PRODUCT_NUM`) ON UPDATE RESTRICT ON DELETE RESTRICT

COLLATE='latin1_general_ci'

ENGINE=InnoDB;

--delimiter //

CREATE PROCEDURE `TestDeadlock`(

IN `vEntryNum` VARCHAR(30)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE i INT DEFAULT 0;

DECLARE exit handler FOR sqlexception

BEGIN

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

SELECT @text, @errno;

ROLLBACK;

END;

START TRANSACTION;

INSERT INTO product_ledger(PRODUCT_LEDGER_NUM, PAY_TO_PRODUCT_NUM) VALUES (vEntryNum, '2020');

SELECT SLEEP(5) INTO @a;

UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020';

COMMIT;

END //

--delimiter ;

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

--connect (con2,localhost,root,,)

--let $run=100

while ($run)

  --connection con1

  --send_eval call TestDeadLock('a-$run')

  --connection con2

  eval call TestDeadLock('b-$run');

  --connection con1

  --reap

  --dec $run

11.4 ef966af801afc2a07222b5df65dddd52c77431dd
connection con1;
call TestDeadLock('a-100');
connection con2;
call TestDeadLock('b-100');
connection con1;
@text @errno
Deadlock found when trying to get lock; try restarting transaction 1213
connection con1;
call TestDeadLock('a-99');
connection con2;
call TestDeadLock('b-99');
@text @errno
Deadlock found when trying to get lock; try restarting transaction 1213
...

And if executed with innodb_print_all_deadlocks=ON,

2025-02-18 13:31:35 6 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2025-02-18 13:31:35 6 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 86, ACTIVE 5 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1

MariaDB thread id 6, OS thread handle 140318326757056, query id 259 localhost root Updating

UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020'

2025-02-18 13:31:35 6 [Note] InnoDB: *** WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

2025-02-18 13:31:35 6 [Note] InnoDB: *** CONFLICTING WITH:

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock mode S locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock mode S locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

2025-02-18 13:31:35 6 [Note] InnoDB:

*** (2) TRANSACTION:

TRANSACTION 87, ACTIVE 5 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1

MariaDB thread id 5, OS thread handle 140318327064256, query id 258 localhost root Updating

UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020'

2025-02-18 13:31:35 6 [Note] InnoDB: *** WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

2025-02-18 13:31:35 6 [Note] InnoDB: *** CONFLICTING WITH:

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock mode S locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock mode S locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 32303230; asc 2020;;

 1: len 6; hex 000000000000; asc       ;;

 2: len 7; hex 80000000000000; asc        ;;

 3: len 7; hex 70726f64756374; asc product;;

 4: len 9; hex 80000000000004b000; asc          ;;

2025-02-18 13:31:35 6 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

Whether it's a problem or not, I'll leave to marko to decide.

Elena Stepanova added a comment - 2025-02-18 11:34 The error is fairly easy to reproduce. Basically as described, only in MTR terms: --source include/have_innodb.inc CREATE TABLE `product` ( `PRODUCT_NUM` VARCHAR (35) NOT NULL COLLATE 'latin1_general_ci' , `PRODUCT_NAME` VARCHAR (128) NULL COLLATE 'latin1_general_ci' , `BALANCE_DUE` DECIMAL (20,2) NULL , PRIMARY KEY (`PRODUCT_NUM`) ) COLLATE = 'latin1_general_ci' ENGINE=InnoDB; INSERT INTO product VALUES (2020, 'product' ,0); CREATE TABLE `product_ledger` ( `PRODUCT_LEDGER_NUM` VARCHAR (35) NOT NULL COLLATE 'latin1_general_ci' , `PAY_TO_PRODUCT_NUM` VARCHAR (35) NULL COLLATE 'latin1_general_ci' , PRIMARY KEY (`PRODUCT_LEDGER_NUM`), INDEX `fk_ple_pay_to_product` (`PAY_TO_PRODUCT_NUM`), CONSTRAINT `fk_ple_pay_to_product` FOREIGN KEY (`PAY_TO_PRODUCT_NUM`) REFERENCES `product` (`PRODUCT_NUM`) ON UPDATE RESTRICT ON DELETE RESTRICT ) COLLATE = 'latin1_general_ci' ENGINE=InnoDB; --delimiter // CREATE PROCEDURE `TestDeadlock`( IN `vEntryNum` VARCHAR (30) ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE done INT DEFAULT FALSE ; DECLARE i INT DEFAULT 0; DECLARE exit handler FOR sqlexception BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; SELECT @text, @errno; ROLLBACK ; END ; START TRANSACTION ; INSERT INTO product_ledger(PRODUCT_LEDGER_NUM, PAY_TO_PRODUCT_NUM) VALUES (vEntryNum, '2020' ); SELECT SLEEP(5) INTO @a; UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020' ; COMMIT ; END // --delimiter ; --connect (con1,localhost,root,,) --connect (con2,localhost,root,,) --let $run=100 while ($run) { --connection con1 --send_eval call TestDeadLock('a-$run') --connection con2 eval call TestDeadLock( 'b-$run' ); --connection con1 --reap --dec $run } 11.4 ef966af801afc2a07222b5df65dddd52c77431dd connection con1; call TestDeadLock( 'a-100' ); connection con2; call TestDeadLock( 'b-100' ); connection con1; @text @errno Deadlock found when trying to get lock; try restarting transaction 1213 connection con1; call TestDeadLock( 'a-99' ); connection con2; call TestDeadLock( 'b-99' ); @text @errno Deadlock found when trying to get lock; try restarting transaction 1213 ... And if executed with innodb_print_all_deadlocks=ON , 2025-02-18 13:31:35 6 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2025-02-18 13:31:35 6 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 86, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MariaDB thread id 6, OS thread handle 140318326757056, query id 259 localhost root Updating UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020' 2025-02-18 13:31:35 6 [Note] InnoDB: *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; 2025-02-18 13:31:35 6 [Note] InnoDB: *** CONFLICTING WITH: RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; 2025-02-18 13:31:35 6 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 87, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MariaDB thread id 5, OS thread handle 140318327064256, query id 258 localhost root Updating UPDATE product SET BALANCE_DUE = BALANCE_DUE + 100 WHERE PRODUCT_NUM = '2020' 2025-02-18 13:31:35 6 [Note] InnoDB: *** WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; 2025-02-18 13:31:35 6 [Note] InnoDB: *** CONFLICTING WITH: RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 86 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; RECORD LOCKS space id 8 page no 3 n bits 320 index PRIMARY of table `test`.`product` trx id 87 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 32303230; asc 2020;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 7; hex 70726f64756374; asc product;; 4: len 9; hex 80000000000004b000; asc ;; 2025-02-18 13:31:35 6 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) Whether it's a problem or not, I'll leave to marko to decide.

Jaswanth added a comment - 2025-02-18 09:00 - edited

Hello Marko
Thanks for the reply. I tested it with MariaDB Server 11.4.5. We are getting the same deadlock issue.

Jaswanth added a comment - 2025-02-18 09:00 - edited Hello Marko Thanks for the reply. I tested it with MariaDB Server 11.4.5. We are getting the same deadlock issue.

Marko Mäkelä added a comment - 2025-02-18 07:12

In FOREIGN KEY checks, shared locks are being acquired. InnoDB indeed used to unnecessarily report deadlocks instead of entering a lock wait when a shared lock needs to be upgraded to exclusive. This should have been improved in ~~MDEV-34877~~. Can you please test if this kind of deadlocks are reproducible with the recently released MariaDB Server 11.4.5?

Marko Mäkelä added a comment - 2025-02-18 07:12 In FOREIGN KEY checks, shared locks are being acquired. InnoDB indeed used to unnecessarily report deadlocks instead of entering a lock wait when a shared lock needs to be upgraded to exclusive. This should have been improved in MDEV-34877 . Can you please test if this kind of deadlocks are reproducible with the recently released MariaDB Server 11.4.5?

People

Assignee:: Marko Mäkelä

Reporter:: Jaswanth

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2025-02-18 07:05

Updated:: 2025-02-21 08:39

Resolved:: 2025-02-20 16:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.