1

I am new with mysql transaction serializable. When I tried to update a row of a table while other concurrent sessions are reading it, the update transaction has to wait until the read transactions complete. Is there any way to allow the write transaction to proceed forward (then the read transactions should fail to commit)?

Here is my test to verify the property above:

Session 1:

set transaction isolation level serializable;
create database locktest;
use locktest;
create table locktest(id int not null primary key auto_increment, text varchar(100));
insert into locktest(text) values('text1');

start transaction;
select * from locktest where id=1;

Session 2:

use locktest;
update locktest set text='new_text2' where id=1;  -- it gets stuck at this point and only proceed forward when I 'commit;' in session 1

What I want is that the update of session 2 must success immediately, and the transaction of session 1 should be aborted. Any suggestion? Thanks.

Hieu Nguyen
  • 382
  • 2
  • 15

1 Answers1

0

SERIALIZABLE in the InnoDB implementation just means that a SELECT will behave like SELECT...LOCK IN SHARE MODE.

If session 1 doesn't use START TRANSACTION, but simply relies on autocommit, then MySQL's idea of SERIALIZABLE will run a read-only statement like SELECT exactly like REPEATABLE-READ. It won't lock anything, and it won't block the UPDATE in session 2.

But there's no way for the UPDATE in session 2 to cause the SELECT in session 1 to abort. That's not the behavior of SERIALIZABLE.

See also my answer to MySQL - Mutual exclusion in transactions and locks? which coincidentally came up earlier today.

And you can read more detail about the transaction isolation modes here: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • So if I have two read-write transactions: one reads data item 1 then updates data item 2, and one updates data item 1 and then reads data item 2, there is no way for them to happen concurrently. Two transactions wait for each other until the lock wait timeout exceeds and try again with the hope that a transaction would get all the locks on data items required and be able to proceed forward. Is that correct? – Hieu Nguyen Nov 19 '14 at 17:18
  • I worked with Oracle before and in this case, the transaction returns something like 'ORA 8177. Cannot serialize access for this transaction'. Is it the same as 'lock wait timeout exceeds' in mysql? – Hieu Nguyen Nov 19 '14 at 17:25
  • @HieuNguyen, in the scenario you describe, if you're using SERIALIZABLE, the first transaction acquires a shared lock on data item 1, and an exclusive lock on data item 2. The second transaction needs an exclusive lock to update data item 1, but it can't get an exclusive lock while there's a shared lock on that data. So the second transaction waits until the lock wait timeout. – Bill Karwin Nov 19 '14 at 18:23