数据库进阶
锁
- 目的
- 解决并发情况下资源抢夺问题, 维护数据的一致性
-
mysql的锁虽然开发者可以手动设置, 但比较影响并发性, 一般会使用乐观锁代替( 如Django中到库存问题)
-
由于mysql会自动使用锁, 所以需要了解锁机制, 以便优化数据库并发能力
- 粒度/覆盖范围
- 表级锁
- 对整个表锁定,并发差,资源消耗少
- 行级锁
- 对数据行锁定,并发好,资源消耗多
- 不同数据库引擎支持的锁也不同
- MyISAM(5.5之前默认)支持表级锁
- InnoDB支持行级锁和表级锁
- 表级锁
- 锁和事务
- 无论操作是否在事务中,都可以获取锁,只不过在事务中,获取的锁只有执行完事务才会释放
- MyISAM
- 只支持表级锁
- 表读锁/共享锁
- 获取后,其他请求可以读不能写
- 表写锁/排它锁
- 获取后,其他请求即不可以读也不能写
- 加锁方式
- 数据库自动管理,查询前给涉及的表添加读锁,更新、删除、修改前给涉及的表加写锁
- InnoDB
- 支持行级锁和表级锁,优先使用行级锁
- 行共享锁
- 获取后,其他事务也可以获取目标集的共享锁,但是不能获取目标集的排它锁(排队等待)
- 行排它锁
- 获取后,其他事务既不能获取目标集的共享锁,也不能获取对应的排它锁
- 加锁方式
- 增删改必须获取排它锁,普通查询不需要获取锁
- 加锁查询
select * from t_user where name = 'xx' lock in share mode 获取目标集共享锁后,执行查询
select * from t_user where name = 'xx' for update
获取目标集排他锁后,执行查询
- 行锁与读写权限
- 行共享锁
- 获取行共享锁后,当前事务可以读(不影响),不一定能写(其他事务也获取读锁,只能等待);其他事务可以读,不能写
- 共享锁容易出现死锁陷阱
-
1 #准备数据 2 create table t_deadlock( 3 id int not null auto-increment, 4 name varchar(20), 5 type int, 6 key (type), 7 primary key (id) 8 ); 9 10 insert into t_deadlock (name,type) VALUES ('zs',1); 11 insert into t_deadlock (name,type) VALUES ('ls',2); 12 insert into t_deadlock (name,type) VALUES ('ww',3);
-
-
1 # 需求: 对zs的type做加1操作, 为防⽌资源抢夺(更新丢失), 设置锁 2 --事务1------------- 3 begin; 4 select type from t_deadlock where name='zs' lock in share mode; # 共享锁 5 --事务2------------- 6 begin; 7 select type from t_deadlock where name='zs' lock in sharemode; # 共享锁 8 --事务1------------- 9 update t_deadlock set type=2 where name='zs'; # 等待事务2释放共享锁 10 -事务2------------- 11 update t_deadlock set type=2 where name='zs'; # 等待事务1释放共享锁 12 # 相互等待, 产生死锁
-
1 #更新丢失的解决方法: 2 1.使用update子查询更新(乐观锁) 3 update t_deadlock set type=type+1 where name='zs'; 4 2.查询时直接使用排它锁(悲观锁) 5 select type from t_deadlock where name='zs' for update;
-
- 行排他锁
- 获取后,当前事务既可以读,也可以写;其他事务可以读,不能写
-
1 # 需求: 记录的数量=3,才插入一条数据 2 --事务1------------- 3 begin; 4 select count(*) from t_deadlock; # 获取记录数量为3 5 --事务2------------- 6 begin; 7 select count(*) from t_deadlock; # 获取记录数量为3 8 --事务1------------- 9 insert into t_deadlock (name, type) values ('zl', 1); 10 commit; # 插入成功 11 --事务2------------- 12 insert into t_deadlock (name, type) values ('fq', 1); 13 commit; # 插入成功, 结果插入了两条数据
1 # 并发插⼊的解决办法: insert后边不能直接连接where, 并且insert只锁对应的行, 2 不锁表, 不会影响并发的插入操作(无法使用乐观锁完成需求), 只能在查询时就手动设置 3 排它锁(悲观锁) 4 --事务1------------- 5 begin; 6 select count(*) from t_deadlock for update; # 获取记录数量为3 7 --事务2------------- 8 begin; 9 select count(*) from t_deadlock for update; # 等待获取排它锁 10 --事务1------------- 11 insert into t_deadlock (name, type) values ('zl', 1) 12 commit; # 插入成功 13 --事务2------------- 14 select count(*) from t_deadlock for update; # 事务1完成, 获取到记录数量为4, 不再执行插入操作
- 行锁是通过给索引加锁实现的,如果查询时没有触发索引,就会锁表
- 合理的索引很重要
- 使用RC级别,只锁行,不锁表
- 间隙锁
- 在击中索引的情况下, 获取行锁时, InnoDB不仅会对符合条件的已有数据行加锁(record lock),
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁( gap lock)
-
InnoDB完整的行锁机制为next key lock(下键锁) = record lock + gap lock
- 缺点
- 会阻塞符合条件的插入操作
-
1 #gap锁场景1:使用范围条件 2 3 begin; #事务1 4 select * from t_user where age<30 for update; 5 #如果此时事务2插入记录(age<30),则会阻塞 (age不是索引触发表锁,age是索引触发的是间隙锁) 6 7 #gap锁场景2:锁定索引的前后区间 [prev,next) 8 update t_user set name='lisi' where age=30; 9 #如果age为索引,且数据中最接近age=30的值为20和40,则age=[20,40)的范围也会被锁定
- 目的
- 防止幻读
- 解决办法
- 尽量不要对有频繁插入的表进行范围条件的检索
- 使用RC级别(不存在间隙锁)
- 使用唯一索引/主键索引进行查询(间隙锁只会对普通索引生成)
-
1 #查看隔离级别 2 select @@global.tx_isolation, @@session.tx_isolation; 3 4 #设置隔离级别(重启后会重置) 5 SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ 6 UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE] 7 8 #修改配置文件设置隔离级别(重启不重置) 9 [mysqld] 10 transaction-isolation = READ-COMMITTED
- 在击中索引的情况下, 获取行锁时, InnoDB不仅会对符合条件的已有数据行加锁(record lock),
