MySQL Bugs: #89643: MAX_EXECUTION_TIME hint doesn't work if waiting on ndbcluster global schema lock

Bug #89643	MAX_EXECUTION_TIME hint doesn't work if waiting on ndbcluster global schema lock
Submitted:	13 Feb 2018 7:53	Modified:	12 Mar 2019 0:58
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	7.6.4	OS:	Any
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
I would expect that the MAX_EXECUTION_TIME hint would work for all queries. However it doesn't seem to apply when the query is waiting on the global schema lock.

I would like to be able to run some I_S queries and have them fail if there is a global schema lock instead of wait until the lock is gone.

How to repeat:
While running a ALTER in the cluster:

root@host1 [(none)]> SELECT /*+ MAX_EXECUTION_TIME(100) */ SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
|         1 |
+-----------+
1 row in set (0.10 sec)

root@host1 [(none)]> SELECT /*+ MAX_EXECUTION_TIME(100) */  table_name FROM information_schema.tables LIMIT 1;
(hangs)

From information_schema.processlist:
     ID: 42240
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 50
  STATE: Waiting for ndbcluster global schema lock
   INFO: SELECT /*+ MAX_EXECUTION_TIME(100) */  table_name FROM information_schema.tables LIMIT 1

Hello Daniël,

Thank you for the report!

Thanks,
Umesh

Posted by developer:
 
The limit specified by the MAX_EXECUTION_TIME hint or the "max_execution_time" session variable can not be enforced while the MySQL Server has sent a request to NDB and is waiting for the reply to be returned. This affects all queries and can be noticed when it takes time for NDB to reply. One such case is when waiting for a row lock, the reply from NDB will not be returned until the lock is granted or timeout occurs.

The global schema lock (which serializes DDL in the cluster) is implemented as an exclusive row lock on an internal table in NDB. In the problem described by this bug the ALTER TABLE will hold the row lock in order to prevent concurrent DDL.

Unfortunately the "SHOW TABLES" and "SELECT * FROM INFORMATION_SCHEMA.TABLES" queries also grabs the global schema lock, even though such commands just produce a list of tables they will also try to synchronize the MySQL Server's view of which tables exists with NDB's view. Those queries will thus hang until the long running ALTER TABLE has completed.

For MySQL Cluster 7.6 it could be possible to workaround the problem by changing the TransactionDeadlockDetectionTimeout to a lower value, thus causing a timeout when trying to grab the global schema lock. This will return control to the MySQL Server and allow it to check if the query has exceeded the time limit. If there is still time left, new attempt(s) to grab the global schema lock will be done, however it's now of course placed at the end of the queue waiting for the row lock. Beware not to set the timeout too low as it's a global configuration variable of NDB and thus affects all transactions.

With introduction of new Data dictionary(DD) in MySQL Cluster 8.0 the "SHOW TABLES" and "SELECT * FROM INFORMATION_SCHEMA.TABLES" has been rewritten to not grab the global schema lock anymore. Instead they simply list what's in the Data dictionary without trying to synchronize anything.

This means that after we would like to close this bug as fixed in 8.0 after having confirmed that either:
1) The 7.6 workaround to set a TransactionDeadlockTimeout to a lower value allows the max_execution_time to take effect.
or
2) The new 8.0 functionality for showing tables does not hang waiting for global schema lock.

Posted by developer:
 
Correction.

The configuration variable to potentially tune for 7.6 is named TransactionDeadlockDetectionTimeout
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-t...

Thank you for the extensive description of the problem!

We have TransactionDeadlockDetectionTimeout=10000 in our config. That gives a timeout of 10s instead of the default 1.2s.

But queries stay in "Waiting for ndbcluster global schema lock" for way longer than that timeout. So I'm afraid this doesn't work as workaround.

Posted by developer:
 
Thanks for additional info. Will check why the attempt to grab global schema lock does not timeout after 10s either.

Posted by developer:
 
Problem has been found. The I_S.TABLES query enters an infinite retry loop which attempts to acquire the global schema lock although query has been killed.

Documented fix as follows in the NDB 7.6.10 and 8.0.17 changelogs:

    Neither the MAX_EXECUTION_TIME optimizer hint nor the
    --max-execution-time system variable was respected for DDL
    statements or queries against INFORMATION_SCHEMA tables while an
    NDB global schema lock was in effect.

Closed.