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
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.13-nt
• OS: Windows (Win 2000)
• CPU Architecture: Any

[24 Jun 2003 1:12] Roozbeh Ghaffari

Description:
<quote>
SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE.
</quote>

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;
  begin;
  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

Hi!

You have to use the command

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

Regards,

Heikki