MySQL Bugs: #15005: MySQL Cluster respond "Lock wait timeout exceeded" after issuing SELECT stateme

Bug #15005	MySQL Cluster respond "Lock wait timeout exceeded" after issuing SELECT stateme
Submitted:	17 Nov 2005 6:28	Modified:	30 Nov 2005 7:55
Reporter:	Kenji Hirohama	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Miracle Linux V3.0
Assigned to:		CPU Architecture:	Any

Description:
MySQL Cluster respond "Lock wait timeout exceeded" after issuing SELECT statement through ODBC driver like;

[1] sqlstate HYT00 : [MySQL][ODBC 3.51 Driver][mysqld-5.0.15-max]Lock wait timeout exceeded; try restarting transaction

The statement is;
SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_title LIKE '%BABABABAOGALAT%' AND i_a_id = a_id ORDER BY i_title ASC;

I can understand If I get "lock wait timeout" after issuing "update" statement.

How to repeat:
I am using "OSDL DBT-1 ODBC version"
http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/

Hi,

If there is _high_ load in system, select can be aborted if they dont complete fast enough.
I think that this will be transformed into "lock wait timeout" by mysqld.

> If there is _high_ load in system, select can be aborted if they dont complete
> fast enough.
> I think that this will be transformed into "lock wait timeout" by mysqld.

Hi,
Sorry, but I don't quite follow you.
Would you please explain it with another words or re-state it?

Did you say select can be aborted during CPU is occupied by other processes?

And, I think we can not set "lock wait timeout" for MySQL Cluster at mysqld side, for instance, by my.cnf file.

Please, send the results of the SHOW CREATE TABLE for item and author tables.

The following is the create statement I issue when creating tables.

If you need the exact output of "SHOW CREATE TABLE" command, I will post next Tuesday.

---
CREATE TABLE `author` (
  `a_id` decimal(10,0) NOT NULL default '0',
  `a_fname` varchar(20) default NULL,
  `a_lname` varchar(20) default NULL,
  `a_mname` varchar(20) default NULL,
  `a_dob` date default NULL,
  `a_bio` text,
  PRIMARY KEY  (`a_id`),
  index i_a_lname (a_lname asc)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

CREATE TABLE `item` (
  `i_id` decimal(10,0) NOT NULL default '0',
  `i_title` varchar(60) default NULL,
  `i_a_id` decimal(10,0) default NULL,
  `i_pub_date` date default NULL,
  `i_publisher` varchar(60) default NULL,
  `i_subject` varchar(60) default NULL,
  `i_desc` text,
  `i_related1` decimal(10,0) default NULL,
  `i_related2` decimal(10,0) default NULL,
  `i_related3` decimal(10,0) default NULL,
  `i_related4` decimal(10,0) default NULL,
  `i_related5` decimal(10,0) default NULL,
  `i_thumbnail` decimal(8,0) default NULL,
  `i_image` decimal(8,0) default NULL,
  `i_srp` decimal(17,2) default NULL,
  `i_cost` decimal(17,2) default NULL,
  `i_avail` date default NULL,
  `i_stock` decimal(4,0) default NULL,
  `i_isbn` varchar(13) default NULL,
  `i_page` decimal(4,0) default NULL,
  `i_backing` varchar(15) default NULL,
  `i_dimensions` varchar(25) default NULL,
  PRIMARY KEY  (`i_id`),
  index i_i_subject (i_subject asc),
  index i_i_a_id (i_a_id asc),
  index i_i_title (i_title asc)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

Thank you for the additional information. And what are the results of 

EXPLAIN SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_title LIKE '%BABABABAOGALAT%' AND i_a_id = a_id ORDER BY i_title ASC;

Are there any other concurrent sessions that work with these tables while you are getting the message about the lock wait timeout?

Hi, the output is the following;

mysql> explain select i_id, i_title, a_fname, a_lname from item, author where i_title like '%BABABABAOGNGBA%' and i_a_id=a_id order by i_title asc;
+----+-------------+--------+------+---------------+----------+---------+------------------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref              | rows | Extra                           |
+----+-------------+--------+------+---------------+----------+---------+------------------+------+---------------------------------+
|  1 | SIMPLE      | author | ALL  | PRIMARY       | NULL     | NULL    | NULL             |  250 | Using temporary; Using filesort |
|  1 | SIMPLE      | item   | ref  | i_i_a_id      | i_i_a_id | 6       | DBT1.author.a_id |    1 | Using where                     |
+----+-------------+--------+------+---------------+----------+---------+------------------+------+---------------------------------+
2 rows in set (0.00 sec)

----

Yes, some queries are issuing as a part of transactions by other virtual clients.

Do you have any ideas when I get "Lock wait timeout exceeded" in general?

I specified a problem here;
http://bugs.mysql.com/bug.php?id=15282

So, would you close this report?

If you agree, that the real cause of the problem was the read-write lock (because of your TEXT column stored separately), as it is supposed in http://bugs.mysql.com/bug.php?id=15282, I can close this bug report as duplicate of bug# 15282. 

Do you have any lock wait timeouts when selecting form tables without blob fields?

Yes, please close this #15005 bug report.

By the way,in my setting, TransactionDeadlockDetectionTimeout=20000
The slow-query log said most transaction which cause the errors took around 20 sec.

Thanks,
Kenji

The problem is described in more details as bug #15282.