MySQL Bugs: #113277: Blocking prevents a transaction from acquiring a lock properly

Bug #113277	Blocking prevents a transaction from acquiring a lock properly
Submitted:	29 Nov 2023 6:09	Modified:	5 Dec 2023 13:37
Reporter:	Cundi Fang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	8.0.35-cluster MySQL Cluster Community S	OS:	Ubuntu (20.04)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	lock, Stuck, timeout

Description:
I have a MySQL cluster consisting of 1 control node and 4 SQL nodes, the 4 SQL nodes are also data nodes, see the REPEAT section for the exact configuration file.

I have created a table by the following statement:

CREATE TABLE IF NOT EXISTS test163 (column1 FLOAT, column2 BOOLEAN, column3 FLOAT, column4 VARCHAR(255), column5 BOOLEAN, column6 BOOLEAN, column7 DATETIME) ENGINE=NDB;

First I did a lot of INSERT and DELETE statements to bring the table to 1000+ rows.

Step 1: I executed the statement at node 1:
DELETE FROM test163 WHERE column5 = 1;

Step 2: While the delete statement at node1 was not yet executed, I executed the statement at node3:
DELETE FROM test163 WHERE column4 = 'jGXEuFAJQ';

Not surprisingly, he is waiting for the statement at node1 to finish executing and release the lock.

Step 3: While the delete statement at node1 has not yet finished executing, I execute the statement at node4: 
DELETE FROM test163 WHERE column2 = 1;

Not surprisingly, he is also waiting for the node1 statement to finish and release the lock.

Step 4: As soon as the delete statement on node1 finished executing, I immediately executed the statement on node1 again: 
DROP TABLE IF EXISTS test163;

At this time, the equivalent of the node3's "delete" statement, the node4's "delete" statement and the node1's "DROP TABLE" statement, created a conflict.

According to my expectation, node3's "delete" statement and node4's "delete" statement constitute a row-level lock competition, following the principle of "first-come-first-served", node3's "delete" statement should be executed, so that node4's "delete" statement to wait.

But at this time, node1's "drop table" statement requests the same table's table-level lock, and eventually constitutes a conflict between row-level lock with table-level lock!

In this case, either node3's "delete" statement is executed first, and node1's "drop table" statement should wait, or node1's "drop table" statement is executed first, and an error is reported to node3 "failed: Table definition has changed".

But strangely enough, in the actual scenario, my result is like this:

Both node1 and node3 were blocked for 1.6 seconds, and eventually, node3 triggered a timeout and reported "mysql_store_result() failed: Lock wait timeout exceeded; try restarting transaction", then the node1's "drop table" statement continued to execute and got normal results. Eventually, node4 reported "mysql_store_result() failed: Table definition has changed, please retry transaction".

How to repeat:
Before repeat, please set the timeout for row-level locks longer, so that the result will be more obvious.

Step 1: I executed the statement at node 1:
DELETE FROM test163 WHERE column5 = 1;

Step 2: While the delete statement at node1 was not yet executed, I executed the statement at node3:
DELETE FROM test163 WHERE column4 = 'jGXEuFAJQ';

Step 3: While the delete statement at node1 has not yet finished executing, I execute the statement at node4: 
DELETE FROM test163 WHERE column2 = 1;

Step 4: As soon as the delete statement on node1 finished executing, I immediately executed the statement on node1 again: 
DROP TABLE IF EXISTS test163;

The configuration of my network is as follows:

[NDBD DEFAULT]
NoOfReplicas =2
DataMemory = 512M
IndexMemory = 64M

[NDB_MGMD]
NodeId=1
hostname =192.172.10.8
datadir =/var/lib/mysql-cluster

[NDBD]
NodeId =2
hostname =192.172.10.9
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =3
hostname =192.172.10.10
datadir =/usr/local/mysql-cluster/data
NodeGroup=0
[NDBD]
NodeId =4
hostname =192.172.10.11
datadir =/usr/local/mysql-cluster/data
NodeGroup=1
[NDBD]
NodeId =5
hostname =192.172.10.12
datadir =/usr/local/mysql-cluster/data
NodeGroup=1

[mysqld]
NodeId =6
hostname =192.172.10.9
[mysqld]
NodeId =7
hostname =192.172.10.10
[mysqld]
NodeId =8
hostname =192.172.10.11
[mysqld]
NodeId =9
hostname =192.172.10.12

Suggested fix:
It is recommended to add some error handling operations or an error judgment when there is a conflict between different levels of locks. It is also possible to add a lock priority to distinguish between different levels of locks.

Hi,

Thank you for using NDB CLUSTER and for reporting this behavior but this is not a bug. The "first-come-first-served" makes no sense as you cannot know what came first, the point that you "entered the query" before other query does not mean it came first. There is nothing that will guarantee order of operations in this case, and, especially in this case, order of operations is irrelevant as it ends up with the same result whatever the order is.

Thank you for your reply! However, there might have been some misunderstanding due to the way I described the issue.

What I want to emphasize is not that the transaction handling of MySQL Cluster violates the 'first-come, first-served' principle. Rather, I want to highlight that the competition between two different levels of locks (row-level locks and table-level locks) leads to 'deadlocks', or at least 'long waits', which is something I do not wish to see.

I have also been trying to reproduce this error in the past few days, and I found that the error is more likely to be triggered when there is a larger amount of data.

> competition between two different levels of locks (row-level locks and table-level locks) leads to 'deadlocks', or at least 'long waits', which is something I do not wish to see.

That is not really a bug, and there's not really much we can do about it. I could say that "drop table" in a busy application is not considered a good practice but there are always ways to trigger a deadlock, that is why detection exist and it is never ideal.