Recently, I asked some questions about the lock in the interview. Today, I will make a further test and summary on the lock.  

Meaning of lock mode:

• 9: Intentional exclusive lock
• 10: Lock the gap between the record itself and before the record, exclusive
• S: Lock the record itself and the gap before the record, and share
• X,REC_NOT_GAP: lock the record itself, exclusive
• S,REC_NOT_GAP: lock the record itself, share
• 10. GAP: do not lock the record itself, exclusive
• S. GAP: do not lock the record itself, share
• X，GAP,INSERT_ Intent: insert intent lock

MyISAM engine

For MyISAM engine, the select statement plus lock in share mode or for update is meaningless. MyISAM must use lock table to lock the table.

INNODB engine

Open the status log of INNODB

test data

RR isolation level

1. No display primary key

Create test table

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

(1) No query criteria attached

set autocommit=0;
begin;
select * from t for update;
show engine innodb status;

Here, temporary locks are added to records, which are actually hidden rows_ ID to lock.  

The first paragraph is to lock (- ∞, 10] directly

The second paragraph is to lock (10,20] directly

The third paragraph is to lock (20,30] directly

asc supremun refers to locking (30, + ∞)

(2) Add query criteria

set autocommit=0;
begin;
select * from t where id = 10 for update;
show engine innodb status;

From the lock status, the lock status has not changed. It can be seen that in RR mode, locking records that do not display a primary key is a gap lock for all records regardless of the conditions.

2. Display primary key exists

(1) Lock all records

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

The log information at this time is the same as that without displaying the primary key.

(2) Lock row records

--In this case, the primary key is used first ID Make a row query
select * from t where id = 20 for update;

Lock the record of line 20, which shows that the record of line change is locked.

(3) index failure

select * from t where id < 20 or name is not null for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 20 |    1 |  16 |
| 30 |    2 |  17 |
| 40 |    3 |  18 |
+----+------+-----+

 3. There is no primary key, but there is a common index in the document

(1). Lock all records

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  key t_id_IDX (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

 (2). Lock common index

select * from t where id = 10 for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
+----+------+-----+
1 row in set (0.00 sec)

1. index t_id_IDX of table `employees`.`t` trx id 10546 lock_mode X

yes t_id_IDX General index(-∞,10]Add temporary lock between sections 
2. index GEN_CLUST_INDEX of table `employees`.`t` trx id 10546 lock_mode X locks rec but not gap
 Row lock on clustered index
3.index t_id_IDX of table `employees`.`t` trx id 10546 lock_mode X locks gap before rec
 yes t_id_IDX General index(10,20)

 4. There is no primary key, and there is a unique index for the document

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  UNIQUE KEY `t_UN` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

yes(-∞,10)10(10,20)20(20,30)30(30,+∞)Lock

5. There are primary keys and unique indexes

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_UN` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t  for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
| 20 |    2 |   0 |
| 30 |    3 |   0 |
+----+------+-----+
3 rows in set (0.00 sec)

yes(-∞,10)10(10,20)20(20,30)30(30,+∞)Lock

6. There are primary keys and non unique indexes

--Test table
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` tinyint(3) unsigned NOT NULL,
  `age` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX KEY `t_UN` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--insert data
INSERT INTO t (id, name, age) VALUES(10, '1', 0),(20, '2', 0),(30, '3', 0);

set autocommit=0;
begin;
select * from t where name='1' for update;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 10 |    1 |   0 |
+----+------+-----+

index t_name_IDX of table `employees`.`t` trx id 10676 lock_mode X
 yes t_name_IDX (-∞,'1']Lock
index PRIMARY of table `employees`.`t` trx id 10676 lock_mode X locks rec but not gap
 yes index PRIMARY 10 Row locking
index t_name_IDX of table `employees`.`t` trx id 10676 lock_mode X locks gap before rec
 yes t_name_IDX ('1','2')Gap lock

RC isolation level

1. No display primary key

Yes, row_id row lock

2. Display primary key exists

Add row lock to primary key