mysql lock in share mode 容易死锁
来源:互联网 发布:与黑人滚床单 知乎 编辑:程序博客网 时间:2024/05/20 07:50
JPA+Hibernate 锁 LockModeType.PESSIMISTIC_READ,容易出现死锁的情况,资料记录
情景一
LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter
如果两个客户端(事务)同时执行同一个查询,都进行更新时,至少其中一个会已死锁结束
实验: 开启两个mysql客户端
1 分别执行如下语句;
set autocommit=0;
begin;
select * from test_concurrence where id='1' lock in share mode;
可以看到两个客户端都可以查询出数据,不用阻塞等待;
2 分别 执行更新语句
update test_concurrence set name='namelock2111' where id='1';
update test_concurrence set name='namelock3333' where id='1';
检测到死锁,其中一个客户端更新失败,一个执行更新
3 分别执行 commit ,提交事务。
情景二
SELECT ... LOCK IN SHARE MODE: 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.
实验: 开启两个mysql 客户端
1 客户端1执行下面语句
begin;
select * from test_concurrence where id='1' lock in share mode;
update test_concurrence set name='namelock2' where id='1';
客户端2 执行
begin;
select * from test_concurrence where id='1' lock in share mode;
可以看到客户端2 处于等待状态;
2 客户端1提交事务,执行“commit”;
客户端1事务提交成功,客户端3成功查询出数据。
答案
If there is another transaction, that has modified the row, the SELECT ... LOCK IN SHARE MODE waits. If the row is not modified, it does not wait. Which leads to the first situation, that 2 transaction can SELECT ... LOCK IN SHARE MODE, but none of them can update the record (deadlock)
如果有一个事务已经修改了记录数据,另一个事务执行“SELECT ... LOCK IN SHARE MODE ” 语句,则会等待;如果记录数据没有被修改,“SELECT ... LOCK IN SHARE MODE ” 不会等待,从而导致情景一,两个事务都可以成功执行“SELECT ... LOCK IN SHARE MODE”,但是会导致死锁。
参考链接:
http://stackoverflow.com/questions/6607006/select-lock-in-share-mode
http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html
- mysql lock in share mode 容易死锁
- mysql lock in share mode 和 select for update
- mysql 并行写 for update lock in share mode 总结
- mysql lock in share mode 和 select for update
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL的锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
- 【Mysql行锁】SELECT...FOR UPDATE和SELECT ...LOCK IN SHARE MODE
- mysql 加锁读Locking Reads (SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE)
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE区别
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL中的行级锁SELECT FOR UPDATE 和LOCK IN SHARE MODE 区别
- MySQL 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE
- MYSQL select for update/lock in share mode 对事务并发性影响
- SELECT LOCK IN SHARE MODE and FOR UPDATE
- select ... for update& lock in share mode加锁情况
- 共享锁select * from t1 where id=2 LOCK IN SHARE MODE;
- 深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE
- mpc8260 11
- strcpy详解
- Ugly Number 判断是否为“丑数”
- vs2015破解流程
- zepto,jquery关于cookie存放时间问题
- mysql lock in share mode 容易死锁
- MFC中设置Label控件的背景色、字体、字号和颜色
- Unity3D用 iTween 实现摄像机按路径浏览
- BGCF
- Java验证身份证号是否合法
- 如何不将iphone上面的数字识别为电话
- libgnutls missing package configuration (PC) files
- android之View坐标系(view获取自身坐标的方法和点击事件中坐标的获取)
- 在Xcode中为main函数设置输入参数