Mysql--锁

• 锁目的
  • 解决并发情况下资源抢夺问题, 维护数据的⼀致性
  • mysql的锁虽然开发者可以⼿手动设置, 但⽐比较影响并发性, ⼀般会 使⽤用乐观锁代替 ( 如Django中
    到库存问题)
  • 由于mysql会⾃自动使⽤用锁, 所以需要了了解锁机制——个别情况必须要加悲观锁，以便便优化数据库并发能力

---

• 粒度/覆盖范围
  • 表级锁
    • 对整个表锁定, 并发差, 资源消耗少
  • 行级锁
    • 对数据行锁定, 并发好, 资源消耗多
  • 不同数据库引擎支持的锁也不同
    • MyISAM (5.5之前默认) 支持表级锁
    • InnoDB 支持行级锁和表级锁

---

• 锁和事务
  • 无论操作是否在事务中, 都可以获取锁, 只不过在事务中, 获取的锁只有执行完事务才会释放
• MyISAM
  • 只支持表级锁
  • 表读锁/共享锁
    • 获取后, 其他请求可以读不能写
  • 表写锁/排它锁
    • 获取后, 其他请求既不能读也不能写
  • 加锁方式
    • 数据库自动管理, SELECT前给涉及的表添加读锁, 更新前(增删改)给涉及的表加写锁
• InnoDB
  • 支持行级锁和表级锁, 优先使⽤用行级锁
  • 行共享锁
    • 获取后, 其他事务也可以获取目标集的共享锁, 但是不能获取目标集的排它锁(排队等待)
  • 行排它锁/互斥锁
    • 获取后, 其他事务既不能获取⽬标集的共享锁,也不能获取对应的排它锁
  • 加锁方式
    • 增删改必须获取排它锁, 普通查询不需要获取锁
    • 加锁查询
      • SELECT * FROM t_user WHERE name = 'xx' LOCK IN SHARE MODE获取目标
        集共享锁后, 执行查询
      • SELECT * FROM t_user WHERE name = 'xx' FOR UPDATE获取目标集排他锁
        后, 执⾏查询

---

• 行锁与读写权限
  • 行共享锁
    • 获取行共享锁后, 当前事务可以读(不影响), 不⼀定能写(其他事务也获取读锁, 只能等待);
      其他事务可以读, 不能写
    • 共享锁容易出现死锁陷阱

    # 准备数据
    create table t_deadlock(
    id int not null auto_increment,
    name varchar(20),
    type int,
    key (type),
    primary key (id)
    );
    insert into t_deadlock (name, type) VALUES ('zs', 1);
    insert into t_deadlock (name, type) VALUES ('ls', 2);
    insert into t_deadlock (name, type) VALUES ('ww', 3);

    # 需求: 对zs的type做加1操作, 为防止资源抢夺(更新丢失), 设置锁
    --事务1-------------
    begin;
    select type from t_deadlock where name='zs' lock in share
    mode; # 共享锁
    --事务2-------------
    begin;
    select type from t_deadlock where name='zs' lock in share
    mode; # 共享锁
    --事务1-------------
    update t_deadlock set type=2 where name='zs'; # 等待事务2释放
    共享锁
    --事务2-------------
    update t_deadlock set type=2 where name='zs'; # 等待事务1释放
    共享锁
    # 相互等待, 产生死锁

    # 更新丢失的解决办法:
    1.使⽤用update子查询更更新 (乐观锁)
    update t_deadlock set type=type+1 where name='zs';
    2.查询时直接使用排它锁 (悲观锁)
    select type from t_deadlock where name='zs' for update;

  • 行排他锁
    • 获取后, 当前事务既可以读, 也可以写; 其他事务可以读, 不能写

    # 需求: 记录的数量=3,才插入⼀条数据
    --事务1-------------
    begin;
    select count(*) from t_deadlock; # 获取记录数量为3
    --事务2-------------
    begin;
    select count(*) from t_deadlock; # 获取记录数量为3
    --事务1-------------
    insert into t_deadlock (name, type) values ('zl', 1);
    commit; # 插⼊成功
    --事务2-------------
    insert into t_deadlock (name, type) values ('fq', 1);
    commit; # 插入成功, 结果插入了两条数据

    # 并发插⼊入的解决办法: insert后边不不能直接连接where, 并且insert只锁对应的行,
    不锁表, 不会影响并发的插入操作(⽆法使⽤用乐观锁完成需求), 只能在查询时就手动设置
    排它锁(悲观锁)
    --事务1-------------
    begin;
    select count(*) from t_deadlock for update; # 获取记录数量为3
    --事务2-------------
    begin;
    select count(*) from t_deadlock for update; # 等待获取排它锁
    --事务1-------------
    insert into t_deadlock (name, type) values ('zl', 1);
    commit; # 插⼊成功
    --事务2-------------
    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
  • 缺点
    • 会阻塞符合条件的插入操作

    # gap锁场景1: 使⽤用范围条件
    begin; # 事务1
    select * from t_user where age < 30 for update;
    # 如果此时事务2插入记录(age<30),则会阻塞 (age不是索引触发表锁, age是索引触发的
    是间隙锁)
    # gap锁场景2: 锁定索引的前后区间 [prev, next)
    update t_user set name='lisi' where age = 30;
    # 如果age为索引, 且数据中最接近age=30的值为20和40, 则age=[20, 40)的范围也会被
    锁定

  • 目的
    • 防止幻读
  • 解决办法
    • 尽量不要对有频繁插入的表进行范围条件的检索
    • 使用 RC 级别(不存在间隙锁)
    • 使用唯⼀索引/主键索引进行查询 (间隙锁只会对普通索引生成)

    # 查看隔离级别
    SELECT @@global.tx_isolation, @@session.tx_isolation;
    # 设置隔离级别(重启后会重置)
    SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ
    UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
    # 修改配置文件设置隔离级别(重启不重置)
    [mysqld]
    transaction-isolation = READ-COMMITTED

转载于:https://www.cnblogs.com/oklizz/p/11453780.html