InnoDB Lock Demo

CREATE TABLE T1 (c1 int) unsigned not null default ' 0 ', C2 Int (Ten)  unsigned not null default ' 0 ', C3 Int (Ten)  unsigned not null default ' 0 ',  c4 Int (Ten)  unsigned not  Null default ' 0 ',  primary key (C1), key C2 (C2)) engine=innodb  |    C1 |  C2  |   C3  |  c4  | +-----+-----+-----+-----+  |   0   |   0   |    0  |   0   |  |   1   |   1   |    1  |   0   |  |   3   |   3   |    3  |   0   |  |   4   |   2   |    2  |   0   |  |   6   |   2   |    2  |   0   |  |   8   |   6   |    6  |   0   |  | &nbsP 10 |   4   |    4  |   0   |    Isolation level for rr  Example 1:

T1	T2
Begin	Begin
SELECT * from t1 where c1=3 for update |  C1 |   C2 |  C3 | C4 |   +-----+-----+-----+-----+  |   3 |    3 |   3 | 0 |
	SELECT * from T1 where c1=3 lock in share mode This situation is blocked because T1 is an exclusive lock, the T2 is a shared lock, and the exclusive lock and the shared lock are mutual.

Example 2:

T1	T2
Begin	Begin
SELECT * from T1 where c1=3 lock in share mode |  C1 |   C2 |  C3 | C4 |   +-----+-----+-----+-----+  |   3 |    3 |   3 | 0 |
	SELECT * from t1 where c1=3 for update This situation is blocked because T1 is a shared lock, T2 is an exclusive lock, exclusive lock and shared lock are mutually exclusive.

Example 3:

T1	T2
Begin	Begin
SELECT * from t1 where c1=3 for update |  C1 |   C2 |  C3 | C4 |   +-----+-----+-----+-----+  |   3 |    3 |   3 | 0 |
	SELECT * from t1 where c1=3 This situation is not blocked because T2 is a one-time non-locking read.

Example 4:

T1	T2
Begin	Begin
SELECT * from T1 where c3=7 lock in share mode
	SELECT * from t1 where c3=10 for update Remark: C3 No Index This situation is blocked because the C3 is not indexed in T2, so it is escalated to a table-level lock.

Example 5:

T1	T2
Begin	Begin
SELECT * from T1 where c3=7 lock in share mode
	SELECT * from t1 where c1=6 for update Note: C1 is the primary key This situation is blocked because C3 without indexes in T1 can cause table-level locks.

Example 6:

T1	T2
Begin	Begin
SELECT * from T1 where c2=2 and c3=5 for update
	SELECT * from T1 where c2=2 and c3=7 for update Note: There is an index on the C2 column, there is no index on the C3 column, c3=7 does not exist This situation is blocked because c2=2 has an index on this column and an exclusive lock is added to the record. The C3 in T1 and T2 are not indexed, so it is not possible to determine whether it is the same record, and he only adds locks to all records.

Example 7:

T1	T2
Begin	Begin
SELECT * from T1 where c2=2 and c3=5 for update
	SELECT * from T1 where c2=3 and c3=7 for update Note: There is an index on the C2 column, there is no index on the C3 column, c3=7 does not exist This situation is not blocked, the locks are indexed, and c2=3 is not the same data in T1 c2=2 and T2.

Example 8:

T1	T2
Begin	Begin
SELECT * from T1 where c2=2 and c3=2 for update
	SELECT * from T1 where c1=4 and c3=10 for update Note: C1 is the primary key, C2 is a normal index, and there is no index on the C3 column This situation is blocked because locking on the c2=2 will eventually go back to the primary key c1=4.

Example 9:

T1	T2
Begin	Begin
Update T1 set c4=20 where c2>=4
SELECT * from t1 where c2>=4	SELECT * from t1 where c1=7 for update Note: The C1 column is the primary key, and the C2 column is a normal index, T1 affects two rows. This situation will not be blocked, the range of T1 locks is c2>=4 all records, and is the next lock, and C1=8 & c1=10 record Lock,t2 lock range is c1=7 record lock.

Example 10:

T1	T2
Begin	Begin
Update T1 set c4=20 where c2>=4
SELECT * from t1 where c2>=4	INSERT INTO T1 select 7,5,10,10 Note: The C1 column is the primary key, and the C2 column is a normal index, T1 affects two rows. This condition is blocked, the T1 lock is in all records of C2>=4, and is next lock, and the C1=8 & c1=10 record lock, 5>4 in the T2, so it will be blocked.

Example 11:

T1	T2
Begin	Begin
Update T1 set c4=20 where c2>=4
SELECT * from t1 where c2>=4	INSERT INTO T1 select 7,2,10,10 Note: The C1 column is the primary key, and the C2 column is a normal index, T1 affects two rows. This situation will not be blocked, the T1 lock is c2>=4 all records, and is next lock, and C1=8 & C1=10 's record lock, In T2 2<4, no matter T1 or T2 are not in the range, so will not be blocked.

Example 12:

T1	T2
Begin	Begin
Update T1 set c4=20 where c1>=6
	INSERT INTO T1 select 9,9,9,9 Note: The C1 column is the primary key, C1=6 already exists This situation is blocked because T1 locks all c1>=6 in the range record lock.

Example 13:

T1	T2
Begin	Begin
INSERT INTO T1 select 9,9,9,9
	INSERT INTO T1 select 7,7,7,7 Note: C1 columns are primary keys, c1=7 and c1=9 records do not exist This situation is not blocked because C1 and C2 are not in the same position

Example 14:

T1	T2
Begin	Begin
INSERT INTO T1 select 9,9,9,9
	INSERT INTO T1 select 9,9,9,9 Note: C1 column is primary key, c1=9 record does not exist This situation will be blocked because C1 and C2 are all in the same position.

InnoDB Lock Demo