At first, lst's understand some technologies used in mysql transaction model:
1. Next-Key Locking
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:
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;
(1) in the transaction one(T1 for short) execute the following SQL:
the records 3 and 5 will be displayed.
Then, in the transaction two(T2 for short) execute an update sql :
If allthings goes well, an error message will displayed after several seconds :
Than , execute three insert sql:
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 :
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:
after that, execute the following in T2:
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
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
may occur the problem of 'dirty read' .
2. READ COMMITTED
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
With other search conditions, these operations employ next-key locking
4. SERIALIZABLE
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.
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
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.
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.
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.