第19章:MySQL之锁

第19章:MySQL之锁


修订日期:2021-01-08


一. 锁

1.1. 锁的介绍

  • 什么是锁

    • 对共享资源进行并发访问
    • 提供数据的完整性和一致性
  • 每个数据库的锁的实现完全不同

    • MyISAM表锁
    • InnoDB 行锁(与Oracle的行锁不同)
    • MSSQL 行级锁 with 锁升级
  • latch

    • mutex
    • rw-lock
  • 锁的区别

    • latch 是针对程序内部的资源(比如:全局变量)的锁的定义,而这里的 lock 针对的是数据库的 事物

    • locklatch 来保证和实现

1.2. latch锁的查看

-- 主要给内核开发人员给予帮助
mysql root@localhost:mytest> show engine innodb mutex;
+--------+-----------------------------+-----------+
| Type   | Name                        | Status    |
+--------+-----------------------------+-----------+
| InnoDB | rwlock: fil0fil.cc:1381     | waits=1   |
| InnoDB | rwlock: fil0fil.cc:1381     | waits=2   |
| InnoDB | rwlock: dict0dict.cc:2687   | waits=12  |
| InnoDB | rwlock: dict0dict.cc:1184   | waits=166 |
| InnoDB | rwlock: fil0fil.cc:1381     | waits=5   |
| InnoDB | rwlock: log0log.cc:838      | waits=905 |
| InnoDB | rwlock: btr0sea.cc:195      | waits=1   |
| InnoDB | sum rwlock: buf0buf.cc:1460 | waits=825 |
+--------+-----------------------------+-----------+
8 rows in set
Time: 0.056s
mysql root@localhost:mytest>

1.3. 锁的类型

  1. S 行级共享锁
  2. X 行级排它锁锁
  3. IS
  4. IX
  5. AI 自增锁

1.4. 意向锁介绍

  1. 揭示下一层级请求的锁的类型
  2. IS:事物想要获得一张表中某几行的共享锁
  3. IX:事物想要获得一张表中某几行的排他锁
  4. InnoDB存储引擎中意向锁都是 表锁

  • 假如此时有 事物tx1 需要在 记录A 上进行加 X锁

    1. 在该记录所在的 数据库 上加一把 意向锁IX
    2. 在该记录所在的 上加一把 意向锁IX
    3. 在该记录所在的 上加一把 意向锁IX
    4. 最后在该 记录A 上加上一把 X锁
  • 假如此时有 事物tx2 需要对 记录B (假设和记录A在同一个页中)加 S锁

    1. 在该记录所在的 数据库 上加一把 意向锁IS
    2. 在该记录所在的 上加一把 意向锁IS
    3. 在该记录所在的 上加一把 意向锁IS
    4. 最后在该 记录B 上加上一把 S锁
  • 加锁从上往下一层一层进行加的

    锁兼容 X IX S IS
    X 冲突 冲突 冲突 冲突
    IX 冲突 兼容 冲突 兼容
    S 冲突 冲突 兼容 兼容
    IS 冲突 兼容 兼容 兼容

意向锁都是相互兼容的,因为意向锁表示的是 下一层 在请求什么类型的锁

  • 假如此时有 事物tx3 需要在 记录A 上进行加 S锁

    1. 在该记录所在的 数据库 上加一把 意向锁IS
    2. 在该记录所在的 上加一把 意向锁IS
    3. 在该记录所在的 上加一把 意向锁IS
    4. 发现该记录被锁定( tx1的X锁 ),那么 tx3需要等待 ,直到 tx1 进行commit

1.5. 意向锁的作用

  • 意向锁 是为了实现 多粒度的锁 ,表示在数据库中不但能实现 行级别的锁 ,还可以实现 页级别的锁表级别的锁 以及 库级别的锁

  • 如果没有意向锁,当你去锁一张表的时候,你就需要对表下的所有记录都进行加锁操作,且对其他事物刚刚插入的记录(游标已经扫过的范围)就没法在上面加锁了,此时就没有实现锁表的功能。

上述锁的操作都是在 内存 中,不会放在数据库中。且大部分加的都是意向锁,都是兼容的。 释放操作则是从记录锁开始 从下往上 进行释放

  • InnoDB 没有数据库级别的锁,也没有 页级别的锁(InnoDB只能在 记录 上加锁),所以InnoDB的 意向锁 只能加在 上,即InnoDB存储引擎中意向锁都是 表锁

1.6. 加锁以及查看

1.6.1 加锁测试

  • 终端1
