MySQL Innodb 引擎中预防 Update 操作上升为表锁

一、MySQL 如何预防 Update 上升为表锁在MySQL中进行任何数据的修改操作都会进行一定的锁操作而锁的不同直接导致性能的差异。例如MyISAM引擎更新时采用表锁并发性较差。而Innodb引擎支持事务更新时采用行锁锁的粒度更细所以并发性较高。由于表锁的粒度过大即使只有部分行被修改也会阻塞其他事务对整个表的写操作限制了系统的吞吐量和响应速度。对于在Innodb引擎中虽然采用了粒度更细的行锁但也不是所有的数据修改操作都是仅锁住相关的行有时很可能不注意就导致了表锁。下面通过一个实验进行深入例如有如下表结构CREATETABLEuser(idintNOTNULLAUTO_INCREMENT,namevarchar(255)DEFAULTNULL,ageintDEFAULTNULL,sexvarchar(255)DEFAULTNULL,PRIMARYKEY(id),KEYidx_user_name_age(name,age),KEYname(name,age))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_0900_ai_ci;写入一些测试数据INSERTINTOtest.user(id,name,age,sex)VALUES(1,小明,18,男);INSERTINTOtest.user(id,name,age,sex)VALUES(2,小红,19,女);INSERTINTOtest.user(id,name,age,sex)VALUES(3,小蓝,16,女);INSERTINTOtest.user(id,name,age,sex)VALUES(4,小王,17,男);INSERTINTOtest.user(id,name,age,sex)VALUES(5,张三,18,男);INSERTINTOtest.user(id,name,age,sex)VALUES(6,李四,19,男);INSERTINTOtest.user(id,name,age,sex)VALUES(7,王五,20,男);INSERTINTOtest.user(id,name,age,sex)VALUES(8,赵六,21,男);下面尝试在事务中修改姓名为张三的年龄为20岁注意这里先不要着急提交事务BEGIN;UPDATEuserSETage20WHEREname张三;下面查看下前正在发生的数据锁情况SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAMEuser从这里可以看出锁住了表里所有的数据已经上升为了表锁但是上面仅更新了姓名为张三的数据怎么会锁住那么多数据呢这是因为name字段没有索引要找到姓名为张三的数据就要进行全表扫描但是update的时候要保证数据的一致性所以此时相关的数据就是全部的表数据因此也就相当于表锁了。那怎么降低锁的粒度呢既然是因为name字段没有索引那给name增加索引再次进行上面实验呢。增加索引ALTERTABLEuserADDINDEXindex_name(name);再次尝试修改但不提交事务BEGIN;UPDATEuserSETage20WHEREname张三;查看下前正在发生的数据锁情况SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAMEuser可以看出此时并没有锁住全部的数据但是锁住的张三, 5和李四, 6是什么呢这其实是索引信息如果修改操作涉及到了非主键索引MySql会首先锁住非主键索引再锁定具体数据的主键索引。至于会锁住李四就是MySQL中Next-Key和GAP间隙锁的原因了当准备更新张三时以防止在这个范围内插入新的记录所以将临近的李四也进行上锁。从上面结果看增加了索引后已经解决了表锁的问题但还是会锁住多余的内容下面直接尝试根据主键进行修改BEGIN;UPDATEuserSETage20WHEREid5;再次查看下前正在发生的数据锁情况SELECT*FROMperformance_schema.data_locksWHEREOBJECT_NAMEuser此时就仅锁住相关的数据了。二、总结从上面的实验过程来看MySQL中的修改操作很有可能导致表锁因此最好在更新语句中使用主键列或其他索引列进行筛选。另外索引最好设置在不经常变更的字段上比较好不然容易造成冲突死锁的情况。