Various SQL plus locks

This morning and colleagues hot debate, arguing about the lock on a few basic concepts.

Record lock, Gap lock, Next_key lock

At that time, I found myself understanding the lock more thoroughly: The isolation level of the transaction is through the lock to achieve AH.

RC: Record lock only
RR: With record and Next_key lock

Now let's talk about the lock implementations of various SQL statements in RR mode:

CREATE TABLE t1 (ID int primary KEY auto_increment,
col1 int NOT NULL default 0,
col2 varchar (+) NOT null default ' ',
COL3 datetime NOT NULL default ' 2010-01-01 00:00:00 ');

First write a stored procedure for production test data:
Delimiter $$
CREATE PROCEDURE Proc_generate_data (in I_rec_num int,in i_prefix_name varchar (a), in I_is_order tinyint)
Begin
declare rec_num int;
DECLARE prefix_name varchar (20);
declare Is_order tinyint;
declare i int;
Set i=0;
Set Rec_num=i_rec_num;
Set Prefix_name=i_prefix_name;
Set Is_order=i_is_order;
Loop1:while (I<rec_num) do
Set @c1 =floor (Round (rand (), 5) *100000);
Set @c2 =concat (Prefix_name, @c1);
Set @c3 =now ();
If Is_order = 0 Then
INSERT into T1 (COL1,COL2,COL3) VALUES (@c1, @c2, @c3);
ElseIf Is_order = 1 Then
Set @id = Floor (round (rand (), 5) *100000);
Select 1 into @if_exist from t1 where [email protected];
If @if_exist! = 1 Then
INSERT into T1 (ID,COL1,COL2,COL3) VALUES (@id, @c1, @c2, @c3);
Else
INSERT into T1 (COL1,COL2,COL3) VALUES (@c1, @c2, @c3);
End If;
Else
INSERT into T1 (COL1,COL2,COL3) VALUES (+, ' Nihao ', @c3);
End If;
Set i= i+1;
End While LOOP1;
End $$
delimiter;

To generate test data:

Sequential production of 10,000 call Proc_generate_data (10000, ' first ', 0);

Non-sequential generation of 10,000 call Proc_generate_data (10000, ' second ', 1);

Sequential production of 10,000 call Proc_generate_data (10000, ' third ', 0);

To start the test:

Insert

The single insert, only the primary key index, is Record_lock or Next_key_lock?

Experimental result: Only record records are locked

No.2 insert single, have primary key and normal index, is Record_lock or Next_key_lock?

Experimental result: Only record records are locked

No.3 Insert...values (), (), (), primary key and normal index, record_lock or Next_key_lock?

Experimental result: Only record records are locked

Update

No.4 Update...set...where id= .... When I go to the primary key, is it record_lock or Next_key_lock?

experimental Behavior When this UPDATE statement is not committed. Other session Execute UPDATE statement if yes

go to the primary key index : If the record is locked, it is blocked

Normal index : None of the others will be blocked unless the primary key corresponding to the normal index is a record recording.

or do not walk index : all blocked

Experimental results: When you go to the primary key update, only the record corresponding to the primary key is locked. When the ID value does not exist, the other session allows the ID to be update but is not allowed to insert the ID

No.5 update...set...where col1= .... When walking a normal index, is Record_lock or next_key_lock generated?

Experimental phenomena:

Mysql> SELECT * from T1 where col1=91190;
+------+-------+-------+---------------------+
| ID | col1 | col2 | col3 |
+------+-------+-------+---------------------+
| 1496 | 91190 | 98879 | 2016-06-26 01:53:42 |
+------+-------+-------+---------------------+

Update T1 set col2= ' 98879 ' where col1=91190; (presence of Col1 Records 91186, 91190, 91197), other session:

To be clogged:

Update T1 set col2= ' 98879 ' where col1=91190;

Update T1 set col2= ' 98879 ' where id=1496;

INSERT into T1 values (200012,91186, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200013,91187, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200014,91188, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200015,91189, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200016,91190, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200017,91191, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200018,91192, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200019,91193, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200020,91193, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200021,91194, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200022,91195, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200023,91196, ' OK ', ' 2016-06-26 01:59:12 ');

Delete from T1 where col1=91190;

Delete from T1 where id=1496;

Non-clogging:

SELECT * from t1 where col1=91191 for update;

SELECT * from t1 where col1=91192 for update;
SELECT * from t1 where col1=91193 for update;

SELECT * from t1 where col1=91186 for update;

SELECT * from t1 where col1=91187 for update;

SELECT * from t1 where col1=91188 for update;

SELECT * from t1 where col1=91189 for update;

INSERT into T1 values (200025,91185, ' OK ', ' 2016-06-26 01:59:12 ');

INSERT into T1 values (200026,91197, ' OK ', ' 2016-06-26 01:59:12 ');

Experimental results:

When the col1 value exists, Update...set...where col1= .... When you go to the normal index,

Create a record lock on 91190,

A gap lock is generated for [91186,91190], [91191,91197], which allows update but does not allow the insert

When the col1 value does not exist, such as:mysql> update T1 set col2= ' 98879 ' where col1=91189;

for [91186,91189] plus gap lock, this gap lock allows update but does not allow insert

No lock on other ranges.

No.6 update...set...where id<3000 .... When I go to the primary key, is it record_lock or Next_key_lock?

Experimental results:

The lock is the value of ID in [ negative Infinity -3000] ID existence record;

No.7 update...set...where col1<300 .... When walking a normal index, is Record_lock or next_key_lock generated?

The lock is col1 in [ negative Infinity-300) Next_key_lock and records the record lock corresponding to the primary key

The record value of the same No.5 col1 does not allow update, allows update within the gap, does not allow insert

No.8 Update...set...where col1<3000 .... When you do not walk col1 index, is Record_lock or next_key_lock generated?

The lock is col1 in [ negative infinity-positive infinity] Next_key_lock and records the record lock corresponding to the primary key

The record value of the same No.5 col1 does not allow update, allows update within the gap, does not allow insert

Mixed SQL

No.9 insert ... select ... where col1<3000 .... When you do not follow the index, are record_lock or next_key_lock generated?

The lock is the next_key_lock of the table T1 col1 in [ negative infinity-3000] and records the record lock corresponding to the primary key

The record value of the same No.5 col1 does not allow update, allows update within the gap, does not allow insert

Typical deadlock simulation test

Various SQL plus locks