MySQL - 共享锁和排它锁初探

约定不等于承诺〃 2023-03-04 08:25 166阅读 0赞

### 文章目录 ###

*  生猛干货
 *  官方文档
 *  事务隔离级别
 *  共享锁 VS 排他锁的区别
 *  SELECT ... FOR UPDATE 排它锁 演示
 *  SELECT ... LOCK IN SHARE MODE 共享锁 演示
 *  搞定MySQL

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]

# 生猛干货 #

[带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试][MySQL]

--------------------

# 官方文档 #

锁定某一行可以用lock in share mode(共享锁) 和for update(排它锁)

官方文档：

[https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-locking-reads.html]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]

大体过一下

如果你在查询数据，然后在同一个事务里插入或者修改相关的数据，常规的 select 语句不会提供足够的保护。其他的事务可以修改或者删除你正在查询的行。

InnoDB 支持两种可以提供安全机制的读取锁：

*  **SELECT … LOCK IN SHARE MODE** 在读取的行上设置一个共享锁，其他的session可以读这些行，但在你的事务提交之前不可以修改它们。如果这些行里有被其他的还没有提交的事务修改，你的查询会等到那个事务结束之后使用最新的值
 *  索引搜索遇到的记录，**SELECT … FOR UPDATE** 会锁住行及任何关联的索引条目，和你对那些行执行 update 语句相同。其他的事务会被阻塞在对这些行执行 update 操作，获取共享锁，或从某些事务隔离级别读取数据等操作。 一致性读(Consistent Nonlocking Reads)会忽略在读取视图上的记录的任何锁。（旧版本的记录不能被锁定；它们通过应用撤销日志在记录的内存副本上时被重建。）

**所有被共享锁和排他锁查询所设置的锁都会在事务提交或者回滚之后被释放。**

注:

*  使用 SELECT FOR UPDATE 为 update 操作锁定行，只适用于 autocommit 被禁用（当使用 START TRANSACTION 开始事务或者设置 autocommit 为0时）。
 *  如果 autocommit 已启用，符合规范的行不会被锁定。

--------------------

# 事务隔离级别 #

默认的 RR

--------------------

# 共享锁 VS 排他锁的区别 #

*  SELECT … LOCK IN SHARE MODE ：**共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。**
    
    **如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据**。
 *  SELECT … FOR UPDATE：排他锁(X锁, exclusive locks)。**如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据**。

**普通 select 语句默认不加锁，而CUD操作默认加排他锁。**

--------------------

# SELECT … FOR UPDATE 排它锁 演示 #

对所在行加上了一把**排它锁** x锁

【实验步骤】

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="right">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">begin 模拟开启事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center">select * from country where countrycode = ‘CN’ for update ; --读取成功，但未提交事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">begin 模拟开启事务</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from country where countrycode = ‘CN’ for update ; ---- 可以读取成功</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">update country set countryname=‘新中国’ where countrycode = ‘CN’; ---- 一直被阻塞，如果会话一一直没提交，session2 直到超时，抛出如下异常 1205 - Lock wait timeout exceeded; try restarting transaction</td> 
  </tr> 
 </tbody> 
</table>

【结论】

通过上述实验 ，我们可以推断出*其他session只能读该行数据，修改则会被阻塞，直到锁定行的session提交事务*

--------------------

# SELECT … LOCK IN SHARE MODE 共享锁 演示 #

<table> 
 <thead> 
  <tr> 
   <th align="center">session1</th> 
   <th align="right">session2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">begin 模拟开启事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center">select * from country where countrycode = ‘CN’ lock in share mode ; --读取成功，但未提交事务</td> 
   <td align="right"></td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">begin 模拟开启事务</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from country where countrycode = ‘CN’ lock in share mode ; ---- 可以加共享锁</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">select * from country where countrycode = ‘CN’ for update ; ---- 不能加排它锁 一直被阻塞，</td> 
  </tr> 
  <tr> 
   <td align="center"></td> 
   <td align="right">update country set countryname=‘China’ where countrycode = ‘CN’ ; — 修改该行数据也被阻塞，如果事务一还没提交，一直等到超时 1205 - Lock wait timeout exceeded; try restarting transaction</td> 
  </tr> 
 </tbody> 
</table>

--------------------

# 搞定MySQL #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70]: /images/20230208/aa9dc903c3514e76ac76496ff05671dd.png
[MySQL]: https://marketing.csdn.net/poster/3766
[https_dev.mysql.com_doc_refman_5.7_en_innodb-locking-reads.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 1]: /images/20230208/fcf3d70cff854d699893c137535b5db0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmdzaGFuZ3dlaQ_size_16_color_FFFFFF_t_70 2]: /images/20230208/a046d0f4369c430c8bc63a748892342c.png