文档章节

MySQL/InnoDB锁机制

秋风醉了
 秋风醉了
发布于 2014/07/30 21:35
字数 1365
阅读 6.2K
收藏 16

显式加锁

  • select ... lock in share mode:加 S 锁 

  • select ... for update:加 X 锁 

 

MySQL快照读和当前读

在一个支持MVCC并发控制的系统中,哪些读操作是快照读?哪些操作又是当前读呢?以MySQL InnoDB为例:

快照读:简单的select操作,属于快照读,不加锁。(当然,也有例外,下面会分析)

select * from table where ?;

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

为什么将 插入/更新/删除 操作,都归为当前读?可以看看下面这个 更新 操作,在数据库中的执行流程:

update 执行流程

从图中,可以看到,一个Update操作的具体流程。当Update SQL被发给MySQL后,MySQL Server会根据where条件,读取第一条满足条件的记录,然后InnoDB引擎会将第一条记录返回,并加锁 (current read)。待MySQL Server收到这条加锁的记录之后,会再发起一个Update请求,更新这条记录。一条记录操作完成,再读取下一条记录,直至没有满足条件的记录为止。因此,Update操作内部,就包含了一个当前读。同理,Delete操作也一样。Insert操作会稍微有些不同,简单来说,就是Insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。

注:根据上图的交互,针对一条当前读的SQL语句,InnoDB与MySQL Server的交互,是一条一条进行的,因此,加锁也是一条一条进行的。先对一条满足条件的记录加锁,返回给MySQL Server,做一些DML操作;然后在读取下一条加锁,直至读取完毕。

 

select ... lock in share mode

官方文档解释

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends. 

在读取的行上设置一个共享模式的锁。这个共享锁允许其它session读取数据但不允许修改它。 行读取的是最新的数据,如果他被其它事务使用中而没有提交,读取锁将被阻塞直到那个事务结束。 

 

lock in share mode示例一

session1

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

mysql> select * from t1 where b = 4000 lock in share mode;
+----+------+
| a  | b    |
+----+------+
| 53 | 4000 |
| 54 | 4000 |
+----+------+
2 rows in set (0.00 sec)

 

session2

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

mysql> select * from t1 where b= 4000;
+----+------+
| a  | b    |
+----+------+
| 53 | 4000 |
| 54 | 4000 |
+----+------+
2 rows in set (0.00 sec)

mysql> update t1 set b = 2999 where a = 53;

 

因为session1给查询结果加了读锁,当session2更新a=53这条数据时,session1里加的读锁拒绝修改,所以session2事务在这里阻塞,即发生死锁,当超过一定时间后,该事务执行失败,这是因为mysql的死锁检测起作用。

 

session1

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

 

session1执行commit,提交事务,解除了对查询结果的读锁,也就是共享锁,这时,session2的update语句执行。

 

session2

