MYSQL--锁

锁

MYSQL中锁的分类

根据锁的粒度不同，主要分为：全局锁、表级锁、行级锁

• 全局锁

  #加锁
  flush tables with read lock
  #解锁
  unlock tables
  

• 表锁：表锁、MDL元数据锁、意向独占锁/意向共享锁、AUTO_INC意向自增锁

  • 表锁

    #表级别的共享锁，也就是读锁；
    lock tables t_student read;
    #级别的独占锁，也就是写锁；
    lock tables t_stuent write;
    #解锁
    unlock tables
    

  • MDL元数据锁

  • 意向锁，意向锁的目的快速判断表里面是否有独占锁，意向锁只会和意向锁进行冲突，不会和行独占锁冲突，下面的语句是行独占锁和共享锁，但是会自动加表级的意向锁

    #意向独占锁
    select .... for update
    #意向共享锁
    select .... lock in share
    

  • AUTO_INC自增锁，虽然是表级锁，但是执行完立即释放

• 行锁：

  • 行记录锁

    #行独占锁
    select .... for update
    #行共享锁
    select .... lock in share
    

  • 间隙锁(3,5),间隙锁和间隙锁之间不会冲突，但是间隙锁和插入意向锁之间会冲突

    select * from t_test where id>3 and id<5 for update;
    

  • 临键锁(3,5]，next-key 相当于记录锁+间隙锁

  • 插入意向锁，意向锁是用来快速判断这个表里面是否有独占锁的，是表级锁；插入意向锁是行级间隙锁，一个事务不能同时拥有间隙锁和插入意向锁，插入意向锁是插入等待

MYSQL如何实现乐观锁

加上版本号

update student set name='abc' and version=2 where sid='123456' and version=1

在线上修改表结构会发生什么？

会加上表级的元数据锁

一条UPDATE语句没有带WHERE会加什么锁

• 可重复度的隔离级别下，update会为每一个扫描过的字段加上记录锁和间隙锁，锁全表
• 读已提交的隔离级别下，update会为每一个扫描过的字段加上记录锁，所以不会锁插入操作

带了where条件没命中索引会加什么锁？

• 可重复读级别下，会加间隙锁和记录锁
• 读未提交级别下，会加记录锁

两条更新语句更新同一条记录，加的是什么锁？

• 如果是唯一索引

  • 如果存在，锁的是这条记录和间隙锁
  • 如果不存在，给扫描过的都加间隙锁

• 如果是非唯一索引

  • 如果存在，给扫描到的第一条二级索引记录加记录锁和间隙锁
  • 如果不存在，给第一个不符合条件的二级索引记录加间隙锁

• 如果没命中索引，就和上文一下，全部加记录锁和间隙锁

可重复读的场景下最容易发生死锁的是？

A事务更新操作加了记录锁和间隙锁，B事务执行插入意向锁，导致是死锁

你了解过死锁问题吗？

• 死锁的条件：互斥，不可剥夺，循环等待，请求保持
• 可重复度情况下，更新的间隙锁和插入的插入意向锁就会导致死锁

MYSQL怎么排查死锁问题？

可以使用 show engin innodb status，然后根据日志来排查

MYSQL如何避免死锁

• 缩短持锁的时间：大事务拆分成小事务
• 减少间隙锁：将隔离级别改成读已提交，用二级索引减少加锁范围
• 设置MYSQL参数，比如说等待阈值