MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)
InnoDB支持行级锁和表级锁(row-level locking),默认行级锁
锁不能笼统的说哪种好,应该按不同的应用场景选择不同的锁
锁类型
- 表锁
开销小,加锁快,不会出现死锁,锁力度大,发生冲突的概率最高,并发度最低
适合查询为主,只有少量按照索引条件更新数据的应用
- 行锁
开销大,加锁慢,会出现死锁,锁粒度自小发生锁冲突的概率最低,并发度最高
适合有大量索引条件并发更新少量不同数据,同时又有并发查询的应用
- 间隙锁(gap锁,页面锁)
开销、加锁时间和锁定力度介于表锁和行锁之间,或出现死锁,并发度一般
表锁
表共享读锁(Table Read Lock)
加锁方式:lock table 表名 read
表独占写锁(Table Write Lock)
加锁方式:lock table 表名 write
锁类型 | None | 读锁 | 写锁 |
---|---|---|---|
读锁 | 是 | 是 | 否 |
写锁 | 是 | 否 | 否 |
表锁总结
- 读操作,不会阻塞其他用户对同一个表的度操作
- 读操作,不会阻塞当前session对表度,对标进行修改是会报错
- 加读锁,这个session可以查询锁表中的数据,更新会查询其他表会报错
- 写操作,会阻塞其他用户对同一个表的读和写操作
- 写操作,当前session可以对本表crud,对其他表操作会报错
- 开启一个新事物,会自动解锁表
行锁
-
两个事物不能锁同一个索引
-
insert,update,delete操作默认会添加排它锁
-
行锁必须有索引才能实现,否则自动锁全表,变成表锁
共享锁(读锁)
-
当一个事务对某一行上读锁时,允许其他事务进行读操作,但是不允许写操作,也不允许上排它锁,可以上读锁
-
上读锁的方式:在sql语句最后面添加 lock in share mode(如:select * from tabe where xxx=xxx lock in share mode;)
排它锁(写锁)
-
当一个事务对某一行上写锁时,不允许其他事物写,单允许度,不允许其他事务上任何锁
-
上锁方式:在sql语句最后面添加for update(如:select * from table for update;)
间隙锁
根据范围进行锁数据。
注意:间隙锁,在查询和插入是,会匹配插入值
主键列测试:
创建测试数据库:
CREATE TABLE test (a int PRIMARY KEY);
插入测试数据:
insert into test values (1),(2),(5),(10),(20);
步骤 | session A | session B | 说明 |
---|---|---|---|
1 | begin | begin | 两个session分别开启事物 |
2 | select * from test where a <= 6 for update; | session A 执行数据查询 | |
3 | insert into test values (9); | 等待插入 | |
4 | insert into test values (10); | 插入成功 | |
5 | insert test values (8); | 插入成功,同一个事务内 |
在select的语句后边,添加排他锁for update,会使用gap锁(间隙锁)。
在步骤3执行查询时,添加间隙锁,执行步骤4,为等待状态。执行步骤5插入成功。
是因为间隙锁根据查询数据,逐个开始加锁,首先给id:1加锁后判断是不是满足条件,满足则继续向后查询,其次判断id:2满足,id:5满足,然后给id:10加锁,判断不满足,就不再继续向后加锁,但是此时id:10已经加锁,所以在插入大于6小于10的数据,都会等待;
非主键列测试:
创建测试数据库:
CREATE TABLE test (a int PRIMARY KEY, b int, key(b));
插入测试数据:
insert into test values (1,1),(3,1),(5,3),(8,6),(10,8);
步骤 | session A | session B | 说明 |
---|---|---|---|
1 | begin | begin | 两个session分别开启事物 |
2 | select * from test where b = 3 for update; | session A 执行数据查询 | |
3 | insert into test values (4,2); | 等待插入 | |
4 | insert into test values (6,5); | 等待插入 | |
5 | insert into test values (2,0); | 插入成功 | |
6 | insert into test values (6,7); | 插入成功 |
非主键列间隙锁(二级索引锁)
会根据当前数值,查询头和尾的索引进行上锁。
如上:当session A查询b=3时,插入2,和插入5都会等待,插入0和7时成功,由此推断,间隙锁在[1-3]和[3-6]之间加了锁。
解决锁等待的问题
如果mysql中出现死锁的问题,使用kill掉锁的进程号开关闭锁