| Bug #65086 | DML queries block SELECTs using unique index lookups | ||
|---|---|---|---|
| Submitted: | 24 Apr 2012 7:09 | Modified: | 14 Feb 2020 1:02 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Documentation | Severity: | S3 (Non-critical) |
| Version: | ALL | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
| Tags: | locking, ndb, unique key | ||
[14 Feb 2020 1:02]
Jon Stephens
Fixed in NDB 7.3+ documentation in mysqldoc rev 65075. See e.g. https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-limitations-transactions.html Closed.
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 ...