mysql死锁问题实例分析

这篇文章主要介绍“mysql死锁问题实例分析”，在日常操作中，相信很多人在mysql死锁问题实例分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql死锁问题实例分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一、问题描述：

        一张表的非索引字段更新，并发环境下出现锁表，导致死锁！

二、简要分析：

        两个事务，一个事务，在执行一个索引字段更新sql，另外一个事务正好进行主键条件更新，也进行非索引更新。导致两个事务交叉等待释放锁，所以出现死锁问题。

三、数据模拟：

创建表：

create table user(

  id int(10) NOT NULL AUTO_INCREMENT,

  user_id varchar(20),

  id_card_no varchar(50),

  name varchar(30),

  password varchar(30),

  sex char(1),

  birthday date,

  PRIMARY KEY (`id`),

  INDEX index_name (user_id(20))

);

插入两条数据

insert into user (user_id,id_card_no,name,password,sex,birthday) values('000001','001','zhangsan','123','t','1993-05-27');

insert into user (user_id,id_card_no,name,password,sex,birthday) values('000002','002','lisi','123','t','1993-05-27');

四、问题复现

step 1：开启一个事务1，首先执行主键更新

  begin;

  update user set name = "test transaction by zz" where id = 1;

step 2：再开启一个事务2，执行另一个主键更新语句，

   begin;

   update user set name = "test transaction by zz" where id = 2;

step 3：事务1执行一条非索引字段更新，不要提交事务;

  update user set name = "test transaction by zz name" where user_id = '000001';

step 4：事务2在执行一条非索引字段更新，不要提交事务;

  update user set name = "test transaction by zz name" where user_id = '000002';

运行结果：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

四、分析结果：

1.两个事务，交叉等待对方释放锁，就会出现死锁。

2.如上操作，事务1执行sql2的时候等待事务2释放行锁，事务2这时候可能也进行非索引字段更新，需要等待事务1释放表锁，这就出现了死锁。

3.主键更新，只会锁住该索引，这就是行级锁的概念;索引字段更新或者删除，会先根据索引字段，找到相应的主键记录，再进行加锁;非主键，非索引字段的更新和删除默认会锁住整张表，这就是一个表级锁的概念;

五、优化与建议：

1.避免非索引字段的更新和删除操作。

2.如果有这样的更新，建议事务外查询，然后根据索引字段进行更新，就不会造成锁表的情况；

3.对相应的字段加索引，但是一张表中索引不宜太多，会影响数据库性能，最好四个以内，对于超过100ms的查询，需要优化；但是加索引也不是最好的解决方案，索引更新，也会先找到索引对应的主键，再进行加锁，也有一点点性能问题。但是满足大部分的业务需求。

4.系统需要异常情况要有重试机制，对于数据库这样的，死锁问题，一般的在并发下出现，也是会有系统空缺的时候，这时候有重试，就稍微好一点。但是对于时效性比较高的业务，一定不能出现这种问题。

到此，关于“mysql死锁问题实例分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！