提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

Mysql | 数据库锁表的原因和解决方法「建议收藏」

mysql数据库方法 解决 建议 收藏 原因 锁表
2023-06-13 09:11:04 时间

大家好，又见面了，我是你们的朋友全栈君。

锁表的原因： 当多个连接（数据库连接）同时对一个表的数据进行更新操作，那么速度将会越来越慢，持续一段时间后将出现数据表被锁的现象，从而影响到其它的查询及更新。　　 例如： 存储过程循环30次更新操作（cycore_file_id 为唯一标识）

/*30次更新操作*/ BEGIN   DECLARE v1 INT DEFAULT 30;   WHILE v1 > 0 DO
    update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
    SET v1 = v1 - 1;   END WHILE;

END

执行结果（速度非常慢）

时间: 29.876s

Procedure executed successfully 受影响的行: 0

200个数据更新操作，三个数据库连接同时执行

update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
 update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
...等等

执行结果（持续一段时间后速度越来越慢，出现等待锁）

# Time: 151208 22:41:24
# User@Host: zmduan[zmduan] @ [192.168.235.1] Id: 2
# Query_time: 1.848644 Lock_time: 0.780778 Rows_sent: 0 Rows_examined: 393382
SET timestamp=1449643284;
update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';

.........
........

# User@Host: zmduan[zmduan] @  [192.168.235.1]  Id:     2
# Query_time: 2.868598  Lock_time: 1.558542 Rows_sent: 0  Rows_examined: 393382
SET timestamp=1449643805;
update jx_attach set complete=1,attach_size=63100 where cycore_file_id='56677142da502cd8907eb58f';
[root@localhost log]# tail -f slow_query.log 
# User@Host: zmduan[zmduan] @  [192.168.235.1]  Id:    19
# Query_time: 1.356797  Lock_time: 0.000169 Rows_sent: 1  Rows_examined: 393383
SET timestamp=1449643805;

上述例子的原因分析： MySQL的innodb存储引擎支持行级锁，innodb的行锁是通过给索引项加锁实现的，这就意味着只有通过索引条件检索数据时，innodb才使用行锁，否则使用表锁。根据当前的数据更新语句（update jx_attach set complete=1,attach_size=63100 where cycore_file_id=‘56677142da502cd8907eb58f’;），该条件字段cycore_file_id并没有添加索引，所以导致数据表被锁。 解决办法 为cycore_file_id添加索引 最终效果（30次更新操作） 时间: 0.094s Procedure executed successfully 受影响的行: 0

上述引用出处：https://blog.csdn.net/yangaliang/article/details/79713530

另外又搜集一些在并发执行时会锁表的sql语句，如下：

解释以及说明（前提是并发执行）： 假设kid是表table 的 一个索引字段 且值不唯一 1.如果kid 有多个值为12的记录那么： update table set name=’feie’ where kid=12; 会锁表 2.如果kid有唯一的值为1的记录那么： update table set name=’feie’ where kid=1; 不会锁

总结：用索引字段做为条件进行修改时， 是否表锁的取决于这个索引字段能否确定记录唯一，当索引值对应记录不唯一，会进行锁表，相反则行锁。

如果有两个delete 而 kid1 与 kid2是索引字段 语句1 delete from table where kid1=1 and kid2=2; 语句2 delete from table where kid1=1 and kid2=3; 这样的两个delete 是不会锁表的 语句1 delete from table where kid1=1 and kid2=2; 语句2 delete from table where kid1=1 ; 这样的两个delete 会锁表 总结：同一个表，如果进行删除操作时，尽量让删除条件统一，否则会相互影响造成锁表

引用出处：https://blog.csdn.net/truelove12358/article/details/53288049

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/172316.html原文链接：https://javaforall.cn

相关文章