第20章　InnoDB锁 - xiaoboluo768/qianjinliangfang GitHub Wiki

20.2　加锁验证

CREATE TABLE `t` (
`id` int(11) DEFAULT NULL,
`name` char(20) DEFAULT NULL
);
insert into t values (10,'donghongyu'),(20,'lichun'),(30,'luoxiaobo');

20.2.1　REPEATABLE-READ隔离级别+表无显式主键和索引

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t for update;
+------+------------+
| id   | name        |
+------+------------+
| 10   | donghongyu |
| 20   | lichun      |
| 30   | luoxiaobo  |
+------+------------+
3 rows in set (0.00 sec)

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
5 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
5 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (9, 'hanjie');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
7 rows in set (0.00 sec)

20.2.2　REPEATABLE-READ隔离级别+表有显式主键但无索引

mysql> show create table dhytest\G
*************************** 1. row ***************************
       Table: dhytest
Create Table: CREATE TABLE `dhytest` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

• 1．不带where条件

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+------+------------+
| id    | name        |
+------+------------+
| 10    | donghongyu |
| 20    | lichun      |
| 30    | luoxiaobo   |
+------+------------+
3 rows in set (0.00 sec)

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
5 rows in set (0.00 sec)

• 2．where条件是主键字段

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql>  select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
2 rows in set (0.00 sec)

• 3．where条件包含主键字段和非主键字段

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 10 and name = 'donghongyu' for update;
+----+------------+
| id | name         |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.00 sec)

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
2 rows in set (0.00 sec)

20.2.3　REPEATABLE-READ隔离级别+表无显式主键但有索引

• 2．普通索引

mysql> create index idx_id on t (id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
4 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t values (9, 'hanjie');
// 阻塞
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
6 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t values (19, 'hanjie');
// 阻塞
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
6 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t values (20, 'hanjie');
Query OK, 1 row affected (0.00 sec)

• 3．唯一索引

mysql> drop index idx_id on t;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create unique index idx_id on t (id);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 10 for update;
+----+------------+
| id | name        |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
2 rows in set (0.00 sec)

20.2.4　REPEATABLE-READ隔离级别+表有显示主键和索引

• 1．表有显式主键和普通索引

mysql> show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL,
`name` char(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+----+------------+
| id | name         |
+----+------------+
| 10 | donghongyu |
| 20 | lichun      |
| 30 | luoxiaobo  |
+----+------------+
3 rows in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
8 rows in set (0.00 sec)

mysql> select * from t where name = 'donghongyu' for update;
+----+------------+
| id | name |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.00 sec)
mysql> select * from data_locks;
......
4 rows in set (0.00 sec)

20.2.5　READ-COMMITTED隔离级别+表无显式主键和索引

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+----+------------+
| id | name        |
+----+------------+
| 10 | donghongyu |
| 20 | lichun     |
| 30 | luoxiaobo  |
+----+------------+
3 rows in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
4 rows in set (0.00 sec)

mysql> select * from t where id = 10 for update;
+----+------------+
| id | name           |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.01 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
2 rows in set (0.00 sec)

20.2.7　READ-COMMITTED隔离级别+表无显式主键有索引

• 2．普通索引

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id = 10 for update;
+----+------------+
| id | name         |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
3 rows in set (0.00 sec)

20.2.8　READ-COMMITTED隔离级别+表有显式主键和索引

• 1．表有显式主键和普通索引

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+----+------------+
| id | name          |
+----+------------+
| 10 | donghongyu |
| 20 | lichun       |
| 30 | luoxiaobo   |
+----+------------+
3 rows in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
7 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where name = 'donghongyu' for update;
+----+------------+
| id | name          |
+----+------------+
| 10 | donghongyu |
+----+------------+
1 row in set (0.00 sec)
mysql> select ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID , THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS , LOCK_DATA from data_locks;
......
3 rows in set (0.00 sec)

---

上一篇：第19章　事务概念基础 | 下一篇：第21章　SQL优化