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