MySQL锁释放时机(事务)

喜欢ヅ旅行 2022-04-10 06:59 1066阅读 0赞

![webp][]

在事务执行过程中，如果有加锁操作，这个锁需要等事务提交时释放。

<table> 
 <thead> 
  <tr> 
   <th>时间线</th> 
   <th>事务1 (T1)</th> 
   <th>事务2(T2)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>t1</td> 
   <td>BEGIN;</td> 
   <td>BEGIN;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>t2</td> 
   <td>UPDATE lockdemo SET state = '666' WHERE id = 2;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>t3</td> 
   <td>&nbsp;</td> 
   <td>UPDATE lockdemo SET state = '22' WHERE id = 2;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>t..</td> 
   <td>SELECT * FROM lockdemo;</td> 
   <td>SELECT * FROM lockdemo;</td> 
  </tr> 
  <tr> 
   <td>t..</td> 
   <td>commit;</td> 
   <td>commit;</td> 
  </tr> 
 </tbody> 
</table>

事务1在t2时刻先执行更新操作，它就会一直持有id=2的锁直到commit；事务2在t3时刻获取会失败。

### SQL ###

BEGIN;
    UPDATE lockdemo SET state = '666' WHERE id = 2;
    SELECT * FROM lockdemo;
    COMMIT;

BEGIN;
    UPDATE lockdemo SET state = '22' WHERE id = 2;
    SELECT * FROM lockdemo;
    COMMIT;

### 死锁 ###

死锁是指两个或多个事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象.当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁,多个事务同时锁定同一个资源时,也会产生死锁。  
例如:  
事务1:

start transaction;
    update stock_price set close = 45.50 where stock_id = 4 and date = '2017-4-26';
    update stock_price set close = 19.80 where stock_id = 3 and date = '2017-4-27';
    commit;

事务2:

start transaction;
    update stock_price set high = 20.10 where stock_id = 3 and date = '2017-4-27';
    update stock_price set high = 47.20 where stock_id = 4 and date = '2017-4-26';
    commit;

如果凑巧,两个事务均执行了第一条update语句,同时锁定了该资源,当尝试执行第二条update语句的时候,去发现资源已经被锁定,两个事务都等待对方释放锁,则陷入死循环,形成死锁。  
为了解决这种问题,数据库系统实现了各种死锁检测和死锁超时机制.比如InnoDB存储引擎目前的处理方法是将持有最少级排他锁的事务进行回滚.

[http://tech.meituan.com/innodb-lock.html][http_tech.meituan.com_innodb-lock.html]

文章来源：[https://www.jianshu.com/p/6b6181a9bbfc][https_www.jianshu.com_p_6b6181a9bbfc]

[webp]: /images/20220402/2b602f3a18fa4a9ca5c21a0ddd8893ca.png
[http_tech.meituan.com_innodb-lock.html]: https://link.jianshu.com/?t=http://tech.meituan.com/innodb-lock.html
[https_www.jianshu.com_p_6b6181a9bbfc]: https://www.jianshu.com/p/6b6181a9bbfc