MySQL锁机制
一、基本概念
从操作的类型上来看,分为读锁和写锁:
读锁:共享锁,对同一份数据,多个读操作可以同时进行且相互间不影响
写锁:排它锁,独占资源。在当前操作未完成之前,其他写操作必须等待。读操作不影响。
排它锁作用于innodb,且必须在事务块中执行。在进行事务操作时,for update会对结果集中的每一行数据加排它锁,其他线程对于结果集中的数据进行修改操作,全部阻塞。
从锁数据的细粒度上来看,分为行锁和表锁。
二、测试
测试环境:mysql 5.5.6、Navicat for mysql。
新建表:
CREATE TABLE `tb_user` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `password` varchar(10) DEFAULT NULL, `sex` char(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
开启两个查询会话,模拟多请求。
1、表锁
锁的粒度偏大,开销小,锁表快,但是发生锁竞争的概率特别高,并发度低。
对于更新update、delete、insert自动加写锁。也可如下的显式命令加锁:
基本命令分析:
加锁:LOCK TABLE tablename WRITE/READ;
释放:UNLOCK TABLES;
查询表是否加锁:show open tables;
表锁分析:SHOW STATUS LIKE 'table%';
结果返回两个参数:Table_locks_immediate表示产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1。
Table_locks_waited 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1)。
1.1、读锁
不阻塞对加锁表的读操作,但是在当前会话中,不可对其他表查询。其他会话的对加锁表的更新,也会阻塞等待锁释放。
-- session01 加表级读锁 |
-- session02 |
-- 查询锁定的表 |
-- 查询锁定的表 |
-- 查询其他未锁定表,报错:[Err] 1100 - Table 'tbl_user_mycat' was not locked with LOCK TABLES |
-- 更新锁定的表,会阻塞,直到锁释放后,再继续完成执行操作 UPDATE tb_user SET `name`='heihei'; |
-- 释放锁 |
|
上述更新完成。 |
1.2、写锁
其他会话中,不能对加锁表进行读写操作。在释放锁之前,也不能对其他未加锁表进行读写操作。
-- session01 加表级写锁 |
-- session02 |
-- 查询锁定的表 -- 更新锁定的表 |
-- 查询其他未锁定表 |
-- 查询其他未锁定表,报错:[Err] 1100 - Table 'tbl_user_mycat' was not locked with LOCK TABLES |
-- 更新锁定的表,会阻塞,直到锁释放后,再继续完成执行操作 |
-- 释放锁 |
|
上述所有的对加锁的表的读写操作,会执行完成 |
2、行锁
锁粒度较小(查询结果集的记录行),发生锁竞争概率较低,并发度高。但是,可能会出现死锁。通常,事务和行锁是在确保数据准确的基础上提高并发的处理能力。
基本命令分析:SHOW STATUS LIKE 'innodb_row_lock%';
Innodb_row_lock_current_waits:当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits :系统启动后到现在总共等待的次数
一般来说,关注Innodb_row_lock_waits(等待总次数)、Innodb_row_lock_time_avg(等待平均时长)比较高的时候,说明系统中竞争比较激烈,资源处理慢或者其他什么原因,具体再查询分析结果,制定相关的优化。
innodb在通过索引条件检索数据的时候,会加行锁。否则,都是加的表锁。也就是说:innodb加行锁是针对索引,而不是记录行。没有索引或者索引失效,都会升级为表锁。
对于update、delete和insert语句,innodb会自动的给结果集加排它锁。select默认是不做任何操作的。当然,显式的加锁也是可以滴:
共享锁(读锁,多个读锁可同时进行,但是若事务中对读锁记录做修改操作,很有可能会发生死锁):SELECT * from tb_user where id=10010 LOCK IN SHARE MODE;
排它锁(写锁,在当前事务未commit之前,阻塞其他的读锁和写锁):SELECT * from tb_user where id=10010 FOR UPDATE;
以下,2.1和2.2基于update来说明上述的自动加锁机制。2.3和2.4举例来说明显式的共享锁和排它锁问题。
2.1、对于索引列的where,加行锁:
-- SESSION01 开启事务 |
|
-- SESSION02 开启事务 |
|
COMMIT; | |
COMMIT; | |
2.2、对于不是索引列的where,加表锁:
-- SESSION01 开启事务 |
|
-- SESSION02 开启事务 |
|
COMMIT; | |
-- session01提交commit,上述更新update完成操作 | |
COMMIT; |
2.3、共享锁
-- SESSION01 开启事务 |
|
-- SESSION02 开启事务 |
|
-- 对读锁进行修改,等待session02提交commit,阻塞 UPDATE tb_user set name='testname1' WHERE id=10010; |
|
-- 报错:[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction UPDATE tb_user set name='testname1' WHERE id=10010; |
|
-- 操作继续。session02中,mysql判断出现死锁,回滚session02后,session01继续操作 |
2.4、排它锁
-- SESSION01 开启事务 |
|
-- SESSION02 开启事务 |
|
-- 当前事务中可执行 |
-- 排它锁,阻塞等待session01提交commit,释放锁 UPDATE tb_user set name='testname2' WHERE id=10010; |
COMMIT; |
|
-- session01提交commit,上述阻塞操作继续执行完成 COMMIT; |
2.5、间隙锁
当使用范围查询,且申请共享锁或者排它锁的时候,InnoDB会给符合条件的已有的结果集的索引加锁。对于在范围内但是不存在的的记录值,叫做“间隙(GAP)”。InnoDB加锁也会对这些间隙进行加锁,成为间隙锁。
在锁定一个很大的范围之后,即使记录不存在,也会被锁定。这就导致,如果此时有其他插入这些不存在的记录的请求,会一直阻塞等待。很容易对性能产生影响。
三、使用和总结
1、Innodb默认使用的是行锁。当未使用索引列查询限定的时候会升级为表锁。
一般,在检查分析锁冲突的时候,必须explain查看sql的执行计划。因为mysql在执行过程中,并不是一定会使用索引(explain查看执行计划的时候,才会有possible_key和key),有可能的情况是mysql认为全表扫描更快,那么此时就不会用行锁,而升级使用表锁。
2、Innodb默认自动给更新操作加锁