MySQL Bugs: #4669: Scans do not ignore uncommitted inserts (instead hang on lock)

Bug #4669	Scans do not ignore uncommitted inserts (instead hang on lock)
Submitted:	21 Jul 2004 11:28	Modified:	30 Jul 2004 19:11
Reporter:	Martin Skold	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	4.1	OS:
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
Scans in the ndbcluster handler currently do not use lock mode CommittedRead.
This results in scans hanging on locks of uncommitted inserts.

How to repeat:
From bug#4643:

create table abc_n (x integer not null primary key, y varchar(32)) engine =
ndb;
create table abc_i (x integer not null primary key, y varchar(32)) engine =
innodb;

#client a:
insert into abc_n values (1,'one'), (2,'two');
insert into abc_i values (1,'one'), (2,'two');
commit;

#client a:
select * from abc_n; 
# returns two rows
select * from abc_i;
# returns two rows

#client b:
select * from abc_n; 
# returns two rows
select * from abc_i;
# returns two rows

# now try this:

#client a:
start transaction;
insert into abc_i values (3,'three');
insert into abc_n values (3,'three');
select * from abc_i; 
# returns three rows
select * from abc_n;
# returns three rows

# without committing go to
# client b:
select * from abc_i; 
# returns two rows
select * from abc_n;
# hangs for about a second, then
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I would have expected the select * from abc_n to return two rows (i.e. the
committed rows) as in the innodb case.

Suggested fix:
Change lockmode from NdbScanOperation::LM_Read to NdbScanOperation::LM_CommittedRead.

Please also run these transactions and verify that it is read committed (when the scan hang on a lock problem is solved)

CLIENT A:
mysql> create table t1(a integer not null primary key, b integer not null)
type=ndb;
Query OK, 0 rows affected, 1 warning (0.68 sec)

populate with data:
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 10 |
| 2 | 10 |
| 4 | 10 |
| 5 | 10 |
| 3 | 10 |
| 1 | 10 |
+---+----+
8 rows in set (0.00 sec)

CLIENT A:

mysql> create index t1_b_idx on t1(b);

mysql> begin;
mysql> update t1 set b=b+1 where b=10;

CLIENT B:
mysql> select * from t1;

CLIENT A:
mysql> commit;

Please check the result and if that complies with read_committed

mysql> create table t1(a integer not null primary key, b integer not null)
    -> type=ndb;
Query OK, 0 rows affected, 1 warning (0.74 sec)

mysql> insert into t1 values (1,10),(2,10),(3,10),(4,10),(5,10),(6,10),(7,11),(8,11);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
 
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 10 |
| 2 | 10 |
| 4 | 10 |
| 5 | 10 |
| 3 | 10 |
| 1 | 10 |
+---+----+
8 rows in set (0.01 sec)
 
mysql> create index t1_b_idx on t1(b);
Query OK, 8 rows affected (1.85 sec)
Records: 8  Duplicates: 0  Warnings: 0

Client A:
 begin; update t1 set b=b+1 where b=10;
Query OK, 0 rows affected (0.00 sec)
 
Client B:
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 10 |
| 2 | 10 |
| 4 | 10 |
| 5 | 10 |
| 3 | 10 |
| 1 | 10 |
+---+----+
8 rows in set (0.07 sec)

Client A:
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
 
Client B:
mysql> select * from t1;
+---+----+
| a | b  |
+---+----+
| 7 | 11 |
| 8 | 11 |
| 6 | 11 |
| 2 | 11 |
| 4 | 11 |
| 5 | 11 |
| 3 | 11 |
| 1 | 11 |
+---+----+
8 rows in set (0.06 sec)

Excellent!