三水住房和城乡建设局的网站,南山网站建设找哪家公司好,qq 互联网站开发代码,网站建设经理岗位职责最近想起之前处理过的一个mysql 死锁问题#xff0c;是在高并发下update批量更新导致的#xff0c;这里探讨一下发生的原因#xff0c;以及解决办法#xff1b;
发生死锁的sql语句如下#xff0c;其中where条件后的字段是有复合索引的。
update t_push_message_device_h…最近想起之前处理过的一个mysql 死锁问题是在高并发下update批量更新导致的这里探讨一下发生的原因以及解决办法
发生死锁的sql语句如下其中where条件后的字段是有复合索引的。
update t_push_message_device_history set status?,update_time? where msg_id? and msg_key? and dev_no?;之所以会发生死锁是与mysql 加锁机制有关系的下面我们来简单描述下加锁过程
1. mysql锁介绍
表锁锁住整张表 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定所以可以很好的避免困扰我们的死锁问题。
当然锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高致使并大度大打折扣。
行锁锁住一行或者多行 如果数据库表没有加索引那么更新操作的时候会锁住整张表也就是所谓的表级锁如果有索引并且查询条件中也带有这些字段那么就会使用行级锁。行级锁并不是锁记录而是锁索引。
行级锁定最大的特点就是锁定对象的颗粒度很小也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小所以发生锁定资源争用的概率也最小能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小所以每次获取锁和释放锁需要做的事情也更多带来的消耗自然也就更大了。此外行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。
2. mysql InnoDB行锁
InnoDB行锁是通过给索引上的索引项加锁来实现的只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将使用表锁。 在实际应用中要特别注意以下几点 在不通过索引条件查询的时候InnoDB使用的是表锁而不是行锁 由于MySQL的行锁是针对索引加的锁不是针对记录加的锁所以虽然是访问不同行的记录但是如果是使用相同的索引键是会出现锁冲突的因为如果不是唯一索引那么就算是相同的索引键也可能对应的是不同的记录 当表有多个索引的时候不同的事务可以使用不同的索引锁定不同的行另外不论是使用主键索引、唯一索引或普通索引InnoDB都会使用行锁来对数据加锁
即便在条件中使用了索引字段但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的如果MySQL认为全表扫描效率更高比如对一些很小的表它就不会使用索引这种情况下InnoDB将使用表锁而不是行锁。因此在分析锁冲突时别忘了检查SQL的执行计划以确认是否真正使用了索引。
3. mysql InnoDB加锁过程
首先要明确的是InnoDB是边扫描边加锁的。
因为是边扫描边加锁这里就存在一个顺序问题假如线程A对a b c d e五条数据边扫描边加X锁而同时线程B对 e f g h a五条数据也边扫描边加X锁明显的这就会存在一个问题在线程A对e加锁时线程B已经对e加锁了所以线程A会等待线程B释放锁而线程B对a加锁时线程A也对a加锁了所以线程B就会等待线程A释放锁最终结果是互相循环等待造成死锁。
4. 解决办法
其实通过上面的加锁过程我们很容易就找到解决办法了那就是where后面条件是主键索引或者是唯一索引唯一索引或者主键索引不会出现这种问题是因为每次都只有一条数据线程A加锁后线程B等待即可。 如果where后面条件是主键索引或者是唯一索引那么直接就对主键行加X锁 如果是二级索引边扫描边对二级索引行加X锁及间隙加GAP锁然后再根据二级索引里的主键信息去扫描聚簇索引对主键行依次加X锁
