Re: Monitor Deadlocks with ndb_cluster engine

[Matthew Montgomery]

Hello Georg,

First, the NDBCLUSTER and InnoDB engines have nothing whatsoever to do with each other. So, the use of any of these queries make no sense when dealing with NDB.

SHOW ENGINE INNODB STATUS\G
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
CREATE TABLE innodb_monitor (a INT) ENGINE=NDB;
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=NDB;

Due to the distributed nature of NDBCLUSTER there is no automatic deadlock detection mechanism within the NDBCLUSTER engine. The only indication that the cluster gives that there is a *possible* deadlock is the "Lock wait timeout exceeded". This error occurs when a given lock operation takes longer than TransactionDeadlockDetectionTimeout (default 1200 ms.). If you have large transactions that lock many rows at once or if you have long running transactions these can prevent this or transactions from completing quickly. Try to avoid large or long running transactions by breaking them into smaller chunks. The TransactionDeadlockDetectionTimeout can also be reached when a node is overloaded but has not yet been ejected from the cluster for missing heartbeats longer than 4xHeartbeatIntervalDbDb (default 1500 ms. each).

The procedure for handling them is the same, retry the transaction, reduce the size or duration of the transaction and lock rows from two transactions that access the same tables in the same order. If none of these options are available raise the TransactionDeadlockDetectionTimeout and/or reduce the HeartbeatIntervalDbDb.

The equivalent variable within InnoDB is innodb_lock_wait_timeout (default 50s) You could increase the TransactionDeadlockDetectionTimeout=50000 to make it behave similarly to InnoDB. The exception to that being that when there are real deadlocks, it will take up to 50s for them to timeout rather than erroring immediately as within InnoDB.



Edited 2 time(s). Last edit at 12/14/2011 09:26PM by Matthew Montgomery.