mysql死锁问题实例分析

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

创新互联建站于2013年开始,先为江阳等服务建站,江阳等地企业,进行企业商务咨询服务。为江阳企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

一、问题描述:

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

二、简要分析:

        两个事务,一个事务,在执行一个索引字段更新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.

mysql死锁问题实例分析

四、分析结果:

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

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

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

五、优化与建议:

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

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

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

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

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


新闻名称:mysql死锁问题实例分析
标题网址:http://scyanting.com/article/jjphoe.html