聊聊Mysql锁问题

呆呆的私房菜

已于 2024-05-11 11:03:20 修改

阅读量1k

点赞数 22

分类专栏： MYSQL 文章标签： mysql

于 2024-05-11 11:02:41 首次发布

本文链接：https://blog.csdn.net/m0_50880099/article/details/138684069

版权

MYSQL 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

聊聊Mysql锁问题

一、 Mysql锁概述
二、Myisam锁问题
三、InnoDB锁问题

一、 Mysql锁概述

相较于其他数据库而言，Mysql锁机制比较简单，不同的引擎支持不同的锁机制。

Myiasm和Memory存储引擎采用的是表级锁
BDB存储引擎采用的是页面锁，但也支持表级锁
InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况采用行级锁

三种锁的种类归纳如下：

表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也很高。
页面锁：开销和加锁时间介于行锁和表锁之间，会出现死锁，锁定粒度位于行锁和表锁中间，并发度一般。

锁适用范围：

表级锁：更适合查询为主，只有少量按索引条件更新数据的应用，如web应用；
行级锁：更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如OLTP系统；

二、Myisam锁问题

2. 1 表级锁的锁模式

表共享读锁
表独占写锁

对Myisam表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作。
对Myisam的写操作，会阻塞其他用户对同一表的读和写操作；
Myiasm表的读操作和写操作之间是串行的！

2.2 如何加表锁

lock table table_name read local;
select xx from table_name;
unlock table_name;

上述案例加上local选项，作用是满足myisam表并发插入条件的情况下，允许其他用户在表尾并发插入记录。
在用lock tables给表显式加锁时，必须同时取得所有涉及到表的锁，并且Mysql不支持锁升级。也就是在执行lock tables后，只能访问显式加锁的这些表，不能访问未加锁的表。
如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。
当lock tables时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错。

2.3 并发插入

myisam表的读和写是串行的，但在一定条件下，myiasm表也支持查询和插入的并发执行。
myisam存储引擎有一个系统变量concurrnet_insert，专门用以控制其并发插入的行为，其值分别可以为0、1、2.

当设置为0时，不允许并发插入；
当设置为1时，如果myisam表中没有空洞（即表的中间没有被删除的行），myisam允许在一个进程读表的同时，另一个进程从表尾插入数据。（默认设置）
当设置为2时，无论myisam有无空洞，都允许在表尾并发插入记录。

可以利用myisam存储引擎的并发插入特性，来解决应用中对于同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设置为2，总是允许并发插入；同时通过定期在系统空闲时间段执行optimize table语句来整理表空间碎片，收回因删除记录而产生的中间空洞。

2.4 Myisam的锁调度

myisam存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么一个进程请求某个myisam表的读锁，同时另一个进程也i请求同一张表的写锁，此时Mysql怎么处理？
写进程会先获得锁，即使读请求先到锁等待队列，写请求后到，写锁也会插入到读锁之前！这是因为Mysql认为写请求一般比读请求更重要！这也是myisam表不太适合有大量更新操作和查询操作应用的原因。因为大量的更新操作会造成查询操作的阻塞。
当然mysql提供了一些参数可以调节myisam的调度行为：

通过指定启动参数low-priority-updates，是myisam引擎默认给予读请求以优先的权利。
通过执行命令set low_priority_updates=1，使该连接发出的请求更新优先级降低/
通过指定insert、update、delete语句的low_priority属性，降低该语句的优先级。
通过设置系统参数max_write_lock_count的值，当表锁达到某个值之后，暂时降低写请求的优先级。

注意：应用中应该尽量避免长时间运行的查询操作，在可能的情况下使用中间表等措施对SQL做一定程度的分解，使得每一步查询都能在较短的时间内完成，从而减少锁冲突。
如果复杂查询不可避免，应尽可能的安排在数据库空闲时间段内执行，比如一些定期统计可以在夜间执行。

三、InnoDB锁问题

InnoDB和Myisam的最大不同点：

支持事务
采用了行级锁

3.1 事务及ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具备ACID 4个属性。

原子性：事务是一个原子操作单元，对数据的修改，要么全部执行，要么全部不执行；
一致性：在事务开始和完成时，数据都必须保持一致状态；
隔离性：系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境执行；
持久性：事务完成之后，对于数据的修改是永久性的。

3.2 并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增强数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务也会带来如下几个问题：

更新丢失：两个事务选择了同一行，最后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题；
脏读：一个事务正在对一条记录做修改，在这个事务完成并提交前，这奥记录的数据就处于不一致状态，此时另一个事务也来读取同条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做了进一步的处理，就会产生未提交的数据依赖关系；
不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前的数据，却发现读取的数据发生了改变，或者这些记录已经被删除了；
幻读：一个事务按相同查询条件重新读取以前检索的数据，却发现其他事务插入了满足其查询条件的新数据。

3.3 事务隔离级别

上面提到的并发事务带来的问题必须由数据库提供一定的事务隔离机制来解决，数据库实现事务隔离的方式，基本上分为两种：

在读取数据前，对其加锁，防止其他事务对数据进行修改；
不用加任何所，通过一定机制生成一个数据请求时间点的一致性数据快照，并通过这个快照来提供一定级别（语句级或者事务级）的一致性读取。即MVCC，数据多版本并发控制。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。事务隔离实质上就是使事务在一定程度上“串行化”进行。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别。

