MySQL Cluster Error: Lock wait timeout exceeded

Question

I was trying to load a ~200G file into MySQL cluster having 4 data nodes, and my DDL for the target table is like this:

CREATE TABLE  XXXXXX
(
     ID BIGINT AUTO_INCREMENT PRIMARY KEY,
     COL1...,
     COL2...,
     .......
)
ENGINE = NDB PARTITION BY KEY();

after few minutes of loading, I received following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

and I found some records loaded into the target table. what should I do to overcome this issue?

some of the variables values which might be helpful:

wait_timeout      : 28800
ndb_wait_connected: 30
ndb_wait_setup    : 30

Answer 1

I have seen issue like that with tables with BLOB/TEXT entries + long running select. NDB uses a hidden table to store chunks for larger objects, and as a side effect unique key SELECT's create a shared read lock for the tables. See http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitations-transactions.html

So on a two node cluster

node2> show create table test;

| test  | CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `i` int(11) DEFAULT NULL,
  `v1` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i` (`i`)
) ENGINE=ndbcluster AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |

node2> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

node2> set @@tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

node2> start transaction;
node2> select v1 from test where i=2;  #shared lock is created

On the second node, updates for the table may fail.

node1>  set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

node1> set @@tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

node1> update test set v1='a' where i=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
node1> load data infile '/tmp/data' replace into table test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Depending on the version, you might see a different error, like

mysql> load data infile '/tmp/data' replace into table test;
ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from NDBCLUSTER