MySQL isolation level and lock-related

There are three types of locks, gap locks (gap-lock), row locks (record-lock) in the MySQL database, Gap-record-lock

The Gap-record-lock is a combination of gap and row locks,

Gap locks (Gap Lock)

Row lock (Record Lock)

Record-lock:

Test table:

CREATE TABLE ' TT ' (

' Tid ' int (one) not NULL DEFAULT ' 0 ',

' tname ' varchar DEFAULT NULL,

' Tage ' tinyint (4) DEFAULT NULL,

' taddr ' varchar DEFAULT NULL,

' Tmark ' varchar (+) DEFAULT NULL

) Engine=innodb DEFAULT Charset=utf8

Regardless of the isolation level, the following actions are to get the table's metadata lock

TRUNCATE,DDL (alter,drop,create), adding an index adds a metadata lock to the table, that is, locks the entire table

In the case of an isolation level that is RR, the representation of the Delete,update,insert lock

Delete from tt where TID = 1

Update tt Set taddr = "Beijing" Where Tid = 1

1.tid PRIMARY Key

(1). 1 exists in the table to add Record-lock to the record

(2). 1 does not exist in the table Gap-lock, row record of the gap lock

2.tid is a normal field (no index) to add to all records, Record-lock,grap-lock

3.tid is a nonclustered unique index

(1). 1 exists in the table to add Record-lock to the record, which locks the index value and row data

(2). 1 does not exist in the table add Gap-lock, which locks a range value of the index

4.tid is a nonclustered non-unique index

(1). 1 exists in the table to add Record-lock,gap-lock, Row Records, index records are added Record-lock, a certain range of the index is locked

(2). 1 does not exist in the table, it locks a range of indexes

Insert

Add Intent Insert Lock to check if a range is locked

The gap lock only affects insertions and does not affect update and delete

1. Only the primary key adds an X lock to the record

2. There is a unique key, the S lock is added to the unique key value

3. Have primary key and unique key, add X to primary key value, add s to unique key value

INSERT INTO test1 SELECT * from Test

In the case of RR, Test adds an S lock, Test1 added X

When the isolation level is RC, the representation of the Delete,update,insert lock

Delete from tt where TID = 1

Update tt Set taddr = "Beijing" Where Tid = 1

1.tid PRIMARY Key

(1). 1 exists in the table to add Record-lock to the record

(2). 1 does not exist in the table and does not add locks

2.tid adds a lock to all records as a normal field (no index)

3.tid is a nonclustered unique index

(1). 1 exists in the table to add Record-lock to the record, which locks the index value and row data

(2). 1 does not exist in the table does not lock data

4.tid is a nonclustered non-unique index

(1). 1 exists in the table to add Record-lock, Row Records, index records are added Record-lock

(2). 1 does not exist in the table does not add locks

Insert

1. Only the primary key adds an X lock to the record

2. There is a unique key, the S lock is added to the unique key value

3. Have primary key and unique key, add X to primary key value, add s to unique key value

INSERT INTO test1 SELECT * from Test

Test does not add lock in RC case, test1 add x Lock

SELECT * into outfile from test does not lock test table (RR,RC has validation)

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1879590

MySQL isolation level and lock-related