深入理解MySQL——锁的概念

系统状态包含了以某些方式联系在一起的实体。这些联系被描述为实体的不变式（invariant）。
实际上，只有当实现本身会增加开销时，行级锁才会增加开销。InnoDB存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。

锁的类型

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

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

	X	S
X	冲突	冲突
S	冲突	兼容

InnoDB存储引擎支持两种意向锁∶

• 意向共享锁（IS Lock），事务想要获得一个表中某几行的共享锁。
• 意向排他锁（IXLock），事务想要获得一个表中某几行的排他锁。
  因为InnoDB存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表扫以外的任何请求。

锁相关的表

//MySQL5.7以后板凳才看得到
mysql> use information_schema;
Database changed
mysql> desc  INNODB_TRX;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |
| trx_state                  | varchar(13)         | NO   |     |                     |       |
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |
| trx_wait_started           | datetime            | YES  |     | NULL                |       |
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_is_read_only           | int(1)              | NO   |     | 0                   |       |
| trx_autocommit_non_locking | int(1)              | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.00 sec)

• trx_id∶InnoDB存储引擎内部唯一的事务ID。
• trx_state∶当前事务的状态。
• trx_started∶ 事务的开始时间。
• trx_requested_lock_id∶等待事务的锁ID。如trx_state的状态为LOCK WAIT，那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCK WAIT，则该值为NULL。
• trx_wait_started∶ 事务等待开始的时间。
• trx_weight∶事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小的进行回滚。
• trx_mysql_thread_id∶MySQL中的线程ID，SHOW PROCESSLIST显示的结果。
• trx_query∶事务运行的SQL语句。在实际使用中发现，该值有时会显示为NULL
  （不知道是不是Bug）。一个具体的例子如下∶

mysql> desc INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |
| lock_trx_id | varchar(18)         | NO   |     |         |       |
| lock_mode   | varchar(32)         | NO   |     |         |       |
| lock_type   | varchar(32)         | NO   |     |         |       |
| lock_table  | varchar(1024)       | NO   |     |         |       |
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

• lock_id∶锁的ID。
• lock_trx_id∶事务ID。
• lock_mode∶锁的模式。
• lock_type∶锁的类型，表锁还是行锁
• lock_table∶要加锁的表。
• lock_index∶锁的索引。
• lock_space∶InnoDB存储引擎表空间的ID号。
• lock_page：被锁住的页的数量。若是表锁，则该值为NULL。
• lock_rec∶被锁住的行的数量。若是表锁，则该值为NULL。
• lock_data：被锁住的行的主键值。当是表锁时，该值为NULL。

mysql> desc INNODB_LOCK_WAITS;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO   |     |         |       |
| requested_lock_id | varchar(81) | NO   |     |         |       |
| blocking_trx_id   | varchar(18) | NO   |     |         |       |
| blocking_lock_id  | varchar(81) | NO   |     |         |       |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• requesting_tx_id∶申请锁资源的事务ID
• requesting_lock_id∶申请的锁的ID。
• blocking_trx_id∶阻塞的事务ID。
• blocking_trx_id∶阻塞的锁的ID。

一致性的非锁定行读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。

锁的概念

并发控制法则

1. 并发执行不应导致程序的失效。
2. 相比于串行执行，并发执行不应有更低的吞吐量和更长的响应时间。

事务隔离级别

1. read uncommitted
2. read committed 解决脏读，不解决不可重复读
3. repeatable read 解决幻读
4. serializable 序列化得事务隔离级别，适用于分布式

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

• 脏读
• 不可重复读
  两阶段加锁，读和写都加锁
• 幻读：连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。读到之前不存的的记录
  不可重复读：读到结果不一样

InnoDB存储引擎有3中行锁的算法设计，分别是：

• Record Lock∶单个行记录上的锁。
• Gap Lock∶ 间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock∶Gap Lock＋Record Lock，锁定一个范围，并且锁定记录本身。

SQL标准定义的四个隔离级别为：

• QREAD UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

READ UNCOMMITTED称为浏览访问（browse access），仅仅只对事务而言的。 READ COMMITTED称为游标稳定（cursor stability）。REPEATABLE READ是2.9999°的隔离，没有幻读的保护。SERIALIZABLE称为隔离，或3°。SQL和SQL 2标准的默认事务隔离级别是SERIALIZABLE。

InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因此避免幻读的产生。与其他数据库系统（如Microsoft SQL Server数据库）是不同的。所以说，InnoDB存储引整在默认REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SOL标准的SERIALIZABLE隔离级别。

隔离级别越低，事务请求的锁越少，或者保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是READ COMMITTED。

RR 事务隔离级别insert性能是一个问题，并行性是个问题
RR事务隔离级别插入时需要等待
锁住的是2,3也不能插入，innodb，为了解决幻读问题
RC好理解，只锁着记录本身2.99度
锁的算法有七层
意向锁
自增锁：MySQL中有自增列
命令行创建表时创建自增列时不创建唯一索引时会报错

mysql> create table ai (a int auto_increment, b int , key(b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table ai (a int auto_increment, b int);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table ai (a int auto_increment, b int, key(a,b));
Query OK, 0 rows affected (0.00 sec)
mysql> 

复合索引，
启动时要获取自增值
锁的概念 o自增锁
●一个表一个自增列。

AUTO_INCREMENT PK
·SELECT MAX((auto_inc_col)FROMt FOR UPDATE;

●在事务提交前释放
o 其他锁在事务提交时才释放。 Think about
o INSERT … SELECT…
自增锁是一个lath，执行完就释放了

insert into ai(a,b) select NULL,o_orderkey from orders limit 1000;

执行这个条语句时产生的锁，对select NULL,o_orderkey from orders limit 1000;这个列进行加锁，锁的时间是这条SQL执行完。自增锁持有是这个SQL执行时间

pager less

大事务insert 会阻塞

mysql> show variables like 'innodb%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
| innodb_autoinc_lock_mode    | 1     |
| innodb_stats_auto_recalc    | ON    |
+-----------------------------+-------+
3 rows in set (0.00 sec)

在MySQL中事务隔离设置为RC，二进制设置为roll，MySQL5.7默认为roll
单调递增，id不是连续自增

innodb_autoinc_lock_mode 2 

查看锁的详细信息

SET GLOBAL innodb_status_output_locks=ON;

innodb 中的锁
MySQL
锁与并发
实现并发控制
事务的隔离
事务的序列化
并发控制准则
并发控制访问程序不出错
比单线程要快
隔离性:
脏读：
不可重复读：
两阶段加锁：读和写都加锁
默认的是rc 解决的是脏读，不解决重复读，和幻读
innodb 事务隔离级别repeatable read被称为2.99度，接近3.
事务隔离级别为0,1,2,3

为什么开启了分布式事务，隔离级别设置为serializable？

什么是幻读？什么是不可重复读？

幻读：连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。同一条SQL读到的记录数量不一样。会有新的记录插进来
不可重复读：同一条记录结果不一样。写过修改了

为什么用的是rc事务隔离级别？它只解决了脏读，不解决幻读和不可重复读？

• 事务请求的锁越少或者保持锁的时间就越短，持有的时间比较短，开销小一点；
• 不解决幻读和不可重复读，真正应用中不是个问题；
• 并发时会加上for update，锁住
• 隔离性被破坏，在实际应用中不是那么重要

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolatin level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

建议设置为rc

innodb锁的算法

对一条记录加锁，就是对索引进行加锁

pk	2	4	6	8	说明
锁类型	唯一索引列
reard lock	2 X
gap lock	(-无穷， 2) X
next key lock	(-无穷，2] X

gap lock 和 reard lock兼容的
next key lock和上面两个都是不兼容的
锁的算法之间是有兼容性的

RR 事务隔离级别表示的是所有的对某一条记录加锁都是使用next key lock算法的。使用RR，insert并行性可能是个问题。
例如：
线程1：update记录2 没有提交
线程2：insert 记录1 wait
查看数据的事务隔离级别

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

RR 事务隔离级别
RR 使用锁的算法nextkey locking
MySQL默认优化record lock 前提是index是unique唯一的，且只返回一条记录
RC 使用锁的算法record lock

RR事务隔离级别
锁住的是 2 ，3也不能插入。

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

mysql> select * from l where a<=2 for update;
+---+---+------+------+
| a | b | c    | d    |
+---+---+------+------+
| 2 | 4 |    6 |    8 |
+---+---+------+------+
1 row in set (0.00 sec)

mysql> inset into l (3,5,7,9);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inset into l (3,5,7,9)' at line 1
mysql> insert into l value(3,5,7,9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

RR 解决幻读
RR锁的范围要比想象的大的多。
将事务隔离级别设置为RC

mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看当前会话线程id

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|             106 |
+-----------------+
1 row in set (0.00 sec)

新插入相同的数据，是插在相同记录后面
关键看这条记录上面锁的算法

打开锁详细日志

mysql> set global innodb_status_output_locks=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

mysql> select * from l;
+---+----+------+------+
| a | b  | c    | d    |
+---+----+------+------+
| 2 |  4 |    6 |    8 |
| 4 |  6 |    8 |   10 |
| 6 |  8 |   10 |   12 |
| 8 | 10 |   12 |   14 |
+---+----+------+------+
4 rows in set (0.00 sec)

RR事务隔离级别下

//回话一
mysql> select * from l where b=6 for update;

//会话2，等待超时
mysql> insert into l value(3,4,20,40);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

//插入成功
mysql> insert into l value(1,4,30,50);
Query OK, 1 row affected (0.00 sec)

key (4,2), (6,4)],((6,4),(8,6))
(4,1)不在范围内
(4,3)在范围内容
2,4,4,6是主键

二级索引锁住了主键
heap no 2 才是用户的列

//查询d=12的记录
select * from l where b=12 for update;

锁住的是（10， +无穷）
10 到正无穷都无法插入
没有任何算法的提示默认的是next key lock 锁
RC 事务隔离级别
默认设为row

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

表示的是删除的每一行的的记录。每一行的记录操作
row MySQL5.1才开始支持
delete 一百万的数据，后面的数据已经插进来了，看不到
事务id是自增的

怎么查看事务是否可见？

1. 当前有一个叫事务活跃列表
2. 只要开启一个是会产生一个read views，会被拷贝下来
3. read views 是一个内存对象，完全不可见的
   RC 没执行一条SQL语句就会创建一个read views 对象
   RR read views 创建一次，RR可重复读
   因为RC 可以到已经提交的事物记录
   不在事务列表中不可见，在活跃事务列表中是可见的
   RR 创建read views 一次
   RC 创建read views N次
   全是select SQL的话，RR性能比RC性能好
   不要有大量insert，update操作RR性能比RC性能好

事务id什么时候分配的？

begin模式 read views 第一次执行SQL才去创建。
执行这个会在语句，会在开始的时候创建read views

//搭配RR事务隔离使用
mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

事务id是在执行时才创建
read views 内存对象看不到的

插入意向锁

gap lock
RC 事务隔离级别

//回话1
//没有创建锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into l value(16,18,20,22);
Query OK, 1 row affected (0.00 sec)

//会话2
//创建锁，切锁等待超时
mysql> select  * from l where a=16 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

隐式锁，这条记录不用加锁我就知道上面有锁。

innodb

• 显示锁
• select
• 隐式锁
  不创建锁对象若没有锁冲突
  隐式锁转换为显式锁
• 锁开销进一步下降
  开销几乎很少
  延迟创建锁的对象，如果在延迟过程中没有对记录进行加加锁的话就不用创建锁的对象，减少内存开销

RR 事务隔离级别模式

gap before rec insert intention 锁，本身是一个gap锁

一条记录看它能不能插就看他后面有没有gap锁
判断当时事务能不能插入，但是不阻塞后面的insert操作
gap before rec insert intention他不阻塞其他线程的插入操作，只阻塞当前事务，用来判断当前事务能不能插入
insert 不阻塞是没有锁的，被阻塞才会阻塞。
每个事务每个页一个锁对象
10字节
锁重用
死锁
两个或两个以上的事务在执行过程中因争夺锁资源而造成的的一种相互等待的现象
AB-BA
解决死锁
超时
innodb_lock_timeout
wait-for graph
自动死锁检测
数据库不采用锁超时来解决死锁
锁的信息链表
事务等待链表
undo 回滚

//记录所有死锁信息参数
mysql> show variables like '%innodb%dead%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_deadlock_detect     | ON    |
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
2 rows in set (0.00 sec)

innodb_print_all_deadlocks 设置为ON 记录所有死锁到日志。
MySQL 5.7 允许MySQL不检测死锁

set global innodb_deadlock_detect=0;

锁超时不会滚

什么情况下需要关闭死锁检测？

在秒杀的场景之下。

MySQL 8.0

select *  from stock where skuId=1 for update nowait;

select * from stock where skuId=1 for update skip locked;