Bug #26333 Nested select statement is not locking in shared mode

• Submitted: 13 Feb 2007 16:42
• Modified: 13 Feb 2007 16:46
• Reporter: Bas Joosten
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.24
• OS: MacOS (Mac OS X)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[13 Feb 2007 16:42] Bas Joosten

Description:
Nested select statement is not locking in shared mode.
I expect to see an error or get a timeout, but I get none.

How to repeat:
I have an empty innoDB table: innoTableTest (ID,F1,F2,F3)

in process one I execute:
START TRANSACTION
SELECT F1.a FROM (SELECT 1 AS a) AS F1 WHERE EXISTS (SELECT * FROM innoTableTest)

in process two:
START TRANSACTION
INSERT INTO innoTableTest (F1,F2,F3) VALUES ('A','B','C')
COMMIT

this should not work (produce an error), because it leaves process one believing that innoTableTest is empty when it is not. But I do not see any error when I expect to see one.

Suggested fix:
Workaround:

Changing the query to SELECT F1.a FROM (SELECT 1 AS a) AS F1 WHERE EXISTS (SELECT * FROM innoTableTest LOCK IN SHARE MODE) solves the problem

[13 Feb 2007 16:46] Heikki Tuuri

Bas,

this is not a bug. This is how the non-locking consistent read of InnoDB, Oracle, PostgreSQL, etc. works.

To set locks in a SELECT statement, you need to use the FOR UPDATE or LOCK IN SHARE MODE clause, or SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE.

Regards,

Heikki