基于存储引擎的MYSQL锁问题

1. 概述

不同的引擎支持不同的锁机制

1. MyISAM 表级锁
2. MEMORY 表级锁
3. RDB 表级锁+页面锁
4. InnoDB 表级锁+ 行锁

表级锁

开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

行级锁

开销大，加锁慢；会出现死锁，锁的粒度最小，发生锁冲突的概率最低，并发度最高。

页面锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁的冲突和并发度介于行锁和表锁之间。

锁的选择要依据具体情况而定

1. 表锁更适合以查询为主，只有少量按照索引更新数据的应用。
2. 行锁更适合以更新为主，有大量按照索引并发更新少量不同数据的应用。

2. MyISAM表锁

2.1 查询表级锁争用情况

show status like 'table%'
Table_locks_waited表示正在等待表锁的线程数量
Table_locks_immediate 表示正在执行过程中的表锁

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 118   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.05 sec)

MyISAM读锁-读锁之间的并行的。
MyISAM读锁-写锁，写锁-写锁之间的串行的。

2.2 加锁方式

**隐式加锁：**MyISAM在执行select之前会隐式的给涉及的表加上读锁，在执行更新操作update、delete、insert之前会隐式的给涉及到的表加写锁。
**显示加锁：**大多是为了检测数据读写的一致性问题。

2.3 并发插入

1. 在一定条件下，MyISAM表也支持查询和插入的并发进行。
2. MyISAM存储引擎有一个系统变量concurrent_insert，专门控制并发插入的行为。
   2.1 0,不允许并发插入。
   2.2 1,默认设置，允许在表中没有空洞的情况下，从表尾插入记录。
   2.3 2,无论表中有没有空洞，都允许在表尾插入记录。

2.4 MyISAM锁的调度

当一个线程请求表的读锁，另一个线程请求同一张表的写锁时应该如何解决

写请求优先在读请求之前，这是因为MySQL一般认为写请求的任务比读请求重要的原因。

可以通过设置max_write_lock_count来实现，当写锁的数量达到这一数目之后就会降低写锁的优先级，从而给读锁一定的机会。

3. InnoDB锁

InnoDB与MyISAM的最大不同

1. InnoDB存储引擎增加了事务的支持。
2. InnoDB 增加了行锁的支持。

3.1 事务及ACID属性

1.事务

事务是由一组SQL语句组成的逻辑处理单元，要么都执行、要么都不执行。
ACID不做过多赘述

1. 原子性 Atomicity
2. 一致性 Consistent
3. 隔离性 Isolation
4. 持久性 Durable

2.并发带来的问题

1. 更新丢失，最后的更新覆盖了其他事务的更新。
2. 脏读，读取到了其他事务未提交的数据。
3. 不可重复读，再次读取之前读取过的数据后发现，数据已被该更改或者删除。
4. 幻读。按相同条件重新检索之前已经读取过的数据，发现其他事务插入了满足那不足条件的数据。

3.事务的隔离级别

	读取数据一致性	脏读	不可重复读	幻读
未提交读	最低级别	是	是	是
已提交读	语句级别	否	是	是
可重复读	事务级别	否	否	是
序列化	*别，事务级	否	否	否

3.2 获取InnoDB行锁的竞争情况

可以通过InnoDB_row_lock检查行锁的使用与竞争情况。

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

3.3 InnoDB行锁模式及加锁方法

1.InnoDB实现了两种类型的锁

1. 共享锁(S),即读锁
2. 排他锁(X)，即写锁

2.为了实现表锁与写锁共存，实现多粒度的锁机制，InnoDB还有两种内部使用的意向锁

1. 意向共享锁(IS),打算给数据行加共享锁，事务在给数据行加共享锁之前必须先获取该数据行的意向共享锁。
2. 意向排他锁(IX),打算给数据行加排他锁，事务在给数据行加上排他锁之前必须获得该数据行的意向排他锁。

3.行锁模式的兼容性

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

4.显示加锁

1. 意向锁以及排他锁、共享锁都是存储引擎自动加的。
2. 用户也可以自己显示的手动加锁。

显示共享锁

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

显示排他锁

SELECT * FROM table_name WHERE ... FOR UPDATE

3.4 InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有创建索引，InnoDB将通过隐藏的聚簇索引来对记录加索引。

1. Record Lock,记录锁，对索引项加锁。
2. Gap Lock,对索引项之间的”间隙“加锁，第一条记录前的”间隙“或者最后一条记录之后的”间隙“加锁。
3. NEXT-KEY lOCK,前面两种锁的组合，对记录及其间隙加锁。
4. Tips, MVCC及NEXT-KEY锁用以解决可重复读隔离级别下的幻读问题。
5. InnoDB这种加锁方式意味着，如果不通过索引条件检索，InnoDB将对表中的所有记录加锁，实际效果类同于表锁。

2.加锁过程中的一些问题

1. InnoDB这种加锁方式意味着，如果不通过索引条件检索，InnoDB将对表中的所有记录加锁，实际检索效果类同于表锁。
2. 由于MySQL行锁是针对索引加的锁，而不是针对记录加的锁，所以在访问不同行的记录时，如果使用的是相同的索引键，是会出现锁冲突的。
3. 当表中有多个索引时，不同的事务会使用不同的索引为不同的行加锁。
4. 即使在检索条件中使用索引，是否使用索引将由mysql判断执行计划的代价决定，MySQL最终会选择执行效率高的方法。

3.5 Next-key Lock

1.当范围检索数据时，InnoDB都会给符合条件的已有数据记录索引项加锁。对于键值条件在范围内但是并不存在的数据，称为”间隙“，InnoDB也会对”间隙“加锁。这种锁机制就是NEXT-KEY锁。

例如：加入数据项只存在1-100的情况下

Select * from table_name where id > 100;

2.使用目的

1. 解决幻读问题。
2. 满足恢复和复制的需要。

3.缺点

1. 范围加锁会引发严重的锁等待，致使阻塞。

3.6 恢复和复制

MySQL有三种日志模式

1. binLog
2. redoLog
3. undoLog

MySQL有四种复制模式

1. 基于SQL语句的复制SBR
2. 基于行数据的复制RBR
3. 混合复制模式：对安全的SQL语句采用SBR，对不安全的SQL语句采用RBR模式。
4. 基于全局事务ID的复制模式，只要是解决主从自动同步一致问题。

3.7 表锁的使用

1. 在原始表非常大的情况下需要更新大部分数据。
2. 事务涉及多个表，比较复杂，很可能引起死锁的情况。

本文地址：https://blog.csdn.net/weixin_44713668/article/details/110697201