Bug #102967 deadlock transaction hang up when connection is broken

• Submitted: 15 Mar 2021 2:39
• Modified: 16 Mar 2021 11:45
• Reporter: yuxiang jiang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.18
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[15 Mar 2021 2:39] yuxiang jiang

Description:
When turn off dead lock detection, transactions hold locks and come along
with dead lock. If transactoins client disconnect accidently, transactions
will not rollback and hold locks until lock timed out. This will cause the
followup blocking on the holding locks. Also if transaction is blocked by
other transaction and disconnected accidently, this transaction will not
release locks until timed out No matter what dead lock check is configed.
Deadlcok hangup can be solved by turning dead lock check on.

How to repeat:
set global innodb_deadlock_detect=off;
create table t1(f1 int);
create table t2(f1 int);

conn 1 conn 2
begin; begin;
select * from t1 for update; select * from t2 for update;
insert into t2 values (1); insert into t1 values (1);

kill conn1, conn2 client

conn 3
show processlist;

Suggested fix:
In timed out monitor thread, we add check on thread's connection status of
socket. If connection is broken, monitor will mark the transaction unusable
and signal lock suspend thread connection as lock timed-out.

[15 Mar 2021 2:41] yuxiang jiang

bug fix for 5.7

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: deadlock_hang_when_killed_client.patch (application/octet-stream, text), 7.19 KiB.

[16 Mar 2021 11:45] MySQL Verification Team

Hello yuxiang jiang,

Thank you for the report and contribution.

regards,
Umesh