Next-key locking?

[Constantin Michael]

Hi,

Can anyone explain the following scenario.

Given a single table 't', with a single integer primary key, why would user B have to wait.

User A

START TRANSACTION;
SELECT * FROM t WHERE t.PrimaryKey <= 3 FOR UPDATE;
COMMIT;

User B

START TRANSACTION;
SELECT * FROM t WHERE t.PrimaryKey > 3 LOCK IN SHARE MODE;
COMMIT;

Surely, seeing that the set of primary keys < 3 being {1,2} and the set of primary keys >=3 being {3, 4, ..., n}, n = number of rows in the table, there would be no reason for user B to wait, seeing that they are mutually exclusive?

Many thanks

Constantin