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) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[15 Mar 2021 2:39] yuxiang jiang
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.