Bug #705 Serializable isolation level does not behave the way it's supposed to
Submitted: 24 Jun 2003 1:12 Modified: 24 Jun 2003 7:48
Reporter: Roozbeh Ghaffari Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.0.13-nt OS:Windows (Win 2000)
Assigned to: CPU Architecture:Any

[24 Jun 2003 1:12] Roozbeh Ghaffari
SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE.

I started an xact with SERIALIZABLE isolation level and selected the entire table. Then in another connection I updated one of the rows of the table. It worked without being blocked!

If I do the same, but select the table with "LOCK IN SHARE MODE", the concurrent update will get blocked which is normal.

So it seems that either the above quote is not correct or there is a bug in innodb code.

How to repeat:
Connection 1:
  create table test (id int primary key, value varchar(10)) type=innodb;
  insert into test values (1, 'ONE');
  set autocommit=0;
  set transaction isolation level serializable;
  select * from test;

Connection 2:
  update test set value='TWO';      -- WORKS FINE WITHOUT BEING BLOCKED!

Connection 1:
  select * from test lock in share mode;

Connection 2:
  update test set value='THREE';    -- NOW IT GETS BLOCKED!

-- So "select * from test" and "select * from test lock ..." are not quite the same in this isolation level.

Suggested fix:
No suggestion.
[24 Jun 2003 7:48] Heikki Tuuri

You have to use the command


Otherwise it only affects the current transaction, not the next.