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.
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