[Mysql 行锁-表锁] 以及show profile分析sql

清疚 2023-10-04 12:11 49阅读 0赞

**表锁:**(偏读)

*  ![List item][]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]

当表 上锁以后,其他客户会话就不能访问 ,就会产生阻塞,除非解锁

**\[如何分析表锁定\]:**

*  可以通过检查table\_locks\_waited 和 table\_locas\_immediate状态变量来分析系统上的表锁定;
 *  SQL: show status like ‘table%’  
    图例:  
    ![在这里插入图片描述][20210429151008836.png]  
    图中有二个状态变量来记录Mysql内部表级锁定的情况, 如下:
 *  table\_locas\_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取值加1;
 *  table\_locks\_waited :出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1);此值高则说明存在着较严重的表级锁争用情况;

**此外:**

> Myisam的读写锁调度是 **写** 优先,这也是myisam不适合作为主表引擎;因为写锁以后,其他线程不能做任何的操作,大量的更新会使查询很难得到锁,从而造成永远的阻塞. **table\_locks\_waited 值越高存在着严重锁竞争情况.**

**行锁:**(偏写)

特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;  
锁定粒度小,发生锁冲突概率最低,并发度也最高.  
![在这里插入图片描述][20210429152101720.png]  
事务(Transaction) 和 ACID 属性:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 1]  
**并发事务处理带来的问题:**

*  更新丢失
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 2]
 *  脏读
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 3]
 *  不可重复读
 *  ![在这里插入图片描述][20210429152601160.png]
 *  幻读
 *  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 4]

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

![在这里插入图片描述][20210429152403422.png]  
**Mysql事务隔离级别来解决并发出现的问题:**

*  ![List item][List item 1]

**查看Mysql当前数据库的事务隔离级别默认级别:**  
![在这里插入图片描述][20210429152824845.png]

> 数据不能出现脏读,不可重复读,但是可以出现幻读;

**测试建表sql;**

*  ![List item][List item 2]

数据库版本5.5以后大多数默认InnoDB是数据库的引擎.

*  **行锁基本演示:**  
    **有了索引,就自动成为行锁;默认行锁**
 *  set autocommit=1;开启自动提交
    
    set autocommit=0;关闭自动提交 需要手动 commit 提交

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

**读己之所写**:  
查询只能查到自己修改的值,  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 5]  
图中我们可以发现 当我们把自动提交关闭以后,客户1 ,客户2(session-2)都关闭以后, 我客户1修改数据以后,查询这个表 发现修改成功, 但是另一个 客户查询 发现数据没有更改, 这就是 **读己之所写**

当二个客户 commit提交以后 客户2就能查看客户1修改的数据了 :  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 6]

在二个客户修改同一个数据的时候,必须要commit,不然不能更新, 如果客户1先更新了 没有commit,客户二就会堵塞, 直到客户1commit 以后 客户二才能 修改成功并且 需要coomit提交 才能覆盖 客户1修改的值

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

**\[如何分析行锁定\]:**  
通过检查InnodDB\_row\_lock状态变量 来分析系统上的行锁争夺情况;  
mysql>show status like’innodb\_row\_lock%’;

1.  ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 7]  
    ![在这里插入图片描述][20210430094756490.png]  
    ![在这里插入图片描述][20210430095032632.png]  
    **show profile**;
2.  使用show profiles分析SQL性能 Show  
    profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。
3.  查看数据库版本 mysql> select version();
    
    profile默认是不打开的 mysql> show profiles; Empty set (0.02 sec)
    
    验证修改后的结果 mysql> show variables like “%pro%”; 可以看到profiling 默认是OFF的。
    
    开启profile，然后测试 开启profile mysql> set profiling=1;
    
    获取profile的帮助 help profile;

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

**如何锁定一行数据,防止串改;**  
**select \* from test\_innodb\_lock where 条件 for update;**  
这个是锁定这一行数据;比如你在线上环境发现一条数据有误,想要修改,但是要防止别人修改,这时候 你可以先锁定, 锁定以后 你没有commit 其他人 就会堵塞.

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

[List item]: https://img-blog.csdnimg.cn/20210429171621974.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210429171904666.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[20210429151008836.png]: https://img-blog.csdnimg.cn/20210429151008836.png
[20210429152101720.png]: https://img-blog.csdnimg.cn/20210429152101720.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210429152220359.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210429152437501.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210429152512805.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[20210429152601160.png]: https://img-blog.csdnimg.cn/20210429152601160.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210429152620879.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[20210429152403422.png]: https://img-blog.csdnimg.cn/20210429152403422.png
[List item 1]: https://img-blog.csdnimg.cn/20210429152743431.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[20210429152824845.png]: https://img-blog.csdnimg.cn/20210429152824845.png
[List item 2]: https://img-blog.csdnimg.cn/20210429155508838.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210429163511491.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210429163837785.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20210430094650335.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70
[20210430094756490.png]: https://img-blog.csdnimg.cn/20210430094756490.png
[20210430095032632.png]: https://img-blog.csdnimg.cn/20210430095032632.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210430111703403.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NxTDUyMGxU,size_16,color_FFFFFF,t_70