未提交读：最低级别，只能保证不读取物理上损坏的数据，会产生脏读、不可重复读、幻读情况。
已提交读：语句级，不会产生脏读，会产生不可重复读、幻读情况。
可重复读：事务级，不会产生脏读和不可重复读，会产生幻读情况。
可序列化：最高级别，事务级，不会产生脏读、不可重复读、幻读情况。

注意：不一定所有的数据库都会完全实现以上4个隔离级别，如ORACLE只提供了已提交读和可序列化两个标准隔离级别；SQL Server除了上述4个隔离级别，还支持一个“快照”的隔离级别；Mysql支持全部4个隔离级别。

3.4 获取InnoDB行锁争用情况

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.5 InnoDB行锁模式及加锁方法

InnnoDB实现了2种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他写锁。

为了允许行锁和表锁并存，实现多粒度机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前锁兼容，InnoDB就将该请求的锁授予该事务，反之则要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预，对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通的SELECT语句，InnoDB不会加任何锁。

-- 共享锁
select ** from table_name where ... lock in share mode;

-- 排他锁
select * from table_name where ... for update;

3.6 InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点mysql和oracle不同，oracle是通过在数据块中对相应的数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁！

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
mysql的行锁是针对索引家的锁，而不是针对记录加的锁，如果使用相同的索引键，还是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行；
即使条件中使用了索引字段，但是否使用索引来检索数据是由mysql判断不同执行计划的代价来决定的。

3.6 间隙锁（Next-key）锁

当我们用范围条件而不是相等条件检索数据，并请求共享锁或排他锁的时候，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙”，InnoDB会对这个间隙加上锁。
使用间隙锁的目的：

防止幻读，以满足隔离级别的要求；
满足其恢复和复制的需要

3.7 InnoDB锁机制设计对恢复和复制的思考

Mysql通过binlog记录执行成功的insert、update、delete等更新数据的sql语句，并由此实现Mysql数据库的恢复和主从复制。
Mysql恢复机制有以下特点：

恢复是基于SQL语句级别的，也就是执行BINLOG中的SQL语句，Oracle是基于数据文件块的；
Mysql的binlog是按照事务提交的先后顺序记录的，恢复也是按照这个顺序执行，Oracle是按照系统更新号SCN来恢复数据的。

从上边两点可知，mysql恢复机制要求，在一个事务未提交之前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，实际上就是要求事务要串行化。这也是很多情况下，InnoDB要用到间隙锁的原因。比如在用范围条件更新记录时，不论在读已提交还是可重复读隔离级别下，InnoDB都要使用间隙锁。

3.8 什么时候使用表锁

事务需要更新大部分数据或全部数据，表又比较大的情况下；
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚；

注意：

lock tables虽然可以给InnoDB加表级锁，但表锁不是由InnoDB存储引擎管理的，而是由上一层Mysql Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道Mysql加的表锁，Mysql Server才能感知到InnoDB加的行锁。这种情况下，InnoDB才能自动识别涉及表级锁的死锁。否则，InnoDB将无法自动检测并处理这种死锁。
在用lock tables对InnoDB表加锁时候，要将autocommit设为0，否则Mysql不会给表加锁；事务结束前，不要用unlock tables释放表锁，因为会隐式提交事务；commit或rollback并不能释放用lock tables加的表锁，必须用unlock tables。

set autocommit=0;
lock table table_name1 write, table_name2 read, ...;
commit;
unlock tables;

3.9 关于死锁

Myisam表锁是deadlock free的，但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。
两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退；另一个事务获得锁，继续完成事务。
但在涉及外部锁或者表锁的时候，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数InnoDB_lock_wait_timeout来解决。这个参数不仅用来解决死锁问题，还可以针对并发较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常情况下，死锁都是由于应用设计问题，通过调整业务流程、数据库对象设计、事务设计、以及访问数据库的SQL语句，绝大部分死锁都可以避免。

在应用中如果程会并发读取多个表，应尽量约定以相同的顺序访问表，这个可以大大降低死锁的机会。
在程序以批量方式处理的数据，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
在可重复读隔离级别下，如果两个线程同时对相同记录使用select … for update加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条数据，如果两个线程都这么干，就会出现死锁。这种情况下将隔离级别改成read commited，就可以避免问题。
在读已提交隔离级别下，如果两个线程都执行select … for update，判断是否存在符合条件的记录，如果没有就插入记录，此时，只有一个线程能插入成功，另一个线程会出现锁等待，当1个线程提交完成后，第2个线程会因主键冲突而出错，但是虽然这个线程出错了，也会获得一个排他锁！此时，如果有第3个线程来申请排他锁，也会出现锁。

通过上边的设计和SQL优化措施，可以大大减少死锁，但死锁很难完全避免，在程序设计上总是捕获并处理死锁是一个良好的编程习惯。
如果出现死锁，可以使用show engine innodb status\G;命令来确定最后一个死锁产生的原因，返回结果中包含死锁相关事务的详细信息。

引发死锁的SQL语句
事务已经获得的锁
正在等待什么锁
被回滚的事务等