Deadlock found when trying to get lock; Try restarting transaction, message from server: "Lock wait timeout exceeded; try restarting transaction" : for SELECT statement
table :
--------------------------------------------
CREATE TABLE `testUsers` (
`id` bigint(20) NOT NULL auto_increment,
`firstName` varchar(200) NOT NULL,
`lastName` varchar(200) default NULL,
`userIdentity` varchar(200) NOT NULL,
`password` varchar(200) NOT NULL,
`email` varchar(100) NOT NULL,
`mobilePhone` varchar(100) default NULL,
`officePhone` varchar(100) default NULL,
`gender` varchar(10) NOT NULL,
`countryId` bigint(20) default NULL,
`city` varchar(200) default NULL,
PRIMARY KEY (`id`)
) ENGINE=NDBCLUSTER;
SAMPLE-SQL:
--------------------------------------------
SELECT * FROM testUsers order by id asc limit 0,20000;
result:
--------------------------------------------
[Thread-Count:166 Data-size-per-query:20000 Total-Data:25,00,000]
first attempt : Successful --> 122 Deadlock --> 44
second attempt: Successful --> 122 Deadlock --> 44
mysql> SELECT VERSION()\G
*************************** 1. row ***************************
VERSION(): 5.6.27-ndb-7.4.8-cluster-gpl
ndb_mgm> show
Connected to Management Server at: 192.168.1.102:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.102 (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0, *)
id=3 (not connected, accepting connect from 192.168.1.47)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.102 (mysql-5.6.27 ndb-7.4.8)
[mysqld(API)] 3 node(s)
id=4 @192.168.1.102 (mysql-5.6.27 ndb-7.4.8)
id=5 @192.168.1.101 (mysql-5.6.27 ndb-7.4.8)
#simple select statement causing deadlock in NDBCluster 7.4.
Any idea ?
Thanks in advance.