数据库进阶

锁　　

• 目的
  • 解决并发情况下资源抢夺问题, 维护数据的一致性
  • 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

       

原文：https://www.cnblogs.com/Infernal/p/11147831.html