锁机制
Mysql锁分为表锁和行锁,表锁虽然开销小,锁表快,但高并发下性能低。行锁虽然开销大,锁表慢,但是并发性能高。InnoDB采用的行锁,支持事务;MyISAM采用表锁不支持事务
InnoDB行锁会变表锁吗?
案例分析:
创建表结构
CREATE TABLE `user` (
`ID_` bigint(20) NOT NULL AUTO_INCREMENT,
`AGE_` int(11) DEFAULT NULL,
`NAME_` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID_`),
KEY `AGE_AND_NAME_` (`AGE_`,`NAME_`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4
注意:AGE_和NAME_采用了组合索引
案例一: 通过主键修改
时间顺序 | Transaction-A | Transaction-B |
---|---|---|
1 | 开启事务 | |
2 | update user set NAME_=‘测试1.1’ where ID_=1; | |
3 | 开启事务 | |
4 | update user set NAME_=‘测试2.1’ where ID_=2; | |
5 | 修改ID=2,修改成功 | |
6 | update user set NAME_=‘测试1.2’ where ID_=1; | |
7 | 修改ID=1,阻塞中 | |
8 | 提交事务 | |
9 | 修改ID=1的命令自动执行,耗时10s |
# Transaction-A
set autocommit = 0;
update user set NAME_='测试1.1' where ID_=1;
#10S稍后提交
commit;
# Transaction-B
set autocommit = 0;
update user set NAME_='测试2.1' where ID_=2; #命令1
Affected rows : 0, Time: 0.00sec
update user set NAME_='测试1.2' where ID_=1; #命令2
Affected rows : 1, Time: 10.13sec
由上图可知 事务A开启事务后,事务B中 命令1正常修改,但是命令2处于阻塞当中。事务A提交事务之后,命令2自动执行。
结论:多个事务操作同一行数据时,后来的事务处于阻塞等待状态。采用了行锁的方式
案例二: 通过唯一索引修改
案例一通过主键修改,下面通过唯一索引修改
#Transaction-A
set autocommit = 0;
update user set NAME_='测试1.1' where AGE_=1;
#10S稍后提交
commit;
#Transaction-B
set autocommit = 0;
update user set NAME_='测试2.2' where AGE_=2; #命令1
Affected rows : 0, Time: 0.00sec
update user set NAME_='测试1.2' where AGE_=1; #命令2
Affected rows : 1, Time: 10.96sec
和案例一的结论一样,都采用了行锁的方式
案例三
我们把AGE_去掉索引,只保留NAME_索引
#Transaction-A
set autocommit = 0;
update user set NAME_='测试1.1' where AGE_=1;
#10S稍后提交
commit;
#Transaction-B
set autocommit = 0;
update user set NAME_='测试2.2' where AGE_=2; #命令1
Affected rows : 0, Time: 10.61sec
update user set NAME_='测试1.2' where AGE_=1; #命令2
Affected rows : 1, Time: 0.00sec
通过案例3看到,事务A开始事务,事务B中命令1处于阻塞当中,事务A提交事务后,命令1自动执行,命令2自动执行
总结:
通过上面三个案例得知,InnoDB通过索引查询,使用的行锁,如果非索引查询会采用表锁。
InnoDB默认是行锁,前提条件是建立在索引之上的。如果筛选条件没有建立索引,会降级到表锁。即如果where条件中的字段都加了索引,则加的是行锁;否则加的是表锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的。所以,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。其他注意事项:
- 在不通过索引条件查询的时候,InnoDB使用的是表锁,而不是行锁。
- 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以即使是访问不同行的记录,如果使用了相同的索引键,也是会出现锁冲突的。
- 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段,但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
锁冲突 死锁
行锁锁的一行数据,所以会发生死锁。
比如:事务A锁住了第1行,事务B锁住了第2行,此时事务A请求锁住第2行,会阻塞到事务B释放第2行,同时事务B也请求锁住第一行,也会阻塞到事务A释放第1行,就会造成死活,会产生Deadlock错误
锁的类型
-
锁分 共享锁 和 排它锁
共享锁(S):允许一个事务去读一行,阻止其他事务获取相同数据集的排它锁。
排它锁(X):允许获得排它锁的事务更新数据,阻止其他事务获取相同数据集的共享锁和排它锁InnoDB对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;
主动加锁的方式:- 共享锁(S):SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
- 排他锁(X) :SELECT * FROM table_name WHERE … FOR UPDATE
-
意向锁
意向锁是表级别的锁。分为意向读锁, 意向写锁。意向锁树数据库层面控制的;
Mysql为了解决行锁和表锁共存的问题,引入了意向锁。意向读锁和意向写锁。