Query OK, 1 row affected (20.83 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1 where a = 53;
+----+------+
| a  | b    |
+----+------+
| 53 | 2999 |
+----+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

 

session1

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

mysql> select * from t1 where a = 53;
+----+------+
| a  | b    |
+----+------+
| 53 | 2999 |
+----+------+
1 row in set (0.00 sec)

 

lock in share mode示例二

session1

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

mysql> select * from t1 where b = 4000 lock in share mode;
+----+------+
| a  | b    |
+----+------+
| 54 | 4000 |
+----+------+
1 row in set (0.00 sec)

 

session2

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

mysql> select * from t1 where b= 4000 lock in share mode;
+----+------+
| a  | b    |
+----+------+
| 54 | 4000 |
+----+------+
1 row in set (0.00 sec)

 

可以看出,当session1加了共享锁后,其他事务还可以给其加共享锁。。。。

 

select ... for update

官方解释

SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing. 

在读取行上设置一个排他锁。组织其他session读取或者写入行数据

 

select ... for update示例一

session1 

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

mysql> select * from t1 where b < 4000 for update;
+----+------+
| a  | b    |
+----+------+
| 51 | 2000 |
| 52 | 2999 |
| 53 | 2999 |
| 57 | 2000 |
+----+------+
4 rows in set (0.06 sec)

mysql>

 

 

session2

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

mysql> select * from t1 where b < 4000 ;
+----+------+
| a  | b    |
+----+------+
| 51 | 2000 |
| 52 | 2999 |
| 53 | 2999 |
| 57 | 2000 |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from t1 where b < 4000 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

在该事务中在加排它锁,过一段时间后,报错。但能读取数据。

参考:http://hedengcheng.com/?p=771

http://blog.csdn.net/jiao_fuyou/article/details/16368827

http://www.cnblogs.com/hancf/archive/2012/08/28/2660422.html

http://blog.csdn.net/zbszhangbosen/article/details/7434637

http://lhc1986.iteye.com/blog/1462856

http://blog.csdn.net/java2000_net/article/details/3709945

http://blog.csdn.net/jiao_fuyou/article/details/16368827

最近写的文章的参考,希望对你有帮助。

========END========

© 著作权归作者所有

秋风醉了
粉丝 253
博文 532
码字总数 405557
作品 0
朝阳
程序员
私信 提问
加载中

评论(1)

calvin_6
calvin_6
看明白了,写的很详细
使用mysql中的锁解决高并发问题

为什么要加锁 多核计算机的出现,计算机实现真正并行计算,可以在同一时刻,执行多个任务。在多线程编程中,因为线程执行顺序不可控导致的数据错误。比如,多线程的理想状态是这样的 但是实际情...

紫葡萄0
2018/11/23
0
0
MySQL数据库和ACID模型

ACID模型是一组强调高可靠性的数据库系统设计原则。InnoDB存储引擎坚持ACID原则,确保即使在软件崩溃甚至是硬件故障的情况下,数据也不会损坏。当你需要依赖兼容ACID原则的业务时,你不必重复...

jockchou
2015/07/13
737
0
SQL优化之一则MySQL中的DELETE、UPDATE 子查询的锁机制失效案例

前言 开发与维护人员避免不了与 in/exists、not in/not exists 子查询打交道,接触过的人可能知道 in/exists、not in/not exists 相关子查询会使 SELECT 查询变慢,没有 join 连接效率,却不...

全部原谅
2018/07/27
5
0
MySQL探秘(三):InnoDB的内存结构和特性

 常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特点就是其插件式的表存储引擎。而在众...

carpediem123
2018/08/26
0
0
mysql 开发进阶篇系列 6 锁问题

一.概述   在数据库中,数据是属于共享资源,为了保证并发访问的一致性,有效性,产生了锁。接下来重点讨论mysql锁机制的特点,常见的锁问题,以及解决mysql锁问题的一些方法或建议。 相比...

花阴偷移
2018/07/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

检查是否已使用jQuery选中复选框 - Check if checkbox is checked with jQuery

问题: How can I check if a checkbox in a checkbox array is checked using the id of the checkbox array? 如何检查是否使用复选框数组的ID选中了复选框数组中的复选框? I am using the...

技术盛宴
21分钟前
3
0
略谈分布式系统中的容器设计模式

本文作者:zytan_cocoa 略谈分布式系统中的容器设计模式 谭中意 2020/3/5 前言:云原生(Cloud Native)不仅仅是趋势,更是现在进行时,它是构建现代的,可弹性伸缩的,快速迭代的计算网络服...

百度开发者中心
03/11
18
0
创建将T约束为枚举的泛型方法 - Create Generic method constraining T to an Enum

问题: I'm building a function to extend the Enum.Parse concept that 我正在构建一个函数来扩展Enum.Parse概念, Allows a default value to be parsed in case that an Enum value is ......

javail
今天
31
0
算法 : 一个整形一维数组,以及一个目标数,写一个方法,求出数组元素之和为该目标值的所有组合。

一个整形一维数组,以及一个目标数,写一个方法,求出数组任意两个元素之和为该目标值的所有组合。 function meetGroup($array, $hit){ $meet_groups =[]; foreach ($array as $...

冻结not
今天
35
0
win10下docker中安装sqlserver并使用navicat premiun连接

搜索镜像:docker search mssql 下载镜像:docker pull microsoft/mssql-server-linux 运行镜像:docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=[包含大小写字母、数字的长度不少于8位数的......

nie_yuange
今天
28
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部