MySQL Bugs: #15282: MySQL Cluster can not behave in the right manner of READ-COMMITTED isolation le

Bug #15282	MySQL Cluster can not behave in the right manner of READ-COMMITTED isolation le
Submitted:	28 Nov 2005 7:51	Modified:	14 Dec 2005 17:28
Reporter:	Kenji Hirohama	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0.15	OS:	Asianux
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
MySQL Cluster can not behave in the right manner of READ-COMMITTED isolation level with some table schema

With simple table schema, MySQL Cluster works well in the right way under READ-COMMITTED isolation level.
However, with some table schema, MySQL Cluster's hehavior is a bit strange, and respond "Lock wait timeout" error.

How to repeat:
1. In my.cnf file, add;
transaction-isolation=read-committed

2. Create table with the following schema;
CREATE TABLE `ken6` (
  `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`),
  KEY `i_i_subject` (`i_subject`),
  KEY `i_i_a_id` (`i_a_id`),
  KEY `i_i_title` (`i_title`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

3. Insert some data;
mysql> insert into ken6(i_id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ken6(i_id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ken6(i_id) values (3);
Query OK, 1 row affected (0.00 sec)

4. From mysqlclient-1
mysql> set autocommit=0;
mysql> update ken6 set i_title=222 where i_id=1;

5. From mysqlclient-2
mysql> set autocommit=0;
mysql> select i_title from ken6 where i_id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Thank you for a problem report. What result did you expected to get? READ_COMMITTED means that you can not read data that are changing. Your first session updated the row you are trying to read in the second session, and its transaction is not commited. So, it is locked, and after waiting for some time you got a message just about that.

For me it looks like intended behaviour, that simply should be documented in the prominent place. Our documentation says nothing about the implementation of transaction support in MySQL Cluster...

In my understanding, READ_COMMITTED means we don't wait for UPDATE statement at all and get the committed data.
And, InnoDB behave so under READ COMMITTED isolation level.
And, MySQL Cluster do so with simple table, like;
create table xxx(a int, b int) type=ndb;

According to the online manual, MySQL Cluster supports only READ_COMMITTED.
http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html

The same thing happened with the following table;
CREATE TABLE `ken11` (
  `i_id` int(11) NOT NULL default '0',
  `i_desc` text,
  PRIMARY KEY  (`i_id`)
) ENGINE=ndbcluster

So, we can re-state;
"MySQL Cluster's behave will change if the table include text field."

If a select includes a blob then read-committed is converted to
read with read-lock.  This is to guarantee consistency since
blob parts are read from another table.

select doesn't include text or blob.
the table includes text.

Sorry, but would you please explain how and when blob parts are read from another table?

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

k hirohama: With BLOBs, any portion over 255 bytes in length is stored in a separate table. AFAIK, this also applies to TEXT columns (and long VARCHARs in versions where these are supported).

As for how this affects transactions, we are updating the documentation. You might also wish to discuss these issues on the Cluster mailing list.