Mysql InnoDB 数据更新导致锁表

Dear 丶 2022-05-25 03:50 224阅读 0赞

一、数据表结构

CREATE TABLE `jx_attach` (
      `attach_id` int(11) NOT NULL AUTO_INCREMENT,
      `feed_id` int(11) DEFAULT NULL ,
      `attach_name` varchar(255) NOT NULL,
      `cycore_file_id` varchar(255) DEFAULT NULL , 
      `attach_size` bigint(20) NOT NULL DEFAULT '0',
      `complete` smallint(6) NOT NULL DEFAULT '0' ,
      PRIMARY KEY (`attach_id`),
      KEY `jx_trend_attach_FK` (`feed_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=394160 DEFAULT CHARSET=utf8;

二、现象

当多个连接同时对一个表的数据进行更新操作，那么速度将会越来越慢，持续一段时间后将出现数据表被锁，从而影响到其它的查询及更新。

存储过程循环30次更新操作

/*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;
    SELECT *
    FROM jx_attach ja,jx_feed jf
    where ja.feed_id=jf.feed_id and ja.cycore_file_id='56677146da502cd8907eb5b7';
    # 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';

三、原因分析

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://www.cnblogs.com/zmduan/p/5033047.html][https_www.cnblogs.com_zmduan_p_5033047.html]

[https_www.cnblogs.com_zmduan_p_5033047.html]: https://www.cnblogs.com/zmduan/p/5033047.html