mysql root@localhost:mytest> show create table c;
+-------+--------------------------------------+
| Table | Create Table                         |
+-------+--------------------------------------+
| c     | CREATE TABLE `c` (                   |
|       |   `id` int(11) NOT NULL,             |
|       |   `num` int(4) DEFAULT NULL,         |
|       |   PRIMARY KEY (`id`)                 |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.009s

mysql root@localhost:mytest> select * from c;
+----+-----+
| id | num |
+----+-----+
| 0  | 10  |
| 2  | 20  |
| 3  | 20  |
+----+-----+
3 rows in set
Time: 0.010s

mysql root@localhost:mytest> begin; -- 开始一个事物
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from c where id=2 for update;  -- 加上排他锁
+----+-----+
| id | num |
+----+-----+
| 2  | 20  |
+----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON    |   --默认是OFF;配置为ON,输出锁状态
+----------------------------+-------+
1 row in set
Time: 0.012s

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                              |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                     |
|        |      | =====================================                                                                                               |
|        |      | 2018-01-22 14:31:15 0x7f254c5af700 INNODB MONITOR OUTPUT                                                                            |
|        |      | =====================================                                                                                               |
----------省略其他输出---------

|        |      | MySQL thread id 294, OS thread handle 139797830776576, query id 1174080 localhost root                                              |
|        |      | TABLE LOCK table `mytest`.`c` trx id 1887424 lock mode IX                                                                           |  -- 在 `mytest`.`c`表上加上了意向锁IX(TABLE LOCK)
|        |      | RECORD LOCKS space id 3469 page no 3 n bits 72 index PRIMARY of table `mytest`.`c` trx id 1887424 lock_mode X locks rec but not gap |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                     |
|        |      |  0: len 4; hex 80000002; asc     ;;                                                                                                 |
|        |      |  1: len 6; hex 0000001cccb4; asc       ;;                                                                                           |
|        |      |  2: len 7; hex a500000042011d; asc     B  ;;                                                                                        |
|        |      |  3: len 4; hex 80000014; asc     ;;                                                                                                 |
|        |      |                                                                                                                                     |
|        |      | --------                                                                                                                            |
|        |      | FILE I/O                                                                                                                            |
|        |      | --------                                                                                                                            |

----------省略其他输出---------

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
-- Record lock : 表示是锁住的记录
-- heap no 3 PHYSICAL RECORD: n_fields 4: 表示锁住记录的heap no 为3的物理记录,由4个列组成
-- compact format : 表示这条记录存储的格式(Dynamic其实也是compact的格式)
-- info bits : 0 -- 表示这条记录没有被删除; 非0 -- 表示被修改或者被删除(32)

输出上述信息的前提是 innodb_status_output_locks = 1,可在配置文件中设置打开,不会影响运行时的性能,只有在show engine innodb status时才会使用

  • 终端1
mysql root@localhost:mytest> commit  -- 提交事物, 提交后就释放了锁,同时在终端2上就看不到锁的信息了
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>

注意如果直接select * from t5 where a=2 for update;终端2是看不到的,因为mysql默认自动提交事物

1.6.2 INNODB_TRX

  • 终端1
mysql root@localhost:mytest> begin; -- 开始一个事物
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from c where id=2 for update;  -- 加上排他锁
+----+-----+
| id | num |
+----+-----+
| 2  | 20  |
+----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> use information_schema;
You are now connected to database "information_schema" as user "gcdb"
Time: 0.003s

mysql gcdb@localhost:information_schema>  select * from INNODB_TRX\G
***************************[ 1. row ]***************************
trx_id                     | 1887425
trx_state                  | RUNNING
trx_started                | 2018-01-22 14:47:50
trx_requested_lock_id      | <null>
trx_wait_started           | <null>
trx_weight                 | 2
trx_mysql_thread_id        | 294
trx_query                  | <null>  -- 事物运行的SQL语句,这里是NULL,因为他是指当前运行的SQL语句,如果运行完了,就看不到了
trx_operation_state        | <null>
trx_tables_in_use          | 0
trx_tables_locked          | 1
trx_lock_structs           | 2
trx_lock_memory_bytes      | 1136
trx_rows_locked            | 1
trx_rows_modified          | 0
trx_concurrency_tickets    | 0
trx_isolation_level        | REPEATABLE READ  -- 当前数据库隔离级别是可重复读
trx_unique_checks          | 1
trx_foreign_key_checks     | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched  | 0
trx_adaptive_hash_timeout  | 0
trx_is_read_only           | 0
trx_autocommit_non_locking | 0
1 row in set
Time: 0.012s
mysql gcdb@localhost:information_schema>
  • INNODB_TRX 表
Column name Description
trx_id 事务ID
trx_state 事务状态:
trx_started 事务开始时间;
trx_requested_lock_id 事务等待的锁的ID(如果事务状态不是LOCK WAIT,这个字段是NULL),详细的锁的信息可以连查INNODB_LOCKS表
trx_wait_started 事务开始等待的时间
trx_weight 事务的权重,反映了一个事务修改和锁住的行数。当发生死锁回滚的时候,优先选择该值最小的进行回滚
trx_mysql_thread_id 事务线程ID
trx_query 事务执行的语句
trx_operation_state 事务当操作的类型,如果有的话 否则为NULL
trx_tables_in_use 事务中有多少个表被使用
trx_tables_locked 事务拥有多少个锁,当前事物有行锁在上面( 因为那些是行锁,不是表锁,表仍旧可以读取和写入通过多个事务,尽管一些记录被锁定)
trx_lock_structs 事务保留的锁的数量
trx_lock_memory_bytes 事务锁住的内存大小(B)
trx_rows_locked 事务锁住的行数
trx_rows_modified 事务更改的行数
trx_concurrency_tickets 事务并发票数
trx_isolation_level 事务隔离级别
trx_unique_checks 是否唯一性检查
trx_foreign_key_checks 外键检测 是否开启
trx_last_foreign_key_error 最后的外键错误
trx_adaptive_hash_latched 自适应散列索引是否被当前事务锁定
trx_adaptive_hash_timeout 是否立即放弃自适应散列索引的搜索latched,或者保留来自MySQL的调用。

1.6.3 INNODB_LOCKS和INNODB_LOCK_WAITS

  • 终端2
--下面两个表是要一起对比查看才有意义的,当前没有两个事物(仅终端1中一个事物)进行相互阻塞和等待,所以目前两个表是空的
mysql gcdb@localhost:information_schema>  select * from INNODB_LOCKS;
+---------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
0 rows in set
Time: 0.010s

mysql gcdb@localhost:information_schema>  select * from `INNODB_LOCK_WAITS`;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
0 rows in set
Time: 0.011s
mysql gcdb@localhost:information_schema>

  • 终端3
mysql root@localhost:sys> use mytest;
You are now connected to database "mytest" as user "root"
Time: 0.003s
mysql root@localhost:mytest> set  innodb_lock_wait_timeout=60;  --  设置锁等待时间为60秒,方便看到锁的信息,线上根据实际情况自行修改
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from c where id=2 lock in share mode; -- 这里如果超时时间设置短,可能来不及看
-- 可通过innodb_lock_wait_timeout进行设置
-- 此时hang住在这里,进行等待,因为终端1中 for update 还没有commit
  • 终端2
mysql gcdb@localhost:information_schema>  select * from INNODB_LOCKS \G;
***************************[ 1. row ]***************************
lock_id     | 421284540508896:3469:3:3  -- 锁ID
lock_trx_id | 421284540508896 --事物ID
lock_mode   | S			--显示为S锁
lock_type   | RECORD
lock_table  | `mytest`.`c`
lock_index  | PRIMARY
lock_space  | 3469
lock_page   | 3
lock_rec    | 3
lock_data   | 2
***************************[ 2. row ]***************************
lock_id     | 1887425:3469:3:3
lock_trx_id | 1887425   --事物ID
lock_mode   | X   		--显示为X锁
lock_type   | RECORD
lock_table  | `mytest`.`c`
lock_index  | PRIMARY
lock_space  | 3469
lock_page   | 3
lock_rec    | 3
lock_data   | 2
2 rows in set
Time: 0.012s

mysql gcdb@localhost:information_schema>  select * from `INNODB_LOCK_WAITS`\G;
***************************[ 1. row ]***************************
requesting_trx_id | 421284540508896  			-- 请求的事物ID (S锁),终端3
requested_lock_id | 421284540508896:3469:3:3   	-- 由txid,space,page_no,heap_no组成
blocking_trx_id   | 1887425     				-- 阻塞上面请求的事物ID(X锁),终端1
blocking_lock_id  | 1887425:3469:3:3
1 row in set
Time: 0.011s
mysql gcdb@localhost:information_schema>

--mysql5.7查看锁信息
--继续执行 终端1/终端3 内的SQL语句,使其中有一个线程发生阻塞

mysql gcdb@localhost:information_schema> use sys;
You are now connected to database "sys" as user "gcdb"
mysql gcdb@localhost:sys>  select * from innodb_lock_waits\G;
***************************[ 1. row ]***************************
wait_started                 | 2018-01-22 15:50:43 	-- 开始的时间
wait_age                     | 0:00:26   	-- 等待的时间
wait_age_secs                | 26			-- 等待秒数
locked_table                 | `mytest`.`c`	-- 锁住的表(意向锁)
locked_index                 | PRIMARY		-- 锁住的是系统生成的聚集索引
locked_type                  | RECORD		-- 锁的类型,记录锁
waiting_trx_id               | 421284540508896
waiting_trx_started          | 2018-01-22 15:50:43
waiting_trx_age              | 0:00:26
waiting_trx_rows_locked      | 1
waiting_trx_rows_modified    | 0
waiting_pid                  | 302
waiting_query                | select * from c where id=2 lock in share mode    -- 等待语句
waiting_lock_id              | 421284540508896:3469:3:3							-- 事物ID:space:page_No:heap_no
waiting_lock_mode            | S												-- 等待(请求)的锁的类型
blocking_trx_id              | 1887425
blocking_pid                 | 294
blocking_query               | <null>
blocking_lock_id             | 1887425:3469:3:3
blocking_lock_mode           | X												-- 阻塞的锁的类型
blocking_trx_started         | 2018-01-22 14:47:50
blocking_trx_age             | 1:03:19
blocking_trx_rows_locked     | 1
blocking_trx_rows_modified   | 0
sql_kill_blocking_query      | KILL QUERY 294									-- 解决建议
sql_kill_blocking_connection | KILL 294
1 row in set
Time: 0.014s
mysql gcdb@localhost:sys>

MySQL 5.6 通过导入sys库,也可以支持该视图

都是 锁在索引 上的,无论是主键还是二级索引,通过 locked_index 进行查看

当超过 innodb_lock_wait_timeout 设置的阈值,等待(请求)的事物就会报锁超时。在某些业务场景下,锁超时是无法避免的。

  • INNODB_LOCKS表

    Column name Description
    lock_id 锁ID
    lock_trx_id 事务ID, 可以连INNODB_TRX表查事务详情
    lock_mode 锁的模式: S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC
    lock_type 锁的类型,行级锁 或者表级锁
    lock_table 加锁的表
    lock_index 如果是lock_type='RECORD' 行级锁 ,为锁住的索引,如果是表锁为null
    lock_space 如果是lock_type='RECORD' 行级锁 ,为锁住对象的Tablespace ID,如果是表锁为null
    lock_page 如果是lock_type='RECORD' 行级锁 ,为锁住页号,如果是表锁为null
    lock_rec 如果是lock_type='RECORD' 行级锁 ,为锁住页号,如果是表锁为null
    lock_data 事务锁住的主键值,若是表锁,则该值为null
  • INNODB_LOCK_WAITS表

    Column name Description
    requesting_trx_id 申请锁资源的事务ID
    requesting_lock_id 申请的锁的ID
    blocking_trx_id 租塞的事务ID
    blocking_lock_id 租塞的锁的ID

二. 锁与并发

  • locking (锁)
  • concurrency control (并发控制)
  • isolation (隔离级别)
  • serializability (序列化)

以上四个其实在数据库中讲的是同一个概念; 是用来实现 并发控制并发控制 用来实现 隔离级别隔离级别 是通过 来控制的, 的目的为了使得事物之间的执行是 序列化

2.1. 事物隔离级别

  • 1.READ UNCOMMITED

  • 2.READ COMMITTED

    • Oracle、DB2、Microsoft SQL Server (默认)
    • 解决 脏读 (ANSI SQL)
  • 3.REPEATABLE READ

    • InnoDB(默认)
    • 解决 脏读 、不可重复读 (ANSI SQL)
    • InnoDB中的RR解决了幻读问题 (实现了事物的隔离级别)
  • 4.SERIALIZABLE

    • 解决 脏读 、 不可重复读 和 幻读 (ANSI SQL)

2.1.1 隔离性

  • 什么是隔离性?
    • 一个事物 所做的 修改 ,对 其他事物不可见 的,好似是 串行 执行的

隔离级别越低 ,事物请求的锁越少 或者保持锁的时间就越短

  • 终端1
mysql root@localhost:mytest> create table t_rc (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.009s
mysql root@localhost:mytest>

mysql root@localhost:mytest> begin --开始一个事物
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_rc values(1,2,3),(2,3,4); --插入但是未提交
Query OK, 2 rows affected
Time: 0.002s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:sys> use mytest;
You are now connected to database "mytest" as user "gcdb"
Time: 0.002s
mysql gcdb@localhost:mytest> select * from t_rc;  --没有查询到数据
+---+---+---+
| a | b | c |
+---+---+---+
0 rows in set
Time: 0.009s

mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t_rc values(3,4,5);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> commit;   --终端2,事物提交
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select * from t_rc;  --终端2,事物提交后查询到只有一条
+---+---+---+
| a | b | c |
+---+---+---+
| 3 | 4 | 5 |
+---+---+---+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>

  • 终端1
--终端1 直接查询可以看到insert数据
mysql root@localhost:mytest> select * from t_rc;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |
+---+---+---+
3 rows in set
Time: 0.010s

mysql root@localhost:mytest> commit;   --终端1,提交之后,终端2才能看到
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> select * from t_rc; --终端1未提交,只能查询自己的
+---+---+---+
| a | b | c |
+---+---+---+
| 3 | 4 | 5 |
+---+---+---+
1 row in set
Time: 0.010s

mysql gcdb@localhost:mytest> select * from t_rc;  --终端1提交之后,再终端2可以查询到所以数据
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
| 3 | 4 | 5 |   
+---+---+---+
3 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest>
--符合事物隔离性

mysql gcdb@localhost:mytest> select @@tx_isolation;  -- 查看当前事物的隔离级别
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ | --mysql默认隔离级别
+-----------------+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>

注意:线上环境一般使用 READ-COMMITTED

2.1.2 脏读

  • 终端1
mysql root@localhost:mytest> create table t_ruc (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> set tx_isolation="read-uncommitted";
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_ruc values(10,20,30),(20,30,40);
Query OK, 2 rows affected
Time: 0.002s

--事物未提交

  • 终端2
mysql gcdb@localhost:mytest> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select * from t_ruc;
+----+----+----+
| a  | b  | c  |
+----+----+----+
| 10 | 20 | 30 |
| 20 | 30 | 40 |        --终端2里面,事物2能看到事物1中插入的数据,但是事物1还未提交
+----+----+----+
2 rows in set
Time: 0.010s

mysql gcdb@localhost:mytest> insert into t_ruc values(30,40,50); --事物2也插入一行数据
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>

  • 终端1
mysql root@localhost:mytest> select * from t_ruc;
+----+----+----+
| a  | b  | c  |
+----+----+----+
| 10 | 20 | 30 |
| 20 | 30 | 40 |
| 30 | 40 | 50 |   --终端2中的事物2还没有commit,在会话1中的事物1就能读取到数据,这就是脏读
+----+----+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>

2.1.3 不可重复读

  • 终端1
mysql root@localhost:mytest> set tx_isolation='read-committed';
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> create table t_rco (a int, b int, c int);
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> insert into t_rco values(100,200,300);
Query OK, 1 row affected
Time: 0.002s

mysql root@localhost:mytest> commit; -- 此时提交事物1,就可以在t_rco表中查询到数据
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_rco;  --查询到数据
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 100 | 200 | 300 |
+-----+-----+-----+
1 row in set
Time: 0.010s
  • 终端2
mysql gcdb@localhost:mytest>  set tx_isolation='read-committed';
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s

mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> update t_rco set b=222 and c=111 where a=100;  
Query OK, 1 row affected
Time: 0.001s

--终端2的insert事物未提交
  • 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 100 | 200 | 300 |   --终端1,没有查询到之前终端2的insert事物,只有一行数据,同时也说明RC隔离级别解决了脏读问题
+-----+-----+-----+
1 row in set
  • 终端2
mysql gcdb@localhost:mytest> rollback;  -- 回滚事务
Query OK, 0 rows affected
Time: 0.007s

mysql gcdb@localhost:mytest> begin;     --从新开启一个事务
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> update t_rco set b=222, c=111 where a=100; --更新记录
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> commit;  --提交事物
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest>

  • 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 100 | 222 | 111 |         --查询到终端2的update事务所更新的记录
+-----+-----+-----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

2.1.4 幻读

  • 终端1
mysql root@localhost:mytest> begin;     --开启新事务
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 100 | 222 | 111 |
+-----+-----+-----+
1 row in set
Time: 0.010s

--事务未提交

  • 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t_rco values(400,400,400);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> commit;  -- 终端2中的事物已提交
Query OK, 0 rows affected
Time: 0.001s
  • 终端1
mysql root@localhost:mytest> select * from t_rco;
+-----+-----+-----+
| a   | b   | c   |
+-----+-----+-----+
| 100 | 222 | 111 |
| 400 | 400 | 400 |   -- 终端1中的事物没提交,就看到了终端2中的事物提交的结果
+-----+-----+-----+
2 rows in set
Time: 0.010s
  • 不可重复读:在一个事物中,针对同一条记录,执行两次相同的SQL得到的结果不一样
  • 幻读:在一个事物中,执行两次相同的SQL,得到了不同的结果集(新增了部分记录或者缺失了部分记录)(不是同一条记

三. 锁的算法

  • Record Lock (行锁)
    • 行锁锁定单条索引记录,锁住的是索引,而非记录本身,即使没有索引定义表, InnoDB创建一个隐藏的聚集索引,并使用此索引进行记录锁定.
  • Gap Lock (间隙锁)
    • 间隙锁锁定是索引记录之间间隙的锁定,也可以是最后一个索引记录之前之后的间隙的锁定,并不包括该索引记录本身
      对于使用唯一索引锁定搜索唯一行的语句,不需要使用间隙锁定。(这不包括搜索条件仅包含多列唯一索引中的某些列的情况;在这种情况下,会发生间隙锁定)
  • Next-Key Lock
    • 索引记录上的记录锁和索引记录之前的间隙上的间隙锁组合,锁定一个范围,并且锁定记录本身

3.1. Record Lock

表t_nums,A列有记录10,20,30,40,50(A列为主键

A
10
20
30
40
50
  • 假如t_nums表A列有记录10,20,30,40,50(只有这三个记录),且在记录30上加锁

    • 对于在记录30上加锁,表示该记录上加了行锁
    • [30, 30] :只锁定记录30本身
  • 终端1

mysql root@localhost:mytest> create table t_nums (a int primary key);
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> insert into t_nums values (10),(20),(30),(40),(50);
Query OK, 5 rows affected
Time: 0.002s

mysql root@localhost:mytest> select * from t_nums;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s

  • 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                   |
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                          |
|        |      | =====================================                                                                                                    |
|        |      | 2018-01-23 13:35:45 0x7f255412c700 INNODB MONITOR OUTPUT                                                                                 |
|        |      | =====================================                                                                                                    |
|        |      | Per second averages calculated from the last 3 seconds                                                                                   |
----------省略其他输出---------
|        |      | ------------                                                                                                                             |
|        |      | TRANSACTIONS                                                                                                                             |
|        |      | ------------                                                                                                                             |
|        |      | Trx id counter 1887535                                                                                                                   |
|        |      | Purge done for trx's no < 1887534 undo n:o < 0 state: running but idle                                                                  |
|        |      | History list length 51                                                                                                                   |
|        |      | LIST OF TRANSACTIONS FOR EACH SESSION:                                                                                                   |
|        |      | ---TRANSACTION 421284540508896, not started                                                                                              |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                          |
|        |      | ---TRANSACTION 1887534, ACTIVE 4888 sec                                                                                                  |
|        |      | 2 lock struct(s), heap size 1136, 1 row lock(s)                                                                                          |
|        |      | MySQL thread id 309, OS thread handle 139798173144832, query id 1174341 localhost root                                                   |
|        |      | TABLE LOCK table `mytest`.`t_nums` trx id 1887534 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3474 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_nums` trx id 1887534 lock_mode X locks rec but not gap |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                          |
|        |      |  0: len 4; hex 8000001e; asc     ;;                                                                                                      | --hex 8000001e; 1e表示十六进制30
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                                                |
|        |      |  2: len 7; hex d800000034012a; asc     4 *;;                                                                                             |

3.2. Gap Lock

  • 假如t_nums表A列有记录10,20,30,40,50(只有这三个记录),且在记录30上加锁

    • 对于在记录30上加锁,表示在记录10记录30 之间加锁(30的前面个记录10,20)
    • (10, 30) :锁定该范围,不包含两个边界(不能插入10、30等在这个范围内的数据,但是可以对10和30做删除或者修改)
    • Gap Lock解决了幻读问题
  • 终端1

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_nums where a<=30 for update; --记录30前面加锁
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>


  • 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                   |
+--------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                          |
|        |      | =====================================                                                                                                    |
|        |      | 2018-01-23 13:49:20 0x7f255412c700 INNODB MONITOR OUTPUT                                                                                 |
|        |      | =====================================                                                                                                    |
----------省略其他输出---------

|        |      | MySQL thread id 309, OS thread handle 139798173144832, query id 1174346 localhost root                                                   |
|        |      | TABLE LOCK table `mytest`.`t_nums` trx id 1887535 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3474 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_nums` trx id 1887535 lock_mode X locks rec but not gap |
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                          |
|        |      |  0: len 4; hex 8000000a; asc     ;;                                                                                                      |  --hex 8000000a; a表示十六进制10
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                                                |
|        |      |  2: len 7; hex d8000000340110; asc     4  ;;                                                                                             |
|        |      |                                                                                                                                          |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                          |
|        |      |  0: len 4; hex 80000014; asc     ;;                                                                                                      |  --hex 80000014; 14表示十六进制20
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                                                |
|        |      |  2: len 7; hex d800000034011d; asc     4  ;;                                                                                             |
|        |      |                                                                                                                                          |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                          |
|        |      |  0: len 4; hex 8000001e; asc     ;;                                                                                                      |
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                                                |   --hex 8000001e; 1e表示十六进制30
|        |      |  2: len 7; hex d800000034012a; asc     4 *;;                                                                                             |
|        |      |                                                                                                                                          |
|        |      | --------                                                                                                                                 |

--锁定了三行记录(10.20.30)

3.3. Next-Key Lock

  • 假如t_nums表A列有记录10,20,30,40,50(只有这三个记录)),且在记录30上加锁

    • 对于在记录30上加锁,表示在记录10 记录30 范围加锁(Gap Lock)的同时,同时在记录30上也行锁
    • (10, 30] :锁定该范围,且包含记录30本身(不能插入11、18等在这个范围的数据,同时对记录30不能删除或修改,记录10可以删除或修改)
  • 注意:锁住的是索引

  • 终端1

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest> select * from t_nums where a<=30 for update;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                             |
+--------+------+--------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                    |
|        |      | =====================================                                                                              |
|        |      | 2018-01-23 15:33:53 0x7f255412c700 INNODB MONITOR OUTPUT                                                           |
|        |      | =====================================                                                                              |
|        |      | Per second averages calculated from the last 7 seconds                                                             |
----------省略其他输出---------

|        |      | MySQL thread id 317, OS thread handle 139797762930432, query id 1174397 localhost root                             |
|        |      | TABLE LOCK table `mytest`.`t_nums` trx id 1887557 lock mode IX                                                     |
|        |      | RECORD LOCKS space id 3474 page no 3 n bits 80 index PRIMARY of table `mytest`.`t_nums` trx id 1887557 lock_mode X |  --主键上加X锁, 表示的是Next Key lock
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                    |
|        |      |  0: len 4; hex 8000000a; asc     ;;                                                                                |  --hex 8000000a; a表示十六进制10
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                          |
|        |      |  2: len 7; hex d8000000340110; asc     4  ;;                                                                       |
|        |      |                                                                                                                    |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                    |
|        |      |  0: len 4; hex 80000014; asc     ;;                                                                                |  --hex 80000014; a表示十六进制20
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                          |
|        |      |  2: len 7; hex d800000034011d; asc     4  ;;                                                                       |
|        |      |                                                                                                                    |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                    |
|        |      |  0: len 4; hex 8000001e; asc     ;;                                                                                |  --hex 8000001e; 1e表示十六进制30
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                          |
|        |      |  2: len 7; hex d800000034012a; asc     4 *;;                                                                       |
|        |      |                                                                                                                    |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                    |  
|        |      |  0: len 4; hex 80000028; asc    (;;                                                                                |  --hex 80000028; 28表示十六进制40
|        |      |  1: len 6; hex 0000001ccd29; asc      );;                                                                          |
|        |      |  2: len 7; hex d8000000340137; asc     4 7;;                                                                       |
|        |      |                                                                                                                    |

3.4 隔离级别和锁

3.4.1 REPEATABLE-READ级别

  • 终端1
mysql root@localhost:mytest> create table t_rc (a int primary key,b int);
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> show create table t_rc;
+-------+--------------------------------------+
| Table | Create Table                         |
+-------+--------------------------------------+
| t_rc  | CREATE TABLE `t_rc` (                |
|       |   `a` int(11) NOT NULL,              |
|       |   `b` int(11) DEFAULT NULL,          |
|       |   PRIMARY KEY (`a`)                  |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> insert into t_rc(a,b) values(1,10),(2,20),(5,50),(10,100);
Query OK, 4 rows affected
Time: 0.002s

mysql root@localhost:mytest> select * from t_rc;
+----+-----+
| a  | b   |
+----+-----+
| 1  | 10  |
| 2  | 20  |
| 5  | 50  |
| 10 | 100 |
+----+-----+
4 rows in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> delete from t_rc where a<6;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 3 rows affected
Time: 0.001s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t_rc values(4,20);

--等待插入 

  • 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                         |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                                |
|        |      | =====================================                                                                                                                          |
|        |      | 2018-01-25 08:51:37 0x7f2554069700 INNODB MONITOR OUTPUT                                                                                                       |
|        |      | =====================================                                                                                                                          |
----------省略其他输出---------

|        |      | mysql tables in use 1, locked 1                                                                                                                                |
|        |      | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)                                                                                                      |
|        |      | MySQL thread id 8335, OS thread handle 139797762664192, query id 1198737 localhost gcdb update                                                                 |
|        |      | insert into t_rc values(4,20)                                                                                                                                  | --insert语句
|        |      | ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:                                                                                                | --事务锁等待4s
|        |      | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887637 lock_mode X locks gap before rec insert intention waiting | --插入意向锁(记录锁)
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                                               |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                            |
|        |      |  1: len 6; hex 0000001ccd90; asc       ;;                                                                                                                      | --这个行锁锁定5
|        |      |  2: len 7; hex 3c0000003b0460; asc <   ; `;;                                                                                                                   |
|        |      |  3: len 4; hex 80000032; asc    2;;                                                                                                                            |
|        |      |                                                                                                                                                                |
|        |      | ------------------                                                                                                                                             |
|        |      | TABLE LOCK table `mytest`.`t_rc` trx id 1887637 lock mode IX                                                                                                   |
|        |      | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887637 lock_mode X locks gap before rec insert intention waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                                               |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                            | --这个行锁锁定5
|        |      |  1: len 6; hex 0000001ccd90; asc       ;;                                                                                                                      |
|        |      |  2: len 7; hex 3c0000003b0460; asc <   ; `;;                                                                                                                   |
|        |      |  3: len 4; hex 80000032; asc    2;;                                                                                                                            |
|        |      |                                                                                                                                                                |
|        |      | ---TRANSACTION 1887632, ACTIVE 26 sec                                                                                                                          |
|        |      | 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3                                                                                            |
|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1198727 localhost root                                                                        |
|        |      | TABLE LOCK table `mytest`.`t_rc` trx id 1887632 lock mode IX                                                                                                   | --在给主键行上加X锁之前,先要在表上加意向锁IX  
|        |      | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887632 lock_mode X                                               |
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                                               |
|        |      |  0: len 4; hex 80000001; asc     ;;                                                                                                                            | --这个行锁锁定1
|        |      |  1: len 6; hex 0000001ccd90; asc       ;;                                                                                                                      |
|        |      |  2: len 7; hex 3c0000003b041a; asc <   ;  ;;                                                                                                                   |
|        |      |  3: len 4; hex 8000000a; asc     ;;                                                                                                                            |
|        |      |                                                                                                                                                                |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                                               |
|        |      |  0: len 4; hex 80000002; asc     ;;                                                                                                                            | --这个行锁锁定2
|        |      |  1: len 6; hex 0000001ccd90; asc       ;;                                                                                                                      |
|        |      |  2: len 7; hex 3c0000003b043d; asc <   ; =;;                                                                                                                   |
|        |      |  3: len 4; hex 80000014; asc     ;;                                                                                                                            |
|        |      |                                                                                                                                                                |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                                               |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                            | --这个行锁锁定5
|        |      |  1: len 6; hex 0000001ccd90; asc       ;;                                                                                                                      |
|        |      |  2: len 7; hex 3c0000003b0460; asc <   ; `;;                                                                                                                   |
|        |      |  3: len 4; hex 80000032; asc    2;;                                                                                                                            |
|        |      |                                                                                                                                                                |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                                                |
|        |      |  0: len 4; hex 8000000a; asc     ;;                                                                                                                            | --这个行锁锁定10
|        |      |  1: len 6; hex 0000001ccd58; asc      X;;                                                                                                                      |
|        |      |  2: len 7; hex f6000006c40137; asc       7;;                                                                                                                   |
|        |      |  3: len 4; hex 80000064; asc    d;;                                                                                                                            |
|        |      |                                                                                                                                                                |

即在 REPEATABLE-READ 的隔离级别下,锁住的是锁住(-∞,1],(1,2],(2,5],(5,10]是因为在REPEATABLE-READ 级别下,插入(4,20)将从表(B+树)的第一个记录(1)开始比对,一直比对等于(4)为止。

  • 总结:
      1. REPEATABLE-READ级别下的锁默认为 Next-Key Lock
      1. REPEATABLE-READ会对游标打开的所有的记录进行加锁

3.4.2 READ-COMMITTED级别

  • 终端1
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest>  set tx_isolation="READ-COMMITTED";
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_rc where a<6 for update;   -- a为是主键,且返回的记录只有一条
+---+----+
| a | b  |
+---+----+
| 1 | 10 |
| 2 | 20 |
| 5 | 50 |
+---+----+
3 rows in set
Time: 0.011s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> set tx_isolation="READ-COMMITTED";
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.011s

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                 |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                        |
|        |      | =====================================                                                                                                  |
|        |      | 2018-01-25 11:22:22 0x7f2533fbe700 INNODB MONITOR OUTPUT                                                                               |
|        |      | =====================================                                                                                                  |
|----------省略其他输出---------

|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1201460 localhost root                                                |
|        |      | TABLE LOCK table `mytest`.`t_rc` trx id 1887640 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887640 lock_mode X locks rec but not gap | --记录加x锁但是没有gap锁(记录锁)
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                        |
|        |      |  0: len 4; hex 80000001; asc     ;;                                                                                                    |
|        |      |  1: len 6; hex 0000001ccd58; asc      X;;                                                                                              |
|        |      |  2: len 7; hex f6000006c40110; asc        ;;                                                                                           |
|        |      |  3: len 4; hex 8000000a; asc     ;;                                                                                                    |
|        |      |                                                                                                                                        |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                        |
|        |      |  0: len 4; hex 80000002; asc     ;;                                                                                                    |
|        |      |  1: len 6; hex 0000001ccd58; asc      X;;                                                                                              |
|        |      |  2: len 7; hex f6000006c4011d; asc        ;;                                                                                           |
|        |      |  3: len 4; hex 80000014; asc     ;;                                                                                                    |
|        |      |                                                                                                                                        |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                        |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                    |
|        |      |  1: len 6; hex 0000001ccd58; asc      X;;                                                                                              |
|        |      |  2: len 7; hex f6000006c4012a; asc       *;;                                                                                           |
|        |      |  3: len 4; hex 80000032; asc    2;;                                                                                                    |
|        |      |                                                                                                                                        |

即在 READ-COMMITTED 的隔离级别下,锁住的是 1,2,5 记录本身(lock_mode X locks rec but not gap)

在大部分情况下,READ-COMMITTED隔离级别下没有GAP锁,但是一些场景下,线上仍可能出现;
设置了READ-COMMITTED隔离级别后, binlog_format = row ,否则会出现主从不一致的情况.

3.4.3 Next-Key lock优化为Record lock

  • REPEATABLE-READ隔离级别

  • 终端1

mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest>  set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_rc where a=5 for update;
+---+----+
| a | b  |
+---+----+
| 5 | 50 |
+---+----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> set tx_isolation="REPEATABLE-READ";
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest>  select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set
Time: 0.009s

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                 |
+--------+------+----------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                        |
|        |      | =====================================                                                                                                  |
|        |      | 2018-01-25 11:32:52 0x7f2533fbe700 INNODB MONITOR OUTPUT                                                                               |
|        |      | =====================================                                                                                                  |
|----------省略其他输出---------

|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1201660 localhost root                                                |
|        |      | TABLE LOCK table `mytest`.`t_rc` trx id 1887641 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3475 page no 3 n bits 72 index PRIMARY of table `mytest`.`t_rc` trx id 1887641 lock_mode X locks rec but not gap |  ---记录加x锁但是没有gap锁(记录锁)
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                        |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                    |
|        |      |  1: len 6; hex 0000001ccd58; asc      X;;                                                                                              |
|        |      |  2: len 7; hex f6000006c4012a; asc       *;;                                                                                           |
|        |      |  3: len 4; hex 80000032; asc    2;;                                                                                                    |
|        |      |                                                                                                                                        |

降级为 Record Lock 是因为返回的记录具有 唯一性 ,不会存在幻读问题;
当唯一索引是复合索引时,且查询条件只 包含部分列 的话,其实还是有Gap lock

3.4.4 非唯一索引的等值查询

3.4.4.1 REPEATABLE-READ隔离级别

  • 终端1
mysql root@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> create table t_unidx (a int not null);
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest>  insert into t_unidx values(1),(2),(5),(10);
Query OK, 4 rows affected
Time: 0.002s

mysql root@localhost:mytest>  begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from t_unidx where a=5 for update;  -- 与a为非唯一索引
+---+
| a |
+---+
| 5 |
+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                      |
+--------+------+-----------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                             |
|        |      | =====================================                                                                                       |
|        |      | 2018-01-25 11:58:08 0x7f2533fbe700 INNODB MONITOR OUTPUT                                                                    |
|        |      | =====================================                                                                                       |
|----------省略其他输出---------

|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1202131 localhost root                                     |
|        |      | TABLE LOCK table `mytest`.`t_unidx` trx id 1887655 lock mode IX                                                             |
|        |      | RECORD LOCKS space id 3477 page no 3 n bits 72 index GEN_CLUST_INDEX of table `mytest`.`t_unidx` trx id 1887655 lock_mode X |  --通过索引 GEN_CLUST_INDEX加锁
|        |      | Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0                                             |
|        |      |  0: len 8; hex 73757072656d756d; asc supremum;;                                                                             |
|        |      |                                                                                                                             |
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                             |
|        |      |  0: len 6; hex 000001a61b18; asc       ;;                                                                                   |
|        |      |  1: len 6; hex 0000001ccd9e; asc       ;;                                                                                   |
|        |      |  2: len 7; hex a7000000250110; asc     %  ;;                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                         |
|        |      |                                                                                                                             |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                             |
|        |      |  0: len 6; hex 000001a61b19; asc       ;;                                                                                   |
|        |      |  1: len 6; hex 0000001ccd9e; asc       ;;                                                                                   |
|        |      |  2: len 7; hex a700000025011f; asc     %  ;;                                                                                |
|        |      |  3: len 4; hex 80000002; asc     ;;                                                                                         |
|        |      |                                                                                                                             |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                             |
|        |      |  0: len 6; hex 000001a61b1a; asc       ;;                                                                                   |
|        |      |  1: len 6; hex 0000001ccd9e; asc       ;;                                                                                   |
|        |      |  2: len 7; hex a700000025012e; asc     % .;;                                                                                |
|        |      |  3: len 4; hex 80000005; asc     ;;                                                                                         |
|        |      |                                                                                                                             |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                             |
|        |      |  0: len 6; hex 000001a61b1b; asc       ;;                                                                                   |
|        |      |  1: len 6; hex 0000001ccd9e; asc       ;;                                                                                   |
|        |      |  2: len 7; hex a700000025013d; asc     % =;;                                                                                |
|        |      |  3: len 4; hex 8000000a; asc     ;;                                                                                         |
|        |      |                                                                                                                             |

t_unidx 根据mysql自己生产 GEN_CLUST_INDEX,当查询条件为 a=5 时, 游标 要访问 所有的记录(索引),来判断a列是否等于5;即需要通过GEN_CLUST_INDEX扫描所有主键,则会将 所有记录(索引)都锁住 。形成了表锁的效果。

总结:在 REPEATABLE-READ 的隔离级别下,当查询的 列没有索引 时,会 锁住所有记录

3.4.4.2 READ-COMMITTED隔离级别

  • 终端1
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> select * from d;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s

mysql root@localhost:mytest>  show create table d;
+-------+--------------------------------------+
| Table | Create Table                         |
+-------+--------------------------------------+
| d     | CREATE TABLE `d` (                   |
|       |   `a` int(11) DEFAULT NULL           |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from d where a=30 for update;
+----+
| a  |
+----+
| 30 |
+----+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.009s

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                      |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                             |
|        |      | =====================================                                                                                                       |
|        |      | 2018-01-25 16:11:56 0x7f254c776700 INNODB MONITOR OUTPUT                                                                                    |
|        |      | =====================================                                                                                                       |

----------省略其他输出---------

|        |      | ---TRANSACTION 1887776, ACTIVE 5 sec                                                                                                        |
|        |      | 2 lock struct(s), heap size 1136, 1 row lock(s)                                                                                             |
|        |      | MySQL thread id 10838, OS thread handle 139797830510336, query id 1206997 localhost root                                                    |
|        |      | TABLE LOCK table `mytest`.`d` trx id 1887776 lock mode IX                                                                                   |
|        |      | RECORD LOCKS space id 3476 page no 3 n bits 80 index GEN_CLUST_INDEX of table `mytest`.`d` trx id 1887776 lock_mode X locks rec but not gap |  -- 只有一个记录锁,且锁住的是GEN_CLUST_INDEX
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                             |
|        |      |  0: len 6; hex 000001a61b14; asc       ;;                                                                                                   |
|        |      |  1: len 6; hex 0000001ccd6e; asc      n;;                                                                                                   |
|        |      |  2: len 7; hex a800000047012e; asc     G .;;                                                                                                |
|        |      |  3: len 4; hex 8000001e; asc     ;;                                                                                                         |

当查询条件是非索引列等值查询REPEATABLE-READ隔离级别下会锁住每个记录(形成表锁的效果),而 READ-COMMITTED隔离级别下只锁住查询条件的记录本身

3.4.5 非等值查询 (假如REPEATABLE-READ级别没有Gap LOCK情况)

如果在 REPEATABLE-READ 级别下,且 没有Gap锁 ,则此时 tx1 锁住的是 1,3,5,7 (记录锁),并 标记为删除 ,同时 tx2 可以插入记录 6 。即此时表中的记录为6。
而在 Log 中记录的是 insert 6;del<=7; (先提交的在前面) ,如果此时有slave机器进行同步,该表中的记录为空。
此时, 主从 机器上的 数据不一致
有Gap Lock 后, tx2insert 6; 需要等待 tx1del<=7; 执行完成后才能执行,此时的Log为 del<=7;insert 6; ,也就不会有数据不一致的问题(符合隔离性要求)

3.4.6 二级索引列的等值查询

3.4.6.1 REPEATABLE-READ隔离级别

  • 终端1
mysql root@localhost:mytest>  create table e(a int, b int, primary key(a), key(b));
Query OK, 0 rows affected
Time: 0.009s

mysql root@localhost:mytest>  insert into e  values(1,1),(3,1),(5,3),(7,6),(10,8);
Query OK, 5 rows affected
Time: 0.002s

mysql root@localhost:mytest> select * from e;
+----+---+
| a  | b |
+----+---+
| 1  | 1 |
| 3  | 1 |
| 5  | 3 |
| 7  | 6 |
| 10 | 8 |
+----+---+
5 rows in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest>  select * from e where b=3 for update;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>


  • 终端2
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                              |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                     |
|        |      | =====================================                                                                                               |
|        |      | 2018-01-25 13:50:14 0x7f2533fbe700 INNODB MONITOR OUTPUT                                                                            |
|        |      | =====================================                                                                                               |
|----------省略其他输出---------

|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1204582 localhost root                                             |
|        |      | TABLE LOCK table `mytest`.`e` trx id 1887714 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X                             |  -- Next-Key lock (index b 二级索引)
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                     |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                 |  --这个行锁锁定a=3
|        |      |  1: len 4; hex 80000005; asc     ;;                                                                                                 |  --这个行锁锁定b=5
|        |      |                                                                                                                                     |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 72 index PRIMARY of table `mytest`.`e` trx id 1887714 lock_mode X locks rec but not gap |  -- Record Lock (index PRIMARY 聚集索引)
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                     |  --这个行锁锁定b=5
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                 |
|        |      |  1: len 6; hex 0000001ccddc; asc       ;;                                                                                           |
|        |      |  2: len 7; hex cc000006c7012a; asc       *;;                                                                                        |
|        |      |  3: len 4; hex 80000003; asc     ;;                                                                                                 |   --这个行锁锁定a=3
|        |      |                                                                                                                                     |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X locks gap before rec        |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                     |
|        |      |  0: len 4; hex 80000006; asc     ;;                                                                                                 |  --这个行锁锁定b=6
|        |      |  1: len 4; hex 80000007; asc     ;;                                                                                                 |  --这个行锁锁定a=7
|        |      |                                                                                                                                     |
|        |      | --------                                                                                                                            |

-- 所以二级索引锁住的范围是 (1, 3],(3, 6)
-- 主键索引只锁住了a=5的这条记录 [5, 5]
-- 如果不锁住(3,6)还是可以有别的事物插入b=3的记录,从而产生幻读

3.4.6.1.1 问题1
-- 以下SQL语句在终端2中执行的效果
select * from e where a = 5 lock in share mode; -- 不可执行,因为a=5上有一把记录锁
insert into e values(4, 2); -- 不可以执行,因为b=2在(1, 3]内
insert into e values(6, 5); -- 不可以执行,因为b=5在(3, 6)内
insert into e values(8, 6); -- 可以执行,(8, 6)均不在锁住的范围内
insert into e values(2, 0); -- 可以执行,(2, 0)均不在锁住的范围内
insert into e values(6, 7); -- 可以执行,(6, 7)均不在锁住的范围内
3.4.6.1.2 问题2
insert into e values(6, 6); -- 不可以执行,二级索引除了看key,还要看主键的值,-- (6, 6)在(7, 6)前面,在二级索引锁住的范围(3, 6)中 即(6 6)中的b=6插在(36)中间,而(8 6)中的b=6插在(36)

-- a : 1 3 5 7 10
-- b : 1 1 3 6 8
-- 其实二级索引锁住的不仅仅是二级索引的Key本身,还有对应的value,也就是主键
-- 「1:1」 「1:3」 「3:5」      「6:7」     「8:10」 -- 锁住的是(「3:5」, 「6:7」)
--                          ^           ^
--                          |           |
--                       「6:6」     「8:6」
--
-- b相同的情况下,a也是排序的;a也是二级索引的一部分
  • 终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest>  select * from e where b=3 for update;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> insert into e values(6, 6);

  • 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                       |
|        |      | =====================================                                                                                                                 |
|        |      | 2018-01-25 14:21:24 0x7f2554069700 INNODB MONITOR OUTPUT                                                                                              |
|        |      | =====================================                                                                                                                 |
----------省略其他输出---------

|        |      | mysql tables in use 1, locked 1                                                                                                                       |
|        |      | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1                                                                         |
|        |      | MySQL thread id 8335, OS thread handle 139797762664192, query id 1204839 localhost gcdb update                                                        |
|        |      | insert into e values(6, 6)                                                                                                                            |
|        |      | ------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:                                                                                       |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887716 lock_mode X locks gap before rec insert intention waiting | -- Next-Key lock (index b 二级索引
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                       | --这个行锁锁定b=6
|        |      |  0: len 4; hex 80000006; asc     ;;                                                                                                                   |
|        |      |  1: len 4; hex 80000007; asc     ;;                                                                                                                   |
|        |      |                                                                                                                                                       |
|        |      | ------------------                                                                                                                                    |
|        |      | TABLE LOCK table `mytest`.`e` trx id 1887716 lock mode IX                                                                                             |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887716 lock_mode X locks gap before rec insert intention waiting |  -- Record Lock (index PRIMARY 聚集索引)
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                       |  
|        |      |  0: len 4; hex 80000006; asc     ;;                                                                                                                   |  --这个行锁锁定a=6
|        |      |  1: len 4; hex 80000007; asc     ;;                                                                                                                   |
|        |      |                                                                                                                                                       |
|        |      | ---TRANSACTION 1887714, ACTIVE 847 sec                                                                                                                |
|        |      | 4 lock struct(s), heap size 1136, 3 row lock(s)                                                                                                       |
|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1204582 localhost root                                                               |
|        |      | TABLE LOCK table `mytest`.`e` trx id 1887714 lock mode IX                                                                                             |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X                                               |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                       |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                                   |
|        |      |  1: len 4; hex 80000005; asc     ;;                                                                                                                   |
|        |      |                                                                                                                                                       |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 72 index PRIMARY of table `mytest`.`e` trx id 1887714 lock_mode X locks rec but not gap                   |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                                       |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                   |
|        |      |  1: len 6; hex 0000001ccddc; asc       ;;                                                                                                             |
|        |      |  2: len 7; hex cc000006c7012a; asc       *;;                                                                                                          |
|        |      |  3: len 4; hex 80000003; asc     ;;                                                                                                                   |
|        |      |                                                                                                                                                       |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887714 lock_mode X locks gap before rec                          |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                       |
|        |      |  0: len 4; hex 80000006; asc     ;;                                                                                                                   |  
|        |      |  1: len 4; hex 80000007; asc     ;;                                                                                                                   |
|        |      |

二级索引上加了 Record LockNext-Key Lock ,则对应的主键Record Lock ;二级索引加 Gap Lock ,则对应的主键不加锁

3.4.6.2 READ-COMMITTED隔离级别

  • 终端1
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s

mysql root@localhost:mytest> select * from e where b=8 for update;
+----+---+
| a  | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.010s
mysql root@localhost:mytest>

  • 终端2
mysql root@localhost:mytest> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                              |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                     |
|        |      | =====================================                                                                                               |
|        |      | 2018-01-25 16:28:12 0x7f254c776700 INNODB MONITOR OUTPUT                                                                            |
|        |      | =====================================                                                                                               |

----------省略其他输出---------

|        |      | MySQL thread id 10838, OS thread handle 139797830510336, query id 1207298 localhost root                                            |
|        |      | TABLE LOCK table `mytest`.`e` trx id 1887777 lock mode IX                                                                           |
|        |      | RECORD LOCKS space id 3478 page no 4 n bits 72 index b of table `mytest`.`e` trx id 1887777 lock_mode X locks rec but not gap       |  -- index b 二级索引上的 Record Lock
|        |      | Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                     |
|        |      |  0: len 4; hex 80000008; asc     ;;                                                                                                 |
|        |      |  1: len 4; hex 8000000a; asc     ;;                                                                                                 |
|        |      |                                                                                                                                     |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887777 lock_mode X locks rec but not gap |  -- index GEN_CLUST_INDEX 聚集索引上的 Record Lock
|        |      | Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0                                                     |
|        |      |  0: len 4; hex 8000000a; asc     ;;                                                                                                 |
|        |      |  1: len 6; hex 0000001ccddc; asc       ;;                                                                                           |
|        |      |  2: len 7; hex cc000006c70144; asc       D;;                                                                                        |
|        |      |  3: len 4; hex 80000008; asc     ;;                                                                                                 |

  • 当查询条件是二级索引列的等值查询时

      1. READ-COMMITTED 模式下,二级索引查询的记录上有一个记录锁, 对应的聚集索引上有一个记录锁
      1. REPEATABLE-READ 模式下,二级索引查询的记录上有一个 Next-Key Lock ,该记录的下一个记录上有一个 Gap-Lock (二级索引);对应的聚集索引上有一个记录

3.5 插入意向锁(insert intention lock)

  • 插入意向锁 本质上就是个 Gap Lock

    • 普通Gap Lock 不允许(上一条记录,本记录) 范围内插入数据
    • 插入意向锁Gap Lock 允许(上一条记录,本记录) 范围内插入数据
  • 插入意向锁的作用是为了 提高并发插入的性能 多个事务 同时写入 不同数据至同一索引范围(区间)内,并不需要等待其他事务完成,不会发生锁等待

3.5.1. 插入的过程

  • 假设现在有记录 10,30,50,70 ;且为 主键 ,需要插入记录 25
  1. 找到 小于等于25的记录 ,这里是 10
  2. 找到 记录10的下一条记录 ,这里是 30
  3. 判断 下一条记录30 上是否有锁(如果有=25的情况,后面再讨论)
    • 判断 30 上面如果 没有锁 ,则 可以插入
    • 判断 30 上面如果有 Record Lock ,则 可以插入
    • 判断 30 上面如果有 Gap Lock / Next-Key Lock ,则无法插入,(10, 30) / (10, 30];在 30 上增加 insert intention lock (此时处于 waiting 状态),当 Gap Lock / Next-Key Lock 释放时,等待的事物(transaction)将被 唤醒 ,此时 记录30 上才能获得 insert intention lock ,然后再插入 记录25
  • 注意:一个事物 insert 25 且没有提交,另一个事物 delete 25 时,记录25上会有 Record Lock

3.5.2 插入意向锁的演示

  • 默认隔离级别下演示

  • 终端1

mysql root@localhost:mytest> create table f (a int(11) NOT NULL,primary key(a)) ;
Query OK, 0 rows affected
Time: 0.007s

mysql root@localhost:mytest> insert into f select a from e ;
Query OK, 5 rows affected
Time: 0.001s

mysql root@localhost:mytest> select * from f;
+----+
| a  |
+----+
| 1  |
| 3  |
| 5  |
| 7  |
| 10 |
+----+
5 rows in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:mytest>  select * from f where a<=6 for update;
+---+
| a |
+---+
| 1 |
| 3 |
| 5 |
+---+
3 rows in set
Time: 0.010s
mysql root@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> begin;insert into f values (4);   -- 插入的SQL语句,之前已阻塞的,等待锁释放中.....
Query OK, 0 rows affected
Time: 0.001s

  • 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                      |
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                             |
|        |      | =====================================                                                                                                                       |
|        |      | 2018-01-25 15:05:24 0x7f2554069700 INNODB MONITOR OUTPUT                                                                                                    |
|        |      | =====================================                                                                                                                       |
|        |      | Per second averages calculated from the last 10 seconds                                                                                                     |
----------省略其他输出---------
|        |      | mysql tables in use 1, locked 1                                                                                                                             |
|        |      | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)                                                                                                   |
|        |      | MySQL thread id 10634, OS thread handle 139798173411072, query id 1205721 localhost gcdb update                                                             |
|        |      | insert into f values (4)                                                                                                                                    | --等待语句
|        |      | ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:                                                                                             |
|        |      | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |   -- 插入记录4的事物等待中(被终端1中的事物阻塞了),等待 获得 插入意向锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a012a; asc       *;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | ------------------                                                                                                                                          |
|        |      | TABLE LOCK table `mytest`.`f` trx id 1887728 lock mode IX                                                                                                   |
|        |      | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting | 
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a012a; asc       *;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | ---TRANSACTION 1887728, ACTIVE 5 sec inserting                                                                                                              |
|        |      | mysql tables in use 1, locked 1                                                                                                                             |
|        |      | LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)                                                                                                   |
|        |      | MySQL thread id 10634, OS thread handle 139798173411072, query id 1205721 localhost gcdb update                                                             |
|        |      | insert into f values (4)                                                                                                                                    |
|        |      | ------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:                                                                                             |
|        |      | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a012a; asc       *;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | ------------------                                                                                                                                          |
|        |      | TABLE LOCK table `mytest`.`f` trx id 1887728 lock mode IX                                                                                                   |
|        |      | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887728 lock_mode X locks gap before rec insert intention waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a012a; asc       *;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | ---TRANSACTION 1887726, ACTIVE 202 sec                                                                                                                      |
|        |      | 2 lock struct(s), heap size 1136, 4 row lock(s)                                                                                                             |
|        |      | MySQL thread id 8365, OS thread handle 139798172346112, query id 1205655 localhost root                                                                     |
|        |      | TABLE LOCK table `mytest`.`f` trx id 1887726 lock mode IX                                                                                                   |
|        |      | RECORD LOCKS space id 3479 page no 3 n bits 72 index PRIMARY of table `mytest`.`f` trx id 1887726 lock_mode X                                               |
|        |      | Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000001; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a0110; asc        ;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a011d; asc        ;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a012a; asc       *;;                                                                                                                |
|        |      |                                                                                                                                                             |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                             |
|        |      |  0: len 4; hex 80000007; asc     ;;                                                                                                                         |
|        |      |  1: len 6; hex 0000001ccde9; asc       ;;                                                                                                                   |
|        |      |  2: len 7; hex d70000008a0137; asc       7;;                                                                                                                |
|        |      |                                                                                                                                                             |

3.5.3 插入意向锁提高插入的并发性演示

  • 终端1
mysql root@localhost:mytest> create table g (a int(11) NOT NULL,primary key(a));
Query OK, 0 rows affected
Time: 0.053s

mysql root@localhost:mytest> insert into g select * from d;
Query OK, 5 rows affected
Time: 0.002s

mysql root@localhost:mytest> select * from g;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set
Time: 0.010s

mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s

mysql root@localhost:mytest> select * from g where a<=40 for update;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
+----+
4 rows in set
Time: 0.010s

mysql root@localhost:mytest> commit;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> begin; insert into g values (25);
Query OK, 0 rows affected
Time: 0.001s

Query OK, 1 row affected
Time: 14.466s
mysql gcdb@localhost:mytest>

  • 终端3
mysql root@localhost:mytest> show engine innodb status;
+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                              |
+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                     |
|        |      | =====================================                                                                                                               |
|        |      | 2018-01-25 15:34:33 0x7f2554069700 INNODB MONITOR OUTPUT                                                                                            |
|        |      | =====================================                                                                                                               |

----------省略其他输出---------

|        |      | 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1                                                                                 |
|        |      | MySQL thread id 10634, OS thread handle 139798173411072, query id 1206268 localhost gcdb                                                            |
|        |      | TABLE LOCK table `mytest`.`g` trx id 1887740 lock mode IX                                                                                           |
|        |      | RECORD LOCKS space id 3480 page no 3 n bits 80 index PRIMARY of table `mytest`.`g` trx id 1887740 lock_mode X locks gap before rec insert intention | --插入意向锁已经获得
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0                                                                     |
|        |      |  0: len 4; hex 8000001e; asc     ;;                                                                                                                 |  --锁定记录30
|        |      |  1: len 6; hex 0000001ccdf5; asc       ;;                                                                                                           |
|        |      |  2: len 7; hex e10000003a012a; asc     : *;;                                                                                                        |
|        |      |                                                                                                                                                     |
|        |      | --------                                                                                                                                            |
  • 终端1
mysql root@localhost:mytest> insert into g values (45);  -- 插入小于50,大于40的值,插入成功
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> insert into g values (35);  -- 插入小于40,大于30的值,插入成功
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> insert into g values (15);  -- 插入小于20,大于10的值,插入成功
Query OK, 1 row affected
Time: 0.001s

mysql root@localhost:mytest> select * from g ;
+----+
| a  |
+----+
| 10 |
| 15 |
| 20 |
| 30 |
| 35 |
| 40 |
| 45 |
| 50 |
+----+
8 rows in set
Time: 0.009s

-- 这样就可以并发的插入记录了,而不需要一个事物等待另一事物,所以当所有相关的插入的事物都提交后,30上的插入意向锁便释放

3.6 锁的内部实现

  • 每个事物每个页 一个锁对象
    • 约100个字节
  • 通过位图存放锁信息
    • 内存占用少
  • 没有锁升级
    • like Oracle

假设页 (20 100 中有 250 条记录,变量 n_bit=250+64=314 ,那么实际位图需要 40 个字节
用于位图的管理(n_bytes= 1 +3 14 /8=40) 。若页中 heap_no为2、3、4 的记录都己经上锁,则对应的数据结构 lock_rec_t 在内存中的关系如下图所示。

由上图可以看到,在页 (20, 100) 中,前3条用户记录都有锁,
因此在对应的数据结构的lock_rec_t中对应的 heap no位图值都为1

3.7 显式锁和隐式锁

3.7.1 显式锁和隐式锁

  • 显式锁

    • 显式锁是使用全局记录锁哈希表lock_t结构的锁,通常用show engine innodb status能查看到
  • 隐式锁

    • 隐式锁没有分配关联的lock_t对象所谓的隐式锁,可以理解成一个记录标记,在内存的锁对象在hash中是不存在的。但我们在更新数据块时,会进行如下操作:
      • 对于聚集索引,每次更改或插入记录会同时写入对应的事务id回滚段指针
      • 对于二级索引,每次更改/插入二级索引记录,会更新二级索引页最大事务id
  • 隐式锁的逻辑过程如下:

    • A. InnoDB的每条记录中都一个隐含trx_id字段,这个字段存在于簇索引B+Tree中。
    • B. 在操作一条记录前,首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务,首先将隐式锁转换为显式锁(就是为该事务添加一个锁)。
    • C. 检查是否有锁冲突,如果创建锁,并设置为waiting状态。如果有冲突不加锁,写数据,并将自己的trx_id写入trx_id字段。
    • D. 等待加锁成功被唤醒,或者超时。
    • E. 写数据,并将自己的trx_id写入trx_id字段。Page Lock可以保证操作的正确性。

3.7.2 显式锁(explicit lock)与隐式锁(implicit lock)的区别

  • explicit locK
    • gap explicit lock
    • no gap explicit lock

gap 通过type_modeLOCK_GAP来进行设置。
no gap explict lock锁住的是记录以及记录之前范围,否则仅锁住范围
explicit lock 可以是 S-lock也可以是X-lock 。然而 implicit lock 总是为 X-lock

explicit lock 是非常直接加锁,例如 SELECT * FROM t WHERE rowed = xxx FOR UPDATE ,那
么就需要对这个记录进行加锁,锁的类型为 x-lock 这个过程通过函数 lock_rec_create 实现。

implicit lock 是指索引记录逻辑上有x-lock ,但实际在内存对象中并不含有这个锁信息。这意
味着implicit lock没有任何内存开销, 从减少InnoDB存储引擎的开销

显示锁和隐式锁参考

3.7.3 聚集索引记录的隐式锁

  • 聚集索引记录中 implicit lock 的判断较为简单。
    • 因为每个聚集索引记录都有个事务id隐藏列,只需要通过该事务id 判断当前是否为活跃事务就能得知是否有 implicit lock。若通过事务 id 查询得到该事务为活跃事务,则此聚集索引记录上有 implicit lock 。反之,则不含有 implicit lock

3.7.3 二级索引记录的隐式锁

  • 二级索引记录是不含有事务id的隐藏列,然而 ,每个二级索引页通过 page headerPAGE_MAX_TRX_ID 保存一个最大事务ID ,当二级索引中的任何记录被更新后,都需要更新PAGE_MAX_TRX_ID 的值。因此判断
    二级索引记录的 implicit lock 分为两个步骤进行:
    • 根据二级索引页的 PAGE_MAX_TRX_ID值进行判断,
    • 通过聚集索引记录进行判断。
  • 因此若当前 PAGE_MAX_TRX_ID 值小于当前活跃事务的最小id 时,则此二级索引记录不含有 implicit lock,
    即之前已经提交的事务修改了该记录。
  • PAGE_MAX_TRX_ID 大于等于当前活跃事务的最id 时,则存在下这些可能性:
    • 存在某活跃事务,修改了二级索引记录,从而导致 PAGE_MAX_TRX_IDD 的更新
    • 存在事务(可能是活跃也可能已经完成提交操作) ,修改了页中其他的二级索引记录,从而导致 PAGE_MAX_TRX_ID 更新。

此时需要通过二级索引记录对应的聚集索引来判断是否含有implicit lock ,这个过程通过函数row_vers_impl_x_locked_off_kernel 实现,其判断过程下图所示。

row_vers_impl_x_locked_off_kernel

函数 row_vers_impl_x_locked_off_kernel 不仅要判断二级索引记录上是否有锁,并且若有锁,
还要返回当前持有该锁事务对象。由于二级索引记录不包含隐藏的事务id 列,因此该信息
必然需要通过对应的聚集索引记录来获得。

判断二级索引记录是否持有 implicit lock,只须判断是否存在未提交的活跃事务对记录进行
INSERT DELETE UPDATE 的操作。若有,则必然持有 implicit lock ,并返回该活跃事务对象 trx_t。

  • 辅助索引记录的隐式锁参考(姜大佬(MYSQL内核:INNODB存储引擎 卷1)第九章锁)

3.8 一致性的非锁定读

在隔离级别为 READ UNCOMMLTTED、READ COMMITTED、REPEATABLE READ 时,读取(select)操作是不会加锁的,通过行多版本控制( MVCC )的方式读取当前执行时间点的记录。

  • 终端1
mysql root@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s
mysql root@localhost:mytest> select * from e where b=8; -- 非锁定的读,不会阻塞
+----+---+
| a  | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.009s
mysql root@localhost:mytest>

  • 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest> select * from e where b=8;  -- 非锁定的读,不会阻塞
+----+---+
| a  | b |
+----+---+
| 10 | 8 |
+----+---+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>

通过 UNDO 指针的指向,可以读取前一个版本甚至前几个版本的记录(即通过UNDO来构造版本记录),从而实现 快照读 (Snapshot Read);
通过trx_id判断该记录是否被锁住(在线事物列表),从而决定是否要读取之前的版本(UNDO)

四. 死锁

4.1 死锁的介绍

  • 两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象
    • AB-BA, A和B互相等待
  • 解决死锁
    • 超时,死锁锁超时不是同一个东西( 锁超时解决死锁 的一种方式)
    • --innodb_lock_wait_timeout,默认是50s
  • waits-for graph
    • 自动死锁检测

数据库中的死锁和程序中的死锁不同,数据库中的死锁是不能完全避免的,且数据库中的死锁有检测机制

4.2 AB-BA死锁演示

  • 终端1
mysql root@localhost:(none)> show variables like 'innodb%print%deadlocks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set
Time: 0.012s

mysql root@localhost:(none)> set global innodb_print_all_deadlocks=1; -- 会将死锁信息打印到err_log(搜索 deadlock 关键字)
Query OK, 0 rows affected
Time: 0.001s

mysql root@localhost:(none)> show variables like 'innodb%print%deadlocks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+
1 row in set
Time: 0.012s
mysql root@localhost:(none)>exit
  • 终端1
(root@localhost) 15:39:15 [mytest]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) 15:39:19 [mytest]> delete from e where a=3;
Query OK, 1 row affected (0.00 sec)

  • 终端2
(gcdb@localhost) 15:39:52 [mytest]> begin;
Query OK, 0 rows affected (0.00 sec)

(gcdb@localhost) 15:39:56 [mytest]> delete from e where a=5;
Query OK, 1 row affected (0.00 sec)

(gcdb@localhost) 15:40:32 [mytest]> delete from e where a=3;
Query OK, 1 row affected (11.40 sec)

(gcdb@localhost) 15:40:48 [mytest]>

  • 终端1
(root@localhost) 15:39:45 [mytest]> delete from e where a=5;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  --出现死锁报错
(root@localhost) 15:40:48 [mytest]>
  • 终端3
mysql root@localhost:(none)> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                      |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                             |
|        |      | =====================================                                                                                                       |
|        |      | 2018-01-29 15:40:54 0x7f254c5f0700 INNODB MONITOR OUTPUT                                                                                    |

----------省略其他输出---------

|        |      | ------------------------                                                                                                                    |
|        |      | LATEST DETECTED DEADLOCK                                                                                                                    | --最新检测到的死锁
|        |      | ------------------------                                                                                                                    |
|        |      | 2018-01-29 15:40:48 0x7f254c776700                                                                                                          |
|        |      | *** (1) TRANSACTION:                                                                                                                        |
|        |      | TRANSACTION 1887794, ACTIVE 16 sec starting index read                                                                                      |
|        |      | mysql tables in use 1, locked 1                                                                                                             |
|        |      | LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                               |
|        |      | MySQL thread id 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb updating                                           |
|        |      | delete from e where a=3                                                                                                                     |
|        |      | *** (1) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap waiting |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce31; asc      1;;                                                                                                   |
|        |      |  2: len 7; hex 290000008d1735; asc )     5;;                                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | *** (2) TRANSACTION:                                                                                                                        |
|        |      | TRANSACTION 1887793, ACTIVE 63 sec starting index read, thread declared inside InnoDB 5000                                                  |
|        |      | mysql tables in use 1, locked 1                                                                                                             |
|        |      | 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                                         |
|        |      | MySQL thread id 45374, OS thread handle 139798173411072, query id 1310151 localhost root updating                                           |
|        |      | delete from e where a=5                                                                                                                     |
|        |      | *** (2) HOLDS THE LOCK(S):                                                                                                                  |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap         |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce31; asc      1;;                                                                                                   |
|        |      |  2: len 7; hex 290000008d1735; asc )     5;;                                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | *** (2) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce32; asc      2;;                                                                                                   |
|        |      |  2: len 7; hex 2a00000027123d; asc *   =;;                                                                                                |
| InnoDB |      |                                                                                                                                             |
|        |      | =====================================                                                                                                       |
|        |      | 2018-01-29 15:40:54 0x7f254c5f0700 INNODB MONITOR OUTPUT                                                                                    |
|        |      | =====================================                                                                                                       |
|        |      | Per second averages calculated from the last 36 seconds                                                                                     |
|        |      | -----------------                                                                                                                           |
|        |      | BACKGROUND THREAD                                                                                                                           |
|        |      | -----------------                                                                                                                           |
|        |      | srv_master_thread loops: 2702 srv_active, 0 srv_shutdown, 1213011 srv_idle                                                                  |
|        |      | srv_master_thread log flush and writes: 1215640                                                                                             |
|        |      | ----------                                                                                                                                  |
|        |      | SEMAPHORES                                                                                                                                  |
|        |      | ----------                                                                                                                                  |
|        |      | OS WAIT ARRAY INFO: reservation count 2793                                                                                                  |
|        |      | OS WAIT ARRAY INFO: signal count 2732                                                                                                       |
|        |      | RW-shared spins 0, rounds 4896, OS waits 2134                                                                                               |
|        |      | RW-excl spins 0, rounds 3731, OS waits 27                                                                                                   |
|        |      | RW-sx spins 131, rounds 2447, OS waits 41                                                                                                   |
|        |      | Spin rounds per wait: 4896.00 RW-shared, 3731.00 RW-excl, 18.68 RW-sx                                                                       |
|        |      | ------------------------                                                                                                                    |
|        |      | LATEST DETECTED DEADLOCK                                                                                                                    |
|        |      | ------------------------                                                                                                                    |
|        |      | 2018-01-29 15:40:48 0x7f254c776700                                                                                                          |
|        |      | *** (1) TRANSACTION:                                                                                                                        |
|        |      | TRANSACTION 1887794, ACTIVE 16 sec starting index read                                                                                      |
|        |      | mysql tables in use 1, locked 1                                                                                                             |
|        |      | LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                               |
|        |      | MySQL thread id 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb updating                                           |
|        |      | delete from e where a=3                                                                                                                     |
|        |      | *** (1) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap waiting |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce31; asc      1;;                                                                                                   |
|        |      |  2: len 7; hex 290000008d1735; asc )     5;;                                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | *** (2) TRANSACTION:                                                                                                                        |
|        |      | TRANSACTION 1887793, ACTIVE 63 sec starting index read, thread declared inside InnoDB 5000                                                  |
|        |      | mysql tables in use 1, locked 1                                                                                                             |
|        |      | 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                                         |
|        |      | MySQL thread id 45374, OS thread handle 139798173411072, query id 1310151 localhost root updating                                           |
|        |      | delete from e where a=5                                                                                                                     |
|        |      | *** (2) HOLDS THE LOCK(S):                                                                                                                  |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap         |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce31; asc      1;;                                                                                                   |
|        |      |  2: len 7; hex 290000008d1735; asc )     5;;                                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | *** (2) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887793 lock_mode X locks rec but not gap waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce32; asc      2;;                                                                                                   |
|        |      |  2: len 7; hex 2a00000027123d; asc *   ' =;;                                                                                                |
|        |      |  3: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | *** WE ROLL BACK TRANSACTION (2)                                                                                                            |
|        |      | ------------                                                                                                                                |
|        |      | TRANSACTIONS                                                                                                                                |
|        |      | ------------                                                                                                                                |
|        |      | Trx id counter 1887800                                                                                                                      |
|        |      | Purge done for trx's n:o < 1887800 undo n:o < 0 state: running but idle                                                                     |
|        |      | History list length 61                                                                                                                      |
|        |      | LIST OF TRANSACTIONS FOR EACH SESSION:                                                                                                      |
|        |      | ---TRANSACTION 421284540507984, not started                                                                                                 |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                             |
|        |      | ---TRANSACTION 421284540509808, not started                                                                                                 |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                             |
|        |      | ---TRANSACTION 1887794, ACTIVE 22 sec                                                                                                       |
|        |      | 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2                                                                         |
|        |      | MySQL thread id 45377, OS thread handle 139798173144832, query id 1310147 localhost gcdb                                                    |
|        |      | TABLE LOCK table `mytest`.`e` trx id 1887794 lock mode IX                                                                                   |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap         |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000005; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce32; asc      2;;                                                                                                   |
|        |      |  2: len 7; hex 2a00000027123d; asc *   ' =;;                                                                                                |
|        |      |  3: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |
|        |      | RECORD LOCKS space id 3478 page no 3 n bits 80 index PRIMARY of table `mytest`.`e` trx id 1887794 lock_mode X locks rec but not gap         |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32                                                            |
|        |      |  0: len 4; hex 80000003; asc     ;;                                                                                                         |
|        |      |  1: len 6; hex 0000001cce32; asc      2;;                                                                                                   |
|        |      |  2: len 7; hex 2a000000271266; asc *   ' f;;                                                                                                |
|        |      |  3: len 4; hex 80000001; asc     ;;                                                                                                         |
|        |      |                                                                                                                                             |

在MySQL出现错误( 非死锁 )的时候,线程中的事物中止 的,需要 显示 的提交或回滚;而 死锁 时,MySQL会 自动回滚;

  • 假如一个事物中有10个步骤,当执行到步骤8时:
    • 出现普通错误,在步骤8中停止,只要重新执行步骤8即可,然后再继续执行后续操作
    • 出现死锁时,该事物中,步骤8之前的操作都会自动回滚

4.3 官网两种死锁演示

官档-死锁

4.3.1 第一种

步骤 会话1 会话2 会话3
1 START TRANSACTION;
INSERT INTO t1 VALUES(1)
2 START TRANSACTION;
INSERT INTO t1 VALUES(1);
3 START TRANSACTION;
INSERT INTO t1 VALUES(1);
4 ROLLBACK;
5 INSERT OK ;
6 ERROR1213,Deadlock
  • 终端1
mysql gcdb@localhost:mytest> CREATE TABLE `t1` (
                          ->  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                          ->   `a` int(11) unsigned DEFAULT NULL,
                          ->    PRIMARY KEY (`id`),
                          ->     UNIQUE KEY `a` (`a`)
                          ->     ) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected
Time: 0.009s

mysql gcdb@localhost:mytest> insert into t1(a)  values(10);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t1(a)  values(20);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t1(a)  values(30);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> select * from t1;
+-----+----+
| id  | a  |
+-----+----+
| 100 | 10 |
| 101 | 20 |
| 102 | 30 |
+-----+----+
3 rows in set
Time: 0.010s

mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t1(a)  values(15);
Query OK, 1 row affected
Time: 0.001s

  • 终端2
mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest>  insert into t1(a)  values(15);
  • 终端3
mysql root@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:mytest>  insert into t1(a)  values(15);
  • 终端1
mysql gcdb@localhost:mytest> rollback;   
Query OK, 0 rows affected
Time: 0.001s
  • 终端2
mysql gcdb@localhost:mytest>  insert into t1(a)  values(15);     --终端2,insert into 语句执行成功。
Query OK, 1 row affected
Time: 8.870s
mysql gcdb@localhost:mytest>

  • 终端3
mysql root@localhost:mytest>  insert into t1(a)  values(15);
(1213, u'Deadlock found when trying to get lock; try restarting transaction')    --终端3,报ERROR1213 死锁
mysql root@localhost:mytest>
  • 终端1
mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                 |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                        |
|        |      | =====================================                                                                                                                  |
|        |      | 2018-01-30 14:58:10 0x7f25380b3700 INNODB MONITOR OUTPUT                                                                                               |
|        |      | =====================================                                                                                                                  |

----------省略其他输出---------

|        |      | ------------------------                                                                                                                               |
|        |      | LATEST DETECTED DEADLOCK                                                                                                                               |
|        |      | ------------------------                                                                                                                               |
|        |      | 2018-01-30 14:58:07 0x7f254c672700                                                                                                                     |
|        |      | *** (1) TRANSACTION:                                                                                                                                   |
|        |      | TRANSACTION 1887869, ACTIVE 9 sec inserting, thread declared inside InnoDB 1                                                                           |
|        |      | mysql tables in use 1, locked 1                                                                                                                        |
|        |      | LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                                          |
|        |      | MySQL thread id 53739, OS thread handle 139798173144832, query id 1335448 localhost gcdb update                                                        |  --终端2线程
|        |      | insert into t1(a)  values(15)                                                                                                                          |  --终端2线程执行insert语句
|        |      | *** (1) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                           |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock_mode X locks gap before rec insert intention waiting |  --终端2中的事物在等待a记录20上的插入意向锁(Gap Lock)
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    |  --锁定a记录20
|        |      |  1: len 4; hex 00000065; asc    e;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** (2) TRANSACTION:                                                                                                                                   |
|        |      | TRANSACTION 1887870, ACTIVE 7 sec inserting, thread declared inside InnoDB 1                                                                           |
|        |      | mysql tables in use 1, locked 1                                                                                                                        |
|        |      | 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1                                                                                    |
|        |      | MySQL thread id 53747, OS thread handle 139798172346112, query id 1335449 localhost root update                                                        | --终端3线程
|        |      | insert into t1(a)  values(15)                                                                                                                          |
|        |      | *** (2) HOLDS THE LOCK(S):                                                                                                                             |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887870 lock mode S locks gap before rec                          | --终端3中的事物持有a记录20的 S-Gap Lock
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000065; asc    e;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** (2) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                           |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887870 lock_mode X locks gap before rec insert intention waiting | --端3中的事物在等待a记录20上的插入意向锁(Gap Lock)
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000065; asc    e;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** WE ROLL BACK TRANSACTION (2)                                                                                                                       | --回滚`终端3`中的事物,所以`终端2`中才插入成功
|        |      | ------------                                                                                                                                           |
|        |      | TRANSACTIONS                                                                                                                                           |
|        |      | ------------                                                                                                                                           |
|        |      | Trx id counter 1887871                                                                                                                                 |
|        |      | Purge done for trx's n:o < 1887869 undo n:o < 0 state: running but idle                                                                                |
|        |      | History list length 14                                                                                                                                 |
|        |      | LIST OF TRANSACTIONS FOR EACH SESSION:                                                                                                                 |
|        |      | ---TRANSACTION 421284540509808, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | ---TRANSACTION 421284540507984, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | Trx id counter 1887871                                                                                                                                 |
|        |      | Purge done for trx's n:o < 1887869 undo n:o < 0 state: running but idle                                                                                |
|        |      | History list length 14                                                                                                                                 |
|        |      | LIST OF TRANSACTIONS FOR EACH SESSION:                                                                                                                 |
|        |      | ---TRANSACTION 421284540509808, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | ---TRANSACTION 421284540507984, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | ---TRANSACTION 1887869, ACTIVE 12 sec                                                                                                                  |
|        |      | 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1                                                                                    |
|        |      | MySQL thread id 53739, OS thread handle 139798173144832, query id 1335448 localhost gcdb                                                               |
|        |      | TABLE LOCK table `mytest`.`t1` trx id 1887869 lock mode IX                                                                                             |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock mode S                                               |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock mode S locks gap before rec                          |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    | --锁定记a录20
|        |      |  1: len 4; hex 00000065; asc    e;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000000f; asc     ;;                                                                                                                    | --锁定记a录15
|        |      |  1: len 4; hex 00000068; asc    h;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887869 lock_mode X locks gap before rec insert intention         |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    |  --锁定a记录20
|        |      |  1: len 4; hex 00000065; asc    e;;                                                                                                                    |
|        |      |                                                                                                                                                        |


mysql gcdb@localhost:mytest> SHOW processlist > ;
+-------+------+-----------+--------+---------+------+----------+------------------+
| Id    | User | Host      | db     | Command | Time | State    | Info             |
+-------+------+-----------+--------+---------+------+----------+------------------+
| 53739 | gcdb | localhost | mytest | Sleep   | 1463 |          | <null>           | --终端2线程号53739
| 53742 | gcdb | localhost | mytest | Query   | 0    | starting | SHOW processlist | --终端1线程号53742
| 53747 | root | localhost | mytest | Sleep   | 1461 |          | <null>           | --终端3线程号53747
+-------+------+-----------+--------+---------+------+----------+------------------+
3 rows in set
Time: 0.010s
mysql gcdb@localhost:mytest>

  • 死锁具体分析:

    • 原来加在 a记录15 上的锁,都转移到了 a记录20 上了( 锁继承 )

      1. 因为 终端1 中的事物 回滚,则 a记录15 标记为删除(delete-mark)
      2. 终端2终端3 中的 事物 都对 a记录15 添加了 S-Lock (后台 Purge 线程会把 a记录15 给删除);
      3. 此时下一个记录,即 a记录20继承之前 a记录15上的锁,所以上面看到了 的信息都在 a记录20 上。
    • 此时的锁的状态是:

      1. 终端2 持有a记录20S Gap-Lock ,并且等待X locks gap insert intention lock
      2. 终端3 持有a记录20的 S Gap-Lock ,并且等待 X locks gap insert intention lock
  • X Lock 和 S Lock 是不兼容的

    • 终端3insert intention lock 等待会话2S Gap-Lock 的释放;
    • 终端2insert intention lock 等待会话3S Gap-Lock 的释放;
    • 都在等待对方释放,所以产生了死锁

至于为什么加S Gap-Lock,是因为在插入之前还需要增加一步检查:如果记录中有唯一约束 ,判断存在一条记录等于当前插入的记录时,则需要在这个记录加上S Gap-Lock

  • 完整的插入过程如下

假设现在有记录 10,30,50,70 ;且为 主键 ,需要插入记录 25

1. 找到`小于等于25的记录` ,这里是`记录10`◦ 
    - 如果记录中已经`存在记录25` ,且带有`唯一性约束`,则需要在`记录25`上增加`S Gap-lock`◦ 
    - 不直接报错退出或者提示已存在的原因,是因为有可能之前的 `记录25` 标记为`删除( delete-mark )`,然后等待 `purg`◦ 
    - 如果 `假设 这里 `没有S Gap-Lock` ,此时 `记录30` 上也 `没有锁` ,按照下面的步骤,可以插入 `两个记录25` ,破坏了`唯一性约束`
2. 找到`记录10的下一条记录` ,这里是 `记录30`
3. 判断 `下一条记录30` 上是`否有锁`。
    判断`记录30`上面如果 `没有锁` ,则 `可以插入`◦ 
    判断`记录30`上面如果有`Record Lock` ,则 `可以插入`◦ 
    判断`记录30`上面如果有 `Gap Lock / Next-Key Lock` ,则无法插入,因为锁的范围是 `(10, 30) / (10, 30]` ;在 `记录30` 上增加 `insert intention lock` (此时处于 waiting 状态),当 `Gap Lock / Next-Key Lock` 释放时,`等待的事物(transaction)`将被 `唤醒` ,此时 `记录30` 上才能获得 `insert intention lock` ,然后`再插入记录25`。

4.3.1 第二种

步骤 会话1 会话2 会话3
1 START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
2 START TRANSACTION;
INSERT INTO t1 VALUES(1);
3 START TRANSACTION;
INSERT INTO t1 VALUES(1);
4 COMMIT;
5 INSERT OK
6 ERROR1213,Deadlock

这里不累赘上面步骤2~5,展示步骤1和步骤6

  • 终端1
mysql gcdb@localhost:mytest> start transaction;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> DELETE FROM t1 WHERE a = 20;
Query OK, 1 row affected
Time: 0.001s
  • 终端1
mysql gcdb@localhost:mytest> SHOW processlist;
+-------+------+-----------+--------+---------+------+----------+------------------+
| Id    | User | Host      | db     | Command | Time | State    | Info             |
+-------+------+-----------+--------+---------+------+----------+------------------+
| 53739 | gcdb | localhost | mytest | Sleep   | 30   |          | <null>           |  --终端2线程号53739
| 53742 | gcdb | localhost | mytest | Query   | 0    | starting | SHOW processlist |  --终端1线程号53742
| 53747 | root | localhost | mytest | Sleep   | 23   |          | <null>           |  --终端3线程号53747
+-------+------+-----------+--------+---------+------+----------+------------------+
3 rows in set
Time: 0.011s

mysql gcdb@localhost:mytest> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                 |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      |                                                                                                                                                        |
|        |      | =====================================                                                                                                                  |
|        |      | 2018-01-30 15:47:02 0x7f25380b3700 INNODB MONITOR OUTPUT                                                                                               |
|        |      | =====================================                                                                                                                  |

----------省略其他输出---------

|        |      | ------------------------                                                                                                                               |
|        |      | LATEST DETECTED DEADLOCK                                                                                                                               |
|        |      | ------------------------                                                                                                                               |
|        |      | 2018-01-30 15:46:34 0x7f254c672700                                                                                                                     |
|        |      | *** (1) TRANSACTION:                                                                                                                                   |
|        |      | TRANSACTION 1887876, ACTIVE 13 sec inserting                                                                                                           |
|        |      | mysql tables in use 1, locked 1                                                                                                                        |
|        |      | LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1                                                                          |
|        |      | MySQL thread id 53739, OS thread handle 139798173144832, query id 1336331 localhost gcdb update                                                        | --终端2
|        |      | insert into t1(a)  values(20)                                                                                                                          | --终端2线程执行insert语句
|        |      | *** (1) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                           |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock_mode X locks gap before rec insert intention waiting | --终端2中的事物在等待a记录30上的插入意向锁(Gap Lock)
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    | --锁定a记录30
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** (2) TRANSACTION:                                                                                                                                   |
|        |      | TRANSACTION 1887877, ACTIVE 6 sec inserting, thread declared inside InnoDB 1                                                                           | 
|        |      | mysql tables in use 1, locked 1                                                                                                                        |
|        |      | 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1                                                                                    |
|        |      | MySQL thread id 53747, OS thread handle 139798172346112, query id 1336336 localhost root update                                                        | --终端3
|        |      | insert into t1(a)  values(20)                                                                                                                          |
|        |      | *** (2) HOLDS THE LOCK(S):                                                                                                                             |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887877 lock mode S                                               |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** (2) WAITING FOR THIS LOCK TO BE GRANTED:                                                                                                           |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887877 lock_mode X locks gap before rec insert intention waiting |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | *** WE ROLL BACK TRANSACTION (2)                                                                                                                       |  --回滚`终端3`中的事务
|        |      | ------------                                                                                                                                           |
|        |      | TRANSACTIONS                                                                                                                                           |
|        |      | ------------                                                                                                                                           |
|        |      | Trx id counter 1887879                                                                                                                                 |
|        |      | Purge done for trx's n:o < 1887879 undo n:o < 0 state: running but idle                                                                                |
|        |      | History list length 17                                                                                                                                 |
|        |      | LIST OF TRANSACTIONS FOR EACH SESSION:                                                                                                                 |
|        |      | ---TRANSACTION 421284540509808, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | ---TRANSACTION 421284540507984, not started                                                                                                            |
|        |      | 0 lock struct(s), heap size 1136, 0 row lock(s)                                                                                                        |
|        |      | ---TRANSACTION 1887876, ACTIVE 41 sec                                                                                                                  |
|        |      | 4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1                                                                                    |
|        |      | MySQL thread id 53739, OS thread handle 139798173144832, query id 1336331 localhost gcdb                                                               |
|        |      | TABLE LOCK table `mytest`.`t1` trx id 1887876 lock mode IX                                                                                             |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock mode S                                               |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock_mode X locks gap before rec insert intention         |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | RECORD LOCKS space id 3485 page no 4 n bits 72 index a of table `mytest`.`t1` trx id 1887876 lock mode S locks gap before rec                          |
|        |      | Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 00000014; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 0000006a; asc    j;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0                                                                        |
|        |      |  0: len 4; hex 0000001e; asc     ;;                                                                                                                    |
|        |      |  1: len 4; hex 00000066; asc    f;;                                                                                                                    |
|        |      |                                                                                                                                                        |
|        |      | --------                                                                                                                                               |

  • 这种死锁的情况,线上是无法完全避免的,解决的办法就是 程序端重试
  • 后续涉及Purge时,可以只使用 两条SQL 语句,就出现死锁现象,这里使用了3条SQL语句。
  • mysqld-debug 版本可以操作更多的参数(比如关闭purge),并且可以看到丰富的信息,可以用于故障诊断

五.AI自增锁

5.1 自增锁

  • 一个表一个自增
  • AUTO_INCREMENT PK
  • 在事务提交前释放
    • 其他的锁在事务提交时才释放
    • 如果AI锁在提交后才释放,那其他事物就没法插入了,无法实现并发

5.2 演示

5.2.1 演示1

  • 终端1
mysql gcdb@localhost:mytest>  create table t_ai_1(a int auto_increment, b int , primary key(a));
Query OK, 0 rows affected
Time: 0.007s

mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t_ai_1 values (NULL, 10);  -- 插入一个值,且事物没有提交
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>
  • 终端2
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> insert into t_ai_1 values(NULL, 20);  --终端1中的事物没有提交,但是终端2中的事物仍能提交,说明AI锁已经释放了
Query OK, 1 row affected
Time: 0.001s
mysql gcdb@localhost:mytest>

-- AI锁 在事物提交前就释放了,类似latch,使用完就释放了

5.2.2 演示2

  • 终端1和终端2
mysql gcdb@localhost:mytest> rollback;
Query OK, 0 rows affected
Time: 0.001s
mysql gcdb@localhost:mytest>

  • 终端1
mysql gcdb@localhost:mytest> begin;
Query OK, 0 rows affected
Time: 0.000s

mysql gcdb@localhost:mytest> insert into t_ai_1 values (NULL, 20);
Query OK, 1 row affected
Time: 0.001s

mysql gcdb@localhost:mytest> commit;
Query OK, 0 rows affected
Time: 0.001s

mysql gcdb@localhost:mytest> select * from t_ai_1;  -- 因为之前的两次插入都rollback了,但是AI锁其实是提交的,导致现在插入时,自增列的序号从3开始
+---+----+
| a | b  |
+---+----+
| 3 | 20 |
+---+----+
1 row in set
Time: 0.010s
mysql gcdb@localhost:mytest>

事物回滚后,自增值 不会跟着回滚,导致自增值不连续,但是这个值连续也没什么意义。

5.3 AI自增锁的方式

所有的插入都是insert-like

  • 如果插入前能 确定行数 的,就是 simple inserts
    • insert into table_1 values(NULL, 1), (NULL, 2);
  • 如果插入前 不能确定行数 的,就是 bulk inserts
    • insert into table_1 select * from table_2;
  • 如果 部分自增长,部分指定 的,就是 mixed-mode inserts

insert ... on duplicate key update 不推荐 使用

  1. 非ANSI SQL 标准
  2. 效果并非预期所期望的那样

5.3.1 设置自增并发度

--innodb_autoinc_lock_mode={0|1|2}

  • innodb_autoinc_lock_mode 是 read-only 的,需要修改后 重启MySQL

  • 0 传统方式

    • SQL语句执行完之后 ,AI锁才释放
    • 例如:当 insert ... select ... 数据量很大时(比如执行10分钟),那在这个 SQL执行完毕前 ,其他事物是 不能插入 的(AI锁未释放)
    • 这样可以保证在这个SQL语句内插入的数据,自增值是 连续的,因为在这个时间内,AI自增锁是被这个SQL持有的,且没有释放
  • 1 默认参数( 大部分情况设置为1 )

    • bulk inserts, 同传统方式一样

      • 对于 bulk inserts 的方式,和 0 - 传统方式 一样,在SQL执行完之后AI锁才释放
    • simple inserts, 并发方式

      • SQL运行完之前确定自增值之后 ,就可以释放自增锁

    因为 bulk inserts 不知道要插入多少行,所以只能等insert结束后,才知道 N 的值,然后 一次性(ai + N)

    simple inserts 知道插入的行数(M),所以可以先 (ai + M) ,然后将锁释放掉,给别的事物用,然后自己慢慢插入数据

  • 参数2

    • 所有自增都可以并发方式( 不同于Simple inserts的方式 )
    • 同一SQL语句自增可能不连续
    • row-based binlog
    for (i = ai; until_no_rec; i++) {
     acquire AI_Lock        # 插入前申请锁
     insert one record...   # 只插入一条记录
     ai = ai + 1            # 自增值+1
     release AI_Lock        # 释放锁
    }
    

    这样做的好处是,对于批量的、耗时的插入,SQL不会长时间的持有AI自增锁,而是插入 一条 (有且仅插入一条,而simple inserts是确定好的M条)语句后就 释放 ,这样可以给别的事物使用,实现并发。
    但是这种方式 并发度是增加了 ,但是性能不一定变好,尤其是单线程导入数据时,要 不断的申请和释放锁
    对于批量插入来说,自增就可能变的不连续了(需要和开发沟通,是否可以接受)

5.4. 自增列与索引

mysql gcdb@localhost:mytest>  create table t_ai_1(a int auto_increment, b int , key(b,a));
(1075, u'Incorrect table definition; there can be only one auto column and it must be defined as a key')  --错误定义,这里有前提条件必须定义为key的第一列

mysql gcdb@localhost:mytest>  create table t_ai_1(a int auto_increment, b int , key(a,b));
Query OK, 0 rows affected
Time: 0.008s
mysql gcdb@localhost:mytest>

自增列必须定义为索引列 ,且 必须是第一个列 ,这样做的好处是,因为索引的第一个列是排序的,这样重启后,数据库可以直接找到 最后一个自增值 ,然后 N+1 操作后,就可以作为 下一个自增列的值 了(否则要全表扫描了;MySQL的自增列的值 不是持久化 的)。

5.5. 自增的两个参数

  • --auto_increment_increment = 1
  • --auto_increment_offset = 1
mysql gcdb@localhost:mytest> show variables like 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |  --步长
| auto_increment_offset    | 1     |  --初始值
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+
4 rows in set
Time: 0.013s

  • 两个服务器上的User表的主键的值就没有交叉了

  • 如果有三台服务器,则 A:[offset = 1, increment=3] , B:[offset = 2, increment=3] , C:[offset = 3, increment=3]

  • 如果一开始不知道后面会有多少台服务器,则可以一开始把increment设置的大一点,比如是10,这样只会浪费一点自增值

  • 这样做的目的是保证 每个节点 上产生的 自增值全局唯一 的,这样做并不能用来做双主(比如一些额外的唯一索引能保证全局唯一么)

posted @ 2018-01-04 16:48  貔貅小米豆  阅读(638)  评论(0编辑  收藏  举报