Bug #65086 DML queries block SELECTs using unique index lookups
Submitted: 24 Apr 2012 7:09 Modified: 29 Apr 2012 13:11
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:7.1, 7.2 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[24 Apr 2012 7:09] Hartmut Holzgraefe
Description:
A SELECT that has a WHERE clause like "WHERE unique_column = constant_value"
is blocked by INSERTs or UPDATEs touching the row that has "unique_column = constant_value"

How to repeat:
   Connection #1                 |   Connection #2
                                 |  
CREATE TABLE t1 (                | 
 id INT PRIMARY KEY,             | 
 val INT UNIQUE,                 |
 val2 INT                        |
) ENGINE=ndb;                    |
                                 |
BEGIN;                           |
INSERT INTO t1 VALUES(23,42,0);  |
                                 |
                                 |   SELECT * FROM t1 WHERE val=42;
                                 |    -> Lock wait timeout exceeded
                                 | 
                                 |   SELECT * FROM t1 IGNORE INDEX(val) WHERE val=42;
                                 |    -> returns empty result set without blocking
                                 |
COMMIT;                          |
                                 |
BEGIN;                           |
UPDATE t1 SET val2=1 WHERE id=23;|
                                 |
                                 |   SELECT * FROM t1 WHERE val=42;
                                 |    -> Lock wait timeout exceeded
                                 | 
                                 |   SELECT * FROM t1 IGNORE INDEX(val) WHERE val=42;
                                 |    -> returns empty result set without blocking
 

Suggested fix:
As the isolation level is READ COMMITTED i do not expect pure reads to block due to uncommitted changes in a different transaction ...