Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm learning by mostly reading the documentation. Unfortunately, http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

doesn't say anything, while it says everything. Confused? Me too.

ndb engine supports only "READ_COMMITTED" transaction isolation level.

A. It starts by saying "sets and reads its own fresh snapshot", which I translate to: The transaction is having a separated 'zone' which whatever it stores there - is what it reads back.

B. While out-side of the transaction, the old-values are unlocked.

C. It continues with: "for locking reads" sentence - No idea what it means.

Question: they claim only READ_COMMITTED transaction isolation level is supported, but while handling a BLOB or a TEXT, they say the isolation is now "locked for reading" too. So is it a contradiction? can a transaction LOCK for reading just as well while handling something other than BLOB/TEXT? (such as integers)

share|improve this question

2 Answers 2

Look at this mysql bug report:

http://bugs.mysql.com/bug.php?id=15282

share|improve this answer
1  
Please include relevant information. –  Lucas Kauffman Jul 22 '12 at 19:02
28 Nov 2005 7:51] Kenji Hirohama
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
[28 Nov 2005 8:48] Valeriy Kravchuk
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...
[28 Nov 2005 8:54] Kenji Hirohama
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
[29 Nov 2005 4:48] Kenji Hirohama
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."
[29 Nov 2005 11:13] Pekka Nousiainen
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.
[29 Nov 2005 12:28] Kenji Hirohama
select doesn't include text or blob.
the table includes text.
[30 Nov 2005 4:21] Kenji Hirohama
Sorry, but would you please explain how and when blob parts are read from another table?
[14 Dec 2005 17:28] Jon Stephens
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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.