一. 锁的分类
二. 并发事务问题
事务并发执行会带来各种各样的问题，最大的难点：
- 最大程度利用数据库的并发访问。
- 确保每个用户能以一致的方式读取和修改数据，尤其是一事务进行读取操作，另一个事务同时进行改动操作的情况下。
一个事务在进行读操作，另一个事务进行改动操作，这种情况会带来脏读，不可重复读，幻读。那从锁的层面如何解决呢
1. 方案一：读操作MVCC，写操作进行加锁
事务利用MVCC进行的读取操作成为一致性读（Consistent Read，read view原理），或者一致性无锁读（快照读）。但往往读取的是历史版本数据。所有普通的select在RR，RC隔离级别下都算一致性读。
一致性读并不会对表中的任何记录做加锁操作，其他事务可自由对表中的记录做改动。
采用MVCC方式，读-写操作彼此并不冲突，性能更好，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。
一般情况下，采用MVCC来解决读-写读并发执行问题。但在业务的某些情况，必须要采用加锁方式执行。
2. 方案二：读，写操作都采用加锁方式
适用场景：业务场景不允许读取旧版本，而每次都必须去读取记录的最新版本。
⭐️脏读，不可重复读，幻读通过锁方式的解决
脏读产生原因&解决：事务A读取了另一个事务B未提交的一条记录，若在事务B在写记录时加锁，那么事务A就无法继续读取该记录了，解决脏读。
不可重复读产生原因&解决：事务A读取一条记录，事务B对该记录进行改动后并提交，事务A再次读取时，获取不同值。如果在事务A读取记录时加锁，那么事务B就无法修改记录，解决不可重复读。
幻读产生原因&解决：事务A读取一个范围记录，事务B插入新的记录，导致事务A再次读取记录范围时发现了新记录。采用加锁方式解决幻读问题不太容易，事务A在第一次读取记录时那些幻读记录不存在，所以读取时加锁会有点麻烦。因为不知道给谁加锁。InnoDB通过临健锁进行处理，后续讲。
三. 锁定读
锁定读（Locking Reads）也称为当前读，读取最新版本记录。并对读取的记录进行加锁，阻塞其他事务同时修改相同记录，避免出现安全问题。
当前读种类：
- 共享锁：select … lock in share mode
- 排他锁：select … for update
- update：排他锁
- insert：排他锁
- delete：排他锁
- 串行化事务隔离级别
当前读这种方式，也称为LBCC（基于锁的并发控制，Lock-Based Concurrency Control），实现原理如下
1. 共享锁，排他锁及兼容性
在使用加锁的方式解决的问题时，由于既要允许读-读情况不受影响，又要使写-写，读-写，写-读情况中的操作相互阻塞。
⭐️共享锁（Shared Locks）简称S锁，在事务读取一条记录时，需要先获取该记录的S锁。
事务A获取一条记录的S锁，事务B继续访问该条记录时：
如果事务B想要在获取这个记录的S锁，那么事务B也会获得该锁，事务A，B同时在该记录持有同一把S锁。
-- 事务A
select * from tx_a lock in share mode;
-- 事务B
select * from tx_a lock in share mode;
⭐️排他锁，也称为独占锁（Exlusive Locks）简称X锁，在事务要改动一条记录时，要先获取该记录的X锁。
根据刚才的例子，事务A，B同时拥有共享锁，如果此时事务B还想在获得一个排他锁，此操作会被阻塞，直到事务A提交之后将S锁释放掉。
如果事务A获取一条记录的X锁，那么事务B接着想获取该记录的S锁或X锁都会阻塞，直到事务A提交。
⭐️兼容性问题
| 锁 | S锁 | X锁 |
|---|---|---|
| S锁 | 兼容 | 不兼容 |
| X锁 | 不兼容 | 不兼容 |
2. 锁定读的select语句
MySQL含有2个种比较特殊的select语句格式，分别为排他锁的select，共享锁的select
select ... lock in share mode;
select ... for update;
⭐️共享锁之间锁定读
⭐️共享锁和排他锁之间的锁定读
⭐️排他锁之间的锁定读
⭐️排他锁和共享锁之间的锁定读
⭐️S锁后不能在获取锁的事务，添加X锁
3. 写操作的隐式锁（insert，update，delete）
隐式锁（Implicit Locks）是指在事务中自动产生的锁，这些锁不是显式通过 SQL 语句（如 FOR UPDATE 或 FOR SHARE）请求的，而是由 InnoDB 自动管理的
写操作的锁（隐式锁）：delete，update，insert等（平常用到的）。
⭐️delete
执行步骤：
- 通过B+Tree定位记录位置，获取记录的X锁，并放置X锁（隐式锁）。
- 执行delete mark操作。
⭐️update
对一条记录做update分为3种情况：
-
如果未修改该记录的主键值并且被更新的列占用存储空间地址在修改前后未发生变化时。
定位待修改记录在B+Tree位置的过程是一个获取X锁的锁定读。
- 在B+Tree定位该记录，获取X锁，并放置X锁（隐式锁）。
- 在原记录存储空间地址删除（移入垃圾链表，等待purge线程回收）。
- 在原记录位置进行修改操作。
-
如果未修改记录的主键值并且至少有一个被更新的占用的存储空间地址修改前后发生变化时。
定位待修改记录在B+Tree位置的过程是一个获取X锁的锁定读。
新插入的记录提供的隐式锁保护。
- 在B+Tree定位该记录，获取X锁，并放置X锁（隐式锁）
- 在原记录存储空间地址删除（移入垃圾链表，等待purge线程回收）。
- 插入新记录。
-
如果修改该记录的主键值，相当于在原记录上做
delete操作之后再来一次insert操作，加锁操作就需要按照delete和insert的规则进行。
⭐️insert
-
INSERT操作会对新插入的记录加行锁写锁（排它锁X锁）；注意：区别于执行update，执行insert时这里加的是X锁，并非next-key lock，因此不会阻塞其他的事务对gap区间的插入操作；
-
在插入记录前，会向插入记录所在位置申请意向插入意向锁（Insertion Intention Gap Lock）；只要不是插入的是同一行记录，多个事务对相同gap区间的插入操作不会冲突；
-
对于唯一索引，发生唯一键冲突时，当前事务会先尝试在这条记录上加读锁S锁；加S锁的机制可能会导致死锁：即A线程占用记录的X锁，B、C阻塞，它们需要先获取S锁再获取X锁执行更新；当A释放X锁时，B、C同时拿到S锁（共享锁），但互相都无法继续获取X锁（S锁与X锁互斥），导致死锁；
四. 锁的粒度
前面提到的锁都是针对记录的，可称为行级锁（行锁），对一条记录加锁影响的也只是这条记录而已。那么这个锁的粒度比较细。
其实一个事务也可以在表级别进行加锁。称为表级锁（表锁）。对一个表加锁影响整个表中的记录，那么这个锁的粒度比较粗。
给表加的锁可分为共享锁（S锁）和独享锁（X锁）。
1. 表锁和行锁比较
锁的粒度：表锁 > 行锁
加锁效率：表锁 > 行锁
冲突概率：表锁 > 行锁
并发性能：表锁 < 行锁
2. 给表加S锁
如果一个事务给表加了S锁，那么：
- 别的事务可以继续获取该表的S锁。
- 别的事务可以继续获得该表中的某些记录的S锁。
- 别的事务不可以继续获得该表的X锁。
- 别的事务不可以继续获得该表中的某些记录的X锁。
3. 给表加X锁
如果一个事务给表加了X锁（表级锁，独占表），那么：
- 别的事务不可继续获取该表的S锁。
- 别的事务不可继续获得该表中的某些记录的S锁。
- 别的事务不可获取该表的X锁。
- 别的事务不可继续获得该表中的某些记录X锁。
为了更好的理解表级别的S锁，X锁和后面的意向锁，12
五. 意向锁
它表明事务即将在表的不同行上获取哪种类型的锁。意向锁的目的是为了在事务检查锁是否兼容之前，减少检查行级锁的次数。
意向锁有两种类型：
- 意向共享锁（Intention Shared Lock）简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加个IS锁。
- 意向独占锁（Intention Exclusive Lock）简称IX锁。当事务准备在某条记录上加X锁时，需要现在表级别加一个IX锁。
IS，IX锁是表记锁，他们提出仅仅为了咋i之后加表级别的S锁和X锁时可快速判断表中的记录时否被上锁，以避免用遍历的方式查看表中有没有上锁的记录。就是说其实IS锁和IX锁，IX锁和IX锁都是互相兼容的。
X，S，IX，IS兼容性：
| 兼容性 | X | IX | S | IS |
|---|---|---|---|---|
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| IX | 不兼容 | 不兼容 | ||
| S | 不兼容 | 不兼容 | ||
| IS | 不兼容 |
锁的组合性（意向锁没有行锁）：
| 组合性 | X | IX | S | IS |
|---|---|---|---|---|
| 表锁 | 有 | 有 | 有 | 有 |
| 行锁 | 有 | 有 |
六. 行锁和表锁
MySQL支持多种存储引擎，不同存储引擎对锁的支持也不一样。
1. InnoDB存储引擎表级锁
1）表级别的S锁，X锁，元数据锁
进行select，inser，delete，update语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁。
另外，在对某个表执行一些如alter table，drop table 这类DDL语句时，其他事务对这个表并发执行select，insert，delete，update对语句会发生阻塞，同理，某个事务中对某个表执行select，insert，delete，update语句时，其他会话中对这个表执行DDL语句也会发生阻塞。这个过程是通过server层使用一种称为元数据锁（Metadata Locks，简称为MDL）来实现，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。
InnoDB存储引擎提供的表级S锁或X锁，使用场景比较少。只会在一些特殊情况下，比方说崩溃恢复过程中用到。不过我们还是可以手动获取一下，比方说在系统变量autocommit=0，innodb_table_locks=1时，手动获取InnoDB簇出引擎提供的表的S锁或X锁可以这么写：
-- read: InnoDB存储引擎会对表table加表级别的S锁
-- write: InnoDB存储引擎会对表table加表级别的X锁
lock tables tables [read|write];
请尽量避免在使用InnoDB存储引擎的表上使用lock tables这样的手动锁表语句，它们并不会提供什么额外的保护，只是降低并发性能而已。
2） 表级别的IS锁，IX锁
当我们对使用InnoDB存储引擎的表哦的某些记录加S锁之前，那就需要现在表级别加一个IS锁，当我么你对使用InnoDB存储引擎的表某些记录加X锁之前，那就需要现在表级别加一个IX锁。
IS锁和IX锁的作用：为了后续在加表级别的S锁和X锁时判断表中是否已经被加锁的记录，以避免用遍历了方式来查看表中有没有上锁记录。
我们并不能手动添加意向锁，只能有InnoDB存储引擎自行添加。
3）表级别的AUTO-INC锁
在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动给AUTO_INCREMENT修饰的列递增赋值的原理主要2个：
- 采用
AUTO-INC锁：执行插入语句，在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增值，在该语句执行结束后，再把AUTO-INC锁释放掉，此时在持有AUTO-INC锁时，其他事务的插入语句都要会被阻塞，可以保证一个语句中的分配的地址是连续的。 - 采用一个轻量级的锁，为插入语句生成
AUTO_INCREMENT修饰的列的值时，获取这个轻量级锁，然后生成本次插入语句的AUTO_INCREMENT列的值之后，就把轻量级锁释放，并不需要等到整个插入语句执行完释放锁。
InnoDB提供innodb_autoinc_lock_mode的系统变量来控制到底使用上述两种方式中的哪种方式为AUTO_INCREMENT进行赋值
innodb_autoinc_lock_mode值
| 值 | 作用 |
|---|---|
| 0 | AUTO-INC锁 |
| 1 | 轻量级锁 |
| 2 | 两种方式混合用（插入数量确定时采用轻量级锁，不确定时使用AUTO-INC锁） |
当innodb_autoinc_lock_mode值为2时，可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的，在有主从复值的场景中是不安全的。
⭐️sql查询
-- MySQL 默认是混合使用
show variables like 'innodb_autoinc_lock_mode';
2. InnoDB存储引擎行级锁
行锁，也称为记录锁，在记录上加的锁。但需要注意，这个记录指的是通过给索引上的索引项加锁。（InnoDB 会在索引结构上加锁以保护数据的一致性和事务的隔离性。这里的“索引”实际上是指索引条目，也就是索引树中的节点，它们包含了指向实际数据行的指针。）
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB使用表锁。
不论使用主键索引，唯一索引或者普通索引，InnoDB都会使用行锁对数据加锁。
执行计划里使用了索引，才会使用了行级锁。即使在搜索条件使用了索引字段，但是否使用索引来检索数据是有MySQL通过判断不同执行计划的代价来决定，如果MySQL认为全表扫描效率高，比如对一些小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。
同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。
即使是行锁，InnoDB也分成各种类型。换句话说即使对同一条记录加行锁，如果类型不同，起到的功效也不同。
1）记录锁
记录锁（Record Locks），仅仅把一条记录锁上。
主要作用是控制对数据库资源的并发访问，防止同一时刻对同一数据进行多个操作，从而确保数据的一致性和完整性。用于确保事务的一致性和隔离性。
官方类型名称：LOCK_REC_NOT_GAP。比方说我们把val值为500的加一个记录锁示意图如下：
-- 加索引生效行锁
create index idx_val on tx_a (val);
-- X型记录锁
SELECT * FROM tx_a WHERE val = 500 FOR UPDATE;
-- S型记录锁
SELECT * FROM tx_a WHERE val = 500 LOCK IN SHARE MODE;
SELECT * FROM tx_a WHERE val = 500 FOR SHARE;
记录锁分为S锁，X锁。当一个事务获取了一条记录的S型记录锁后，其他事务也可继续获取该记录的S型记录锁，但获取不了该记录的X型记录锁。当一个事务获取了X型记录锁后，其他事务不能获取该记录的，X型，S型的记录锁。
2）间隙锁
MySQL在REAPEATABLE READ隔离级别下是可以解决幻读问题，解决方案有两种：
- 可以使用MVCC解决。
- 加锁
但在使用加锁方案解决时有问题，事务在第一次执行读取操作时，那些幻读记录上尚不存在，是无法给这些幻读记录加上记录锁。InnoDB提出间隙锁的概念，官方的类型名称为：LOCK_GAP，简称gap锁。
间隙锁的实质是对索引前后的聚簇索引间隙上锁，不对索引本身上锁。
⭐️表结构及数据
⭐️非聚簇索引与间隙锁
聚簇索引作为搜索条件，进行更新操作，不会添加间隙锁。
只有非聚簇索引，并当索引生效，才会生效间隙锁。
列子如下
-- 加索引生效行锁
create index idx_val on trx_a (val);
对事务A进行更新，那么通过val找到聚簇索引，通过B+Tree找到上下行记录，并对范围内上锁，并不包含val=500的行，对索引上下(1,5)，(5,10)之间上间隙锁。
事务B进行插入，则阻塞。
对索引上下(1,5)，(5,10)之间上间隙锁。不允许中间插入数据。
⭐️聚簇索引与间隙锁
对于聚簇索引，情况稍微复杂一些。聚簇索引包含了所有的列数据，因此每个数据行在聚簇索引中都有一个物理位置。当你使用聚簇索引执行范围查询时，InnoDB 仍然会使用间隙锁来锁定范围内的空隙，但这通常是隐式的。
-- 独占锁的间隙锁
select * from tx_a where id >= 1 and id <= 5 for update
-- 共享锁的间隙锁
select * from tx_a where id >= 1 and id <= 12 for share;
3. 其他存储引擎中的锁
对于MyISAM，MEMORY，MERGE这些存储引擎，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。
比方说Session 1中对于一个表执行SELECT操作，就相当于对表加了一个表级别的共享锁（S锁），如果select操作未完成时，Session 2中对这个表执行update操作，相当于获取表的X锁，此操作会阻塞，直到Session 1中的select操作完成，释放掉表级的S锁后，Session 2中对这个表执行update操作才能继续获取X锁，进行后续更新操作。
因为使用MyISAM，MEMORY，MERGE这些存储引擎的表在同一时刻只允许一个会话对表进行写操作，所以这些存储引擎一般用在读，或者大部分都是读操作，或者单用户情景下。
另外，MyISAM存储引擎中有一个Concurrent Inserts的特性，支持在对MyISAM表读取时同时插入记录，这样可以提升一些插入速度，更多细节参考官方文档。
七. 死锁
1. 概念
MySQL中的死锁是指两个或多个事务在同一资源集上相互占有资源，而又都在等待其他事务释放资源，导致它们之间相互等待，无法向前推进的情况。死锁会导致所有相关事务都无法继续执行，并可能导致数据库崩溃。
产生死锁：
-
事务同时更新多个表
当一个事务同时更新多个表并且使用了不同的顺序，可能会导致死锁的发生。例如，事务 A 首先更新表 X，此时获取到了 X 表的锁，并在未释放该锁的情况下尝试更新表 Y；而事务 B 首先更新表Y，此时获取到了 Y 表的锁，并在未释放锁的情况下尝试更新表 X。这种情况下，两个事务会相互等待对方的锁释放，从而形成死锁。
-
事务嵌套
当一个事务内部开启了另一个事务，并在内层事务中更新了某个表，而外层事务也需要更新该表的同一行记录时，就有可能发生死锁。因为外层事务需要等待内层事务释放锁，而内层事务需要等待外层事务释放锁。
-
索引顺序不一致
当多个事务按照不同的顺序访问相同的数据行，并且使用了不同的索引时，可能会发生死锁。例如，事务 A 按照索引 1 的顺序访问数据行，事务 B 按照索引 2 的顺序访问同一组数据行，这样两个事务之间就会产生死锁。
-
不同事务同时更新相同的索引
当多个事务同时更新相同的索引时，可能会导致死锁。这是因为事务在更新索引时会获取对应的锁，并在未释放锁的情况下尝试更新其他数据，从而形成死锁。
⭐️死锁产生场景示例：
会话1：
begin;
select * from tx_a where val = 100 for update;
会话2：
begin;
select * from tx_a where val = 200 for update;
会话1：
select * from tx_a where val = 200 for update;
这时会话1语句会被阻塞。产生死锁。
MySQL检测到了死锁，并结束了会话2中事务的执行，此时，切回会话1，发现原本阻塞的SQL语句执行完成了。
2. 分析死锁
查看死锁信息：
show engine innodb status\G
查看事务加锁的情况，不过一般情况下，看不到哪个事务对哪些记录加了那些锁，需要修改系统变量innodb_status_output_locks（MySQL5.6.16引入），默认OFF。
show variables like 'innodb_status_output_locks';
-- 改为on
set variables like 'innodb_status_output '
3. 避免死锁
- 缩小写数据的范围，创建索引，即降低锁粒度
- 缩短持有锁的时间
- 在相同的事务中，用相同的操作顺序给资源加锁（死锁的发生条件之一就是两个事务用相反的顺序给资源加锁）
4. 解决死锁
当两个或更多事务互相持有对方所需的锁，形成循环依赖关系时，就会发生死锁。InnoDB 会检测到这种情况，并自动选择一个事务进行回滚，以便其他事务可以继续执行。
⭐️配置选项
-
innodb_lock_wait_timeout：
-
这个系统变量设置了事务等待锁的时间。如果在等待时间内未能获取锁，事务将被回滚。默认值通常设置为 50 秒。
-
配置示例
1SET GLOBAL innodb_lock_wait_timeout = 10; -
这个设置可以全局或会话级别更改。
-
-
innodb_deadlock_detect：
-
这个系统变量控制是否启用死锁检测。默认情况下，死锁检测是开启的。
-
配置示例
1SET GLOBAL innodb_deadlock_detect = OFF; -
通常情况下，不建议关闭此选项，因为它会导致长时间的事务挂起。
-
-
innodb_deadlock_detect 的值：
- ON：启用死锁检测。
- OFF：禁用死锁检测。
- ALL：除了检测死锁外，还记录所有死锁信息。
⭐️死锁解决策略
InnoDB 采用一种策略来解决死锁，通常会选择代价最小的事务进行回滚。代价（cost）是基于事务的行锁数量、事务大小以及其他因素计算得出的。
⭐️死锁日志
当 InnoDB 解决死锁时，它会生成一条包含有关死锁信息的日志条目。这些信息可以帮助诊断和理解死锁的原因。日志信息通常包含：
- 发生死锁的事务 ID。
- 涉及的锁和锁定的资源。
- 事务的状态。
⭐️配置示例
如果你想调整死锁检测和处理的行为，可以使用以下命令：
1-- 设置等待锁的超时时间为 30 秒
2SET GLOBAL innodb_lock_wait_timeout = 30;
3
4-- 禁用死锁检测（不推荐）
5SET GLOBAL innodb_deadlock_detect = OFF;
6
7-- 开启死锁检测并记录所有死锁信息
8SET GLOBAL innodb_deadlock_detect = ALL;
- innodb_lock_wait_timeout 控制事务等待锁的超时时间。
- innodb_deadlock_detect 控制是否启用死锁检测。
- InnoDB 默认会自动解决死锁，选择代价最小的事务进行回滚。
- 死锁日志 提供有关死锁的信息，有助于诊断问题。
通过以上调整这些配置选项，你可以根据你的应用需求来优化事务处理和并发控制。通常情况下，建议保持死锁检测开启，并根据实际情况调整等待超时时间。
8249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
