MySQL编程优化篇（六）锁问题

最新推荐文章于 2021-01-19 02:14:23 发布

新新许愿树

最新推荐文章于 2021-01-19 02:14:23 发布

阅读量228

点赞数 1

分类专栏：关系型数据库 # MySQL 文章标签： MySQL 锁问题

本文链接：https://blog.csdn.net/qq_41541619/article/details/103601391

版权

关系型数据库同时被 2 个专栏收录

25 篇文章 2 订阅

订阅专栏

MySQL

12 篇文章 1 订阅

订阅专栏

恢复和复制的需要，对InnoDB锁机制的影响

什么时候使用表锁

关于死锁

具体的可以查看我的另外一篇博客，下面只摘取部分介绍！

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

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

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISQM表锁

MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

MyISAM在执行查询语句(SELECT) 前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、 DELETE、INSERT等)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCKTABLE命令给MyISAM表显式加锁。

一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也是MyISAM不太适合与有大量更新和查询操作应用的原因。

无死锁！

InnoDB锁问题

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

事务的ACID属性不予介绍。

并发事务带来的问题：脏读、不可重复读、幻读和更新丢失。

下面主要介绍一下更新丢失。

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。

案例：

两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

事务隔离级别

数据库实现事务隔离的方式，基本上可分为以下两种。

读取数据前，对其加锁，阻止其他事务对数据进行修改。
不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。

可以通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况：mysql> show status like '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)

InnoDB的行锁模式

InnoDB实现了以下两种类型的行锁。

共享锁(S) ：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排他锁(X) ：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

意向共享锁(IS) ：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX) ：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

注意：

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句， InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB行锁实现方式

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

InnoDB行锁分为3种情形。

Record lock：对索引项加锁。
Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
Next-key lock：前两种的组合，对记录及其前面的间隙加锁。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！

在实际应用中，要特别注意InnoDB行锁这一特性，否则可能导致大量的锁冲突，从而影响并发性能。下面通过一些例子来说明。

在不通过索引条件查询时，InnoDB会锁定表中的所有记录。

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对所有记录加锁。

Next-Key锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用Next-Key锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用Next-Key锁！

如下，假设emp表共101条记录，其empid的值分别是1、2、...、100、101：

-- 会对empid大于100（包括并不存在的）“间隙”加锁
select * from emp where empid > 100 for update;

-- 会对empid=102的这条记录加锁，尽管这条记录不存在
select * from emp where empid = 102 for update;

恢复和复制的需要，对InnoDB锁机制的影响

MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。

对于insert into target_tab select * from source_tab where ...和create table new_tab ... select .. from source_tab where...，这种SQL语句，用户并没有对source_tab做任何更新操作，但MySQL对这种SQL做了特别处理，对source_tab加了共享锁，没有使用多版本数据一致性读技术！

所以，INSERT...SELECT...和CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新。如果查询比较复杂，会造成严重的性能问题，读者在应用中应尽量避免使用。实际上，MySQL将这种 SQL叫做不确定（non-deterministic）的 SQL，属于“Unsafe SQL”，不推荐使用。

什么时候使用表锁

在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释放表锁，因为 UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。

关于死锁

死锁检测：超时机制 + wait for graph(等待图)

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。

避免死锁的常用方法

在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等

新新许愿树

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL编程优化篇（六）锁问题

目录MyISQM表锁InnoDB锁问题事务隔离级别InnoDB的行锁模式InnoDB行锁实现方式Next-Key锁恢复和复制的需要，对InnoDB锁机制的影响什么时候使用表锁关于死锁具体的可以查看我的另外一篇博客，下面只摘取部分介绍！表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。行级锁：开销大，加锁慢；会出现死锁...
复制链接

扫一扫