如何锁（S，X，IS，IX）在Mysql中与查询如UP UPDATE / LOCK IN SHARE MODE？ [英] How locks (S,X,IS,IX) work in Mysql with queries like FOR UPDATE/LOCK IN SHARE MODE?

问题描述

1：

我正在尝试这个，效果很好：

 启动事务; 
 select * from orders where id = 21548 LOCK IN SHARE MODE; 
 update orders set amount = 1500其中id = 21548; 
 commit; 
  

根据 LOCK IN SHARE MODE 的定义，使用IS锁定并使用S锁定锁定所选行。

当使用S lock锁定行时，如何在不释放锁定的情况下进行修改？
它需要X锁来修改它。

<$ p $

p> // session1
start transaction;
select * from orders where id = 21548 FOR UPDATE;


保持此session1相同，并在不同的会话中尝试：

  // session2 
 select * from orders where id = 21548; // working 
 update orders set amount = 2000 where id = 21548; // waiting 
  

FOR UPDATE 将整个表锁定为IX模式，

由于X模式与S模式不兼容，那么第二个会话中的select查询是如何执行的？

一个答案可能是选择查询不是请求S锁，这就是为什么它运行成功。但是在第二个会话中的更新查询也不是要求X锁，但是当你执行它，它开始等待

我已经阅读了很多关于这一点的东西，但无法清除我的疑惑。请帮助。

解决方案

Bill Karwin通过电子邮件回答了这个问题。他说：

1. 持有S锁的同一事务可以将锁锁定到X锁。这不是冲突。




2. 使用FOR UPDATE的会话1中的SELECT获取X锁。没有指定锁定子句的简单SELECT查询不需要获取S锁。

任何UPDATE或DELETE需要以获取X锁。这是隐含的。这些语句没有任何特殊的锁定子句。

有关IS / IX锁和FOR UPDATE / LOCK IN SHARE模式的详细信息，请访问
共享和排他锁。

1:

I was trying this and it was working fine:

start transaction; 
 select * from orders where id = 21548 LOCK IN SHARE MODE;
 update orders set amount = 1500 where id = 21548;
commit;

According to the definition of LOCK IN SHARE MODE , it locks the table with IS lock and lock the selected rows with S lock.

When a row is locked with S lock.How can it be modified without releasing lock? It needs X lock to modify it.Right? Or is it valid only for different connection transaction?

2:

//session1
start transaction;
select * from orders where id = 21548 FOR UPDATE;

Keep this session1 same and try this in the different session:

 //session2
 select * from orders where id = 21548; //working
 update orders set amount = 2000 where id = 21548; //waiting

FOR UPDATE locks the entire table into IX mode and selected row into X mode.

As X mode is incompatible with S mode then how come select query in second session is getting executed?

One answer might be that select query is not asking for S lock that's why it's running successfully.But update query in the second session is also not asking for X lock , but as you execute it , it starts waiting for the lock held by session1.

I have read a lot of stuff regarding this but not able to clear my doubts.Please help.

解决方案

Bill Karwin answered this question through email.He said:

1. The same transaction that holds an S lock can promote the lock to an X lock. This is not a conflict.

2. The SELECT in session 1 with FOR UPDATE acquires an X lock. A simple SELECT query with no locking clause specified does not need to acquire an S lock.

Any UPDATE or DELETE needs to acquire an X lock. That's implicit. Those statements don't have any special locking clause for that.

For more details on IS/IX locks and FOR UPDATE/LOCK IN SHARE MODE please visit shared-and-exclusive-locks .

这篇关于如何锁（S，X，IS，IX）在Mysql中与查询如UP UPDATE / LOCK IN SHARE MODE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！