章节目标

了解InnoDB存储引擎对表中数据的锁定
了解InnoDB存储引擎会以怎样的粒度锁定数据

只有当实现本身会增加开销时，行级锁才会增加开销。一个锁和多个锁的开销是相同的。

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

6.1 什么是锁

锁是数据库系统区别于文件系统的一个关键特性。
锁机制用于管理对共享资源的并发访问。

6.2 lock与latch

latch：

分为mutex（互斥量）和rwlock（读写锁）
保证并发线程操作临界资源的正确性
没有死锁检测的机制

lock

对象是事务，用来锁定的是数据库中的对象，如表、页、行。
仅在事务commit或rollback后进行释放
有死锁机制

6.3 InnoDB 存储引擎中的锁

6.3.1 锁的类型

InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（S Lock），允许事务读一行数据
排他锁（X Lock），允许事务删除或更新一行数据

S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况

InnoDB存储引擎支持多粒度（granular）锁定：

这种锁定允许事务在行级上的锁和表级上的锁同时存在

为了支持在不同粒度上进行加锁：

支持一种额外的锁方式，称之为意向锁。
- 意向锁即为表级别的锁
- 目标是为了在一个事务中揭示下一行将被请求的所类型

支持两种意向锁：
- 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
- 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

监控当前事务并分析可能存在的所问题：

6.3.2 一致性非锁定读

InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据

快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。
RC事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
RR事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

6.3.3 一致性锁定读

SELECT…FOR UPDATE
- 对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁
- 对于非一致性非锁定读，已经被执行了SELECT…FOR UPDATE,课是可以被读取的。
SELECT…LOCK IN SHARE MODE
- 对记录的行加一个S锁，再加X锁，会阻塞
以上两句必须在一个事务中，当事务提交了，锁也就释放了。

6.3.4 自增长与锁

插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式称做 AUTO-INC Locking

6.3.5 外键和锁

外键值的插入或更新，首先需要查询父表中的记录
对于父表的select操作，不是使用一致性非锁定读

6.4 锁的算法

6.4.1 行锁的3中算法

Record Lock：单个行记录上的锁
- 总会锁住索引记录
- 没有显示索引，会使用隐式的主键来进行锁定
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

Next-Key Lock ：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

一个索引有10，11，13，20这四个值
（-∞，10）（10，11],(11,13],(13,20],(20,+∞)
当查询的索引含有唯一属性，会降级为Record Lock

drop table if exists t;
create table t (a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;

当查询的索引是辅助索引时：

drop table if exists z;
create table z (a int ,b int,primary key(a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

  
会话A：
  
  ```sql
  select * from z where b=3 for update;

对于聚集索引，其仅对列a等于5的索引加上Record Lock。

对于辅助索引，其加上的时Next-Key Lock，锁定的范围是（1，3），下一个键值加上gap lock，还有一个辅助索引范围为（3，6）的锁。

会话B：

1
2
3

select * from z where a=5 LOCK IN SHARE MODE;
insert into z select 4,2;
insert into z select 6,5;

第一个不能执行，会话A=5的值加上X锁，执行会被阻塞
第二个，主键插入4没问题，插入的辅助索引值2在锁定的范围（1，3），会被阻塞
- 第三个，主键6没有锁定，5也不在范围（1，3），当时在另一个范围（3，6），会被阻塞

可以立即执行：

1
2
3

insert into z select 8,6;
insert into z select 2,0;
insert into z select 6,7;

会检查插入记录的下一条记录是否被锁定

1	INSERT INTO z select 2,2

会阻塞，因为在辅助索引列b上插入值为2的记录是，会监测到下一个记录3已经被索引

关闭Gap Lock：

事务隔离级别设置为READ COMMITTED
- innodb_locks_unsafe_for_binlog设置为1

若唯一索引由多个列组成，查询使用其中一个，属于rang类型查询，依然使用Next-Key Lock，不会降级

6.4.2 解决Phantom Problem（幻读）

Phantom Problem是指在同一个事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

root@MySQL-01 13:30:  [qinxi]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

root@MySQL-01 13:43:  [qinxi]> select * from t where a>2 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

锁住的不是5这单个值，而是对（2，-∞）这个范围加了X锁

第4步时，显示以下内容：

  root@MySQL-01 14:08:  [information_schema]> select * from innodb_trx\G;
*************************** 1. row ***************************
                      trx_id: 42089795
                   trx_state: LOCK WAIT
                 trx_started: 2020-10-21 14:08:53
       trx_requested_lock_id: 42089795:578:4:5
            trx_wait_started: 2020-10-21 14:08:53
                  trx_weight: 3
         trx_mysql_thread_id: 77
                   trx_query: insert into z select 4,4
         trx_operation_state: inserting
           trx_tables_in_use: 1
           trx_tables_locked: 1
            trx_lock_structs: 2
       trx_lock_memory_bytes: 1136
             trx_rows_locked: 1
           trx_rows_modified: 1
     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
  *************************** 2. row ***************************
                      trx_id: 421218363488992
                   trx_state: RUNNING
                 trx_started: 2020-10-21 14:06:52
       trx_requested_lock_id: NULL
            trx_wait_started: NULL
                  trx_weight: 2
         trx_mysql_thread_id: 78
                   trx_query: NULL
         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
  2 rows in set (0.00 sec)
  
  ERROR: 
  No query specified
  
  root@MySQL-01 14:08:  [information_schema]> select * from innodb_locks\G;
  *************************** 1. row ***************************
      lock_id: 42089795:578:4:5
  lock_trx_id: 42089795
    lock_mode: X,GAP
    lock_type: RECORD
   lock_table: `qinxi`.`z`
   lock_index: b
   lock_space: 578
    lock_page: 4
     lock_rec: 5
    lock_data: 6, 7
  *************************** 2. row ***************************
      lock_id: 421218363488992:578:4:5
  lock_trx_id: 421218363488992
    lock_mode: S,GAP
    lock_type: RECORD
   lock_table: `qinxi`.`z`
   lock_index: b
   lock_space: 578
    lock_page: 4
     lock_rec: 5
    lock_data: 6, 7
  2 rows in set, 1 warning (0.00 sec)
  
  ERROR: 
  No query specified
  
  root@MySQL-01 14:08:  [information_schema]> select * from innodb_lock_waits\G;
  *************************** 1. row ***************************
  requesting_trx_id: 42089795
  requested_lock_id: 42089795:578:4:5
    blocking_trx_id: 421218363488992
   blocking_lock_id: 421218363488992:578:4:5
  1 row in set, 1 warning (0.00 sec)
  
  ERROR: 
  No query specified

6.5 锁问题

锁带来的问题：

脏读
不可重复读
丢失更新

6.5.1 脏读

脏页和脏数据是不同的概念。

脏页指的是缓冲池中已经被修改的页，但是还没有刷新到新的磁盘中，数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中。
脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交。

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据。