InnoDB transaction model

At first, lst's understand some technologies used in mysql transaction model:

1. Next-Key Locking

http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html 写道

InnoDB performs the row-level locking in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters.

The next-key locks that InnoDB sets on index records also affect the “gap” before that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the index order.

 from the introduction, we kown that the next-key locking is the composite of normal lock and 'gap' lock. Besides locks the selected records, it also locks the 'gap'  the range index scan iterated in the index order.  The explaination may obscure. So i am going to take an example to illustrate it.

There are my testing environment:

window xp sp2
Mysql 5.0.20

create table parent
(
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `parent` VALUES (3,'p3'),(5,'p5');

(1) in the transaction one(T1 for short) execute the following SQL:

select * from parent where id > 2 for update;

 the records 3 and 5 will be displayed.

Then, in the transaction two(T2 for short) execute an update sql :

update parent set name = 'newp3' where id=3;

 If allthings goes well,  an error message will displayed after several seconds :

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 Than , execute three insert sql:

insert into parent values(1,'p1');

insert into parent values(2,'p2');
insert into parent values(4,'p4');

 the first one execute successfully and the others are fail with the same error message as listed above.

(2) restore the records,  and change the sql executed in T1 to :

select * from parent where id < 3 for update;

 then the same three sql in T2 executed again. the result reverse : the first two fail and the last success.

(3) restore the records, then in T1 execute:

select * from parent where id = 3 for update;

 after that, execute the following in T2:

insert into parent values(2,'p2');
update parent set name='new' where id=3;
insert into parent values(4,'p4');

 The result is the two insert sql success and the rest fail.

From the results of  three different  condition,  I think we can clearly realize the meaning of 'gap' lock and  rows actually be locked.

2. Consistent Read

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html 写道

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions.

If you are running with the default REPEATABLE READ isolation level, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses

InnoDB uses a consistent read for select in clauses like INSERT INTO ... SELECT and UPDATE ... (SELECT) that do not specify FOR UPDATE or IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to serializable.

 

Four transaction levels supported by mysql

1. READ UNCOMMITTED

SELECT statements are performed in a non-locking fashion,
may occur the problem of 'dirty read' .

2. READ COMMITTED

All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements lock only the index records, not the gaps before them

UPDATE and DELETE statements using a unique index with a unique search condition lock only the index record found

In range-type UPDATE and DELETE statements, InnoDB must set next-key or gap locks

 3. REPEATABLE READ

SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use a unique index with a unique search condition lock only the index record found

With other search conditions, these operations employ next-key locking

 4. SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE.

 In the rest of the article, i will concentrate on the two widely used isolation levels : READ COMMITTED(RC for short) and REPEATABLE READ(rr for short). introduce their characteristic compare their difference.

(1)     from the introducation of consistent read above, we kown that  it is the default mode of the two isolation levels to process SELECT statment. But there is an important different  between them :  the consistent read , in RR, with the same transaction always read the same snapshot  established by the first of read; in contrast, each consistent read set and reads its own fresh snapshot. in RC. Let's make an experiment.

In RR:

T1 :
start transaction;
select * from parent; // list 1,3,5
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;  commit;
select * from parent; // list 1,3
T1:
select * from parent; //also list 1,3,5

In RR:

T1 :
start transaction;
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;
select * from parent; // list 1,3
commit;
T1:
select * from parent; // list 1,3

 The different results of the two situation make it clear that every consistent read always read the snapshot established in the time point the first read executed. So only the operation result of  transaction  committed  before it can be seen.

In RC:

T1 :
start transaction;
select * from parent; // list 1,3,5
T2:
start transaction;
select * from parent; // list 1,3,5
delete from parent where id=5;
select * from parent; // list 1,3
commit;
T1:
select * from parent; // list 1,3
T2:
start transaction;
select * from parent; // list 1,3
delete from parent where id=3;
select * from parent; // list 1
commit;
T1:
select * from parent; // list 1

 Obviously, each consistent read fresh its snapshot and see the lastest committed result.

(2)      different lock mechanism is used for SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements in RC.

In RC:

T1:
select * from parent; // list 1,3,5
select * from parent where id > 2 for update; // list 3,5
T2:
select * from parent; // list 1,3,5
update parent set name='xxx' where id=3; // error
insert into parent values(4,'p4'); //success
select * from parent; // list 1,3,4,5

 Comparing the result with the example one in the first chapter,  the insert operation here work successfully even though the record id 4 is in the scope of index 'gap' scanned by the select... for update statment. That is to say, in RC,  the select...for update statment does not put  'gap' lock on table.