锁表的原因:
当多个连接(数据库连接)同时对一个表的数据进行更新操作,那么速度将会越来越慢,持续一段时间后将出现数据表被锁的现象,从而影响到其它的查询及更新。
例如:
存储过程循环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/fragrant_no1/article/details/79727263