Resolving Schema Lock Deadlocks

8.10 Resolving Schema Lock Deadlocks

In rare cases, after a schema change is received through georeplication, two sessions in an ndbappmysqld pod can become stuck waiting for locks held by each other. Application SELECT and UPDATE queries can remain blocked, and further schema changes might not complete at the affected site.

This procedure identifies the affected SQL pod and restarts it to clear the condition.

Perform this procedure on all sites, one site at a time. If a schema lock is detected, continue with the remaining steps to confirm and resolve the condition. If no lock is detected, proceed to the next site.

Identify the SQL pod holding the schema lock.

Connect to an ndbmysqld or ndbappmysqld pod at the site and run:

SELECT c.client_node_id,
       n.node_hostname AS node_fqdn,
       a.state,
       a.duration_millis
FROM ndbinfo.cluster_locks AS a
JOIN ndbinfo.cluster_locks AS b
  ON a.tableid = b.tableid
 AND a.fragmentid = b.fragmentid
 AND a.rowid = b.rowid
JOIN ndbinfo.cluster_transactions AS c
  ON a.transid = c.transid
JOIN ndbinfo.dict_obj_info AS d
  ON b.tableid = d.id
LEFT JOIN ndbinfo.config_nodes AS n
  ON n.node_id = c.client_node_id
WHERE d.fq_name = 'sys/def/systab_0'
  AND b.state = 'H'
  AND b.duration_millis > 10000;

Example output:

client_node_id  node_fqdn                                                state  duration_millis
71              ndbappmysqld-1.ndbappmysqldsvc.site2.svc.cluster.local     H      66302828
71              ndbappmysqld-1.ndbappmysqldsvc.site2.svc.cluster.local     W      237

In the output:

H identifies a session holding the lock.
W identifies a session waiting for the lock.
duration_millis indicates how long the lock has been held or awaited, in milliseconds.

Use node_fqdn from the H row to identify the SQL pod. In this example, the pod is ndbappmysqld-1 in namespace site2.

If the query returns Empty set, proceed to the next site. Otherwise, continue to step 2.

Confirm that the schema-lock holder is stuck.

Connect to the SQL pod identified in step 1 and run the following query. Repeat the query after approximately 30 seconds to determine whether the same session remains blocked.

SELECT DISTINCT
       UTC_TIMESTAMP(6) AS sampled_at_utc,
       @@hostname AS sql_pod,
       h.mysql_connection_id AS holder_connection,
       h.transid AS holder_transaction,
       h.duration_millis AS held_millis,
       t.PROCESSLIST_STATE AS session_state,
       t.PROCESSLIST_TIME AS seconds_in_state,
       m.OBJECT_SCHEMA,
       m.OBJECT_NAME,
       m.LOCK_TYPE,
       m.LOCK_STATUS
FROM ndbinfo.server_locks AS h
JOIN ndbinfo.dict_obj_info AS d
  ON d.id = h.tableid
JOIN performance_schema.threads AS t
  ON t.PROCESSLIST_ID = h.mysql_connection_id
JOIN performance_schema.metadata_locks AS m
  ON m.OWNER_THREAD_ID = t.THREAD_ID
WHERE d.fq_name = 'sys/def/systab_0'
  AND h.state = 'H'
  AND h.mode = 'X'
  AND h.duration_millis > 10000
  AND m.OBJECT_TYPE = 'TABLE'
  AND m.LOCK_TYPE = 'EXCLUSIVE'
  AND m.LOCK_STATUS = 'PENDING';

Example output, shown vertically for readability:

sampled_at_utc     : 2026-10-30 06:17:26.509626 UTC
sql_pod            : ndbappmysqld-1
holder_connection  : 17285
holder_transaction : 94653657500364494
held_millis        : 66427468
session_state      : Waiting for table metadata lock
seconds_in_state   : 66427
OBJECT_SCHEMA      : cndb_gsl_repro_h1_20261029
OBJECT_NAME        : probe_01
LOCK_TYPE          : EXCLUSIVE
LOCK_STATUS        : PENDING

This output indicates that the session holding the NDB lock is itself waiting for a table metadata lock. PENDING means that the requested metadata lock has not been granted.

Compare the two query results. If the same holder_connection and holder_transaction remain PENDING, and the affected queries or schema changes remain blocked, continue to step 3.

If the query returns Empty set, this check does not confirm the condition. Repeat step 1 before considering a pod restart.

Restart the identified SQL pod.
Replace <namespace> and <sql-pod-name> with the namespace and pod identified in the preceding steps.
```
kubectl -n <namespace> delete pod <sql-pod-name>
```
After the pod is recreated and becomes Ready, repeat step 1 to verify that the schema lock is no longer present.