I observed the following case, which looks like a bug for isolation level “repeatable read” from my point of view
I have table:
CREATE TABLE IF NOT EXISTS campaign ( id int NOT NULL AUTO_INCREMENT, status enum('active','blocked','deleted') NOT NULL DEFAULT 'active' );
INSERT INTO campaign (`status`) VALUES ("deleted");
[LIST=1]
[]
[]update the record
UPDATE campaign SET `status`="active" WHERE id=1;
[]received this update through libslave.
[]create a new connection and make query “SELECT status FROM campaign WHERE id=1”
[*]expected that status will be “active”, but actually status is “deleted”
[/LIST]
if I change the isolation level to “Read-Commited”, I get the status “active” in step 5 as expected.
Adding read lock in previous query also works. the query
SELECT status FROM campaign WHERE id=1 LOCK IN SHARE MODE;
also returns status “active” even isolation level is “repeatable read”