454-MySQL(意向共享,排他锁和死锁)

女爷i 2022-09-07 01:23 223阅读 0赞

## InnoDB表级锁 ##

我们在之前知道，InnoDB是行锁，但是不是每次都获取行锁，如果没有索引项的话，还是一个表锁。而且有的时候，我们希望直接去使用表锁。

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用表级锁；  
1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；  
2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。  
如：  
LOCK TABLE user READ；读锁锁表  
LOCK TABLE user WRITE; 写锁锁表  
事务执行…  
COMMIT/ROLLBACK; 事务提交或者回滚  
UNLOCK TABLES; 本身自带提交事务，释放线程占用的所有表锁

使用表锁的时候，涉及到效率的问题：  
**要获取一张表的共享锁S或者排它锁X**，最起码得确定，这张表没有被其他事务获取过X锁，这张表里的数据没有被其他事务获取过行锁X锁。  
假如这张表有1000万个数据，怎么判断这1000万行哪些有X锁哪些没有X锁  
**不扫描 不知道。**  
**得全部扫描过去才知道。**

## 意向共享锁和意向排他锁 ##

解决效率问题。  
![在这里插入图片描述][ca503a9daadc4afe87adfa34e9b6d6b2.png]

都是和表锁的概念相关的。

**意向共享锁（IS锁）**：事务计划给记录加行共享锁，事务在给一行记录加共享锁前，必须先取得该表的IS 锁。  
**意向排他锁（IX锁）**：事务计划给记录加行排他锁，事务在给一行记录加排他锁前，必须先取得该表的IX 锁。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**1、意向锁是由InnoDB存储引擎获取行锁之前自己获取的  
2、意向锁之间都是兼容的，不会产生冲突  
3、意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）  
4、意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。**

![在这里插入图片描述][c9c24a5ac0914e50848e1cc24abc40b2.png]  
![在这里插入图片描述][2840f11048eb4a008638da802826bb60.png]

## 死锁 ##

MyISAM 表锁是 **deadlock free** 的， 这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。  
但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。  
![在这里插入图片描述][e75e37aa46054dfbb9c25ce38a907d49.png]  
死锁问题一般都是我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

**死锁出现的典型场景：**

事务1获取行锁1  
事务2获取行锁2  
然后事务1现在要获取行锁2，要等待了。  
而事务2现在要获取行锁1，也等待了。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**当前进行的2个事务都阻塞住了。**  
所有的事务都阻塞住了，相当于进程内的所有线程都阻塞住了，造成了死锁问题。  
多个事务/线程 获取多个相同资源锁的时候应该按照同样的顺序获取资源的锁  
事务被阻塞了，或者死锁，mysqld是有事务阻塞的超时时间，事务处理失败。

## 举个例子 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
事务1select 事务2select  
![在这里插入图片描述][fa94cd6094944587a5400b35eba3f19f.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
事务被阻塞，会有超时时间，如果是死锁，mysqld直接能够检测到死锁问题的发生并进行事务的回滚。

非必要我们不要手动获取锁，MVCC就不错了。

## 锁的优化建议 ##

1.尽量使用较低的隔离级别  
2.设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力  
3.选择合理的事务大小，小事务发生锁冲突的概率小  
4.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会  
5.尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响  
6.不要申请超过实际需要的锁级别  
7.除非必须，查询时不要显示加锁

[ca503a9daadc4afe87adfa34e9b6d6b2.png]: /images/20220829/409e1c5758af44a58568c378ecea4f62.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/9efa0f5143164836b84c42da1ec1bc8b.png
[c9c24a5ac0914e50848e1cc24abc40b2.png]: /images/20220829/3893624328e34db8930aa1cd83cb9237.png
[2840f11048eb4a008638da802826bb60.png]: /images/20220829/c40529389baa44a0bf9de3aced463cbf.png
[e75e37aa46054dfbb9c25ce38a907d49.png]: /images/20220829/278a139faf874aa5910ab3c2e50ee7a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/a391f405ad70415eb869a5cd6db7f573.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/c1bdce0189424406aa254a3fe2e96b81.png
[fa94cd6094944587a5400b35eba3f19f.png]: /images/20220829/252291380ff24a18b649d8cafbb82538.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/cde5d2c5e8db4196ac289443ecbe7071.png