MySQL Bugs: #113156: Is this dead lock can be treated as a bug in design?

Bug #113156	Is this dead lock can be treated as a bug in design?
Submitted:	21 Nov 2023 2:42	Modified:	21 Nov 2023 16:20
Reporter:	ding qi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
the mysql-test-run script is pasted in the "repeat" blank.

when the dead lock occurs, run "show processlist" in a third thread, it shows

| 10 | root            | localhost       | db1  | Query   |    8 | Waiting for global read lock | insert into tb1 values(3)            |
| 11 | root            | localhost       | db1  | Query   |    8 | executing                    | select * from tb1 lock in share mode |

means these two thread are waiting each other.

How to repeat:
create database db1;
use db1;
create table tb1 (id int primary key)engine=innodb;                   
insert into tb1 values(1);

connect(con1, localhost, root,,db1);                                  
connect(con2, localhost, root,,db1);                                  

connection con1;
begin; 
insert into tb1 values(2); 

connection con2;
flush tables with read lock; 
send select * from tb1 lock in share mode; 

connection con1;
insert into tb1 values(3);

disconnect con1; 
disconnect con2;
drop database db1;

Hi Mr. Bryce,

Thank you for your bug report.

We would like to inform you that deadlocks are not a bug by design. They are a feature by design. The fact that InnoDB manages to diagnose deadlocks and rollback one of the transactions is a proof that InnoDB works according to the specifications and according to the SQL standard.

In all transaction engines, deadlocks are to be expected. We are happy that we have a product that is so reliable in catching deadlocks. 

You should check in your code the transaction that is rolled back and try to submit it from the beginning to the end. That is what your application should be doing.

Not a bug.

thanks for replying.

But in this case, MySQL can not catch the deadlock, it just wait until one of the thread rollback by the logic of "timeout".

IF the deadlock is detected, the action of rollback should be done very quickly. But as shown in the result of "show processlist", both of the threads wait more than 8 seconds.

Hi,

That is true.

What you have is a lock wait timeout. Your bug title indicated that it is a deadlock, but it is not.

Lock wait timeout usually happen due to the bad application design. When you start transaction, you should just issue one SQL statement after another and then commit. If you have any wait during transaction, you will hit upon this problem. 

All transaction engines have a timeout determined in advance.

Hence, after starting a transaction, it should not wait on any other server, it should not wait on any network or Internet communication, it should not wait on end-user's input. It should not wait on anything.

Otherwise, you will get lock waits that might timeout.