mysql锁

一、mysql锁的分类

---

• 锁属性：共享锁、排他锁
• 锁粒度：全局锁、表锁、页锁、行锁
• 锁机制：乐观锁、悲观锁
• 锁状态：意向共享锁、意向排他锁
• 锁算法：记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)

二、共享锁、排他锁

---

• 共享锁： 又称S锁、读锁。当事务对数据加读锁后，其他事务只能对锁定的数据加读锁，不能加写锁（排他锁），仅当前一个事务时，在当前事务中加读锁是可以的。加了读锁的行可以不加读锁和写锁照样也可以读。

#加锁方式
select * from T where id=1 lock in share mode;
#释放方式
commit、rollback;

• 排他锁： 又称X锁、写锁。当事务对数据加写锁后，其他事务不能再对锁定的数据加任何锁。DML语句默认是加写锁的。InnoDB对select语句默认不加锁，所以其他事务除了不能写操作外，照样是允许读的。

#加锁方式
#手动加写锁
select * from T where id=1 for update;
#释放方式
commit、rollback;

三、全局锁、表锁、页锁、行锁

---

MyISAM和MEMORY存储引擎采用的是表级锁；BDB存储引擎采用的是页面锁，也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。

• 全局锁： 当数据库处于全局锁的状态时，其他线程的数据更新语句（增删改）、数据定义语句（建表、索引变更、修改表结构等）和更新类事务的提交语句会被阻塞，全局锁常用于全库逻辑备份。 也就是把整库每个表都select出来存成文本。

  • 问题：

    1. 既然要全库只读，为什么不使用set global readonly=true的方式呢？因为在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大。在异常处理机制上也有差异。如果执行flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
    2. 全局锁后主从备份可能数据不一致问题，如果在主库上备份不能执行增删改操作，业务基本上不可用，如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟数据不一致问题。解决方案1：主库记录一个操作日志，从库加全局锁备份完后关闭全局锁，把这段时间主库日志文件里的操作都执行一遍。解决方案2(重点推荐)：innodb引擎中有数据快照版本的功能，这个功能叫MVCC，因为MVCC保留了历史版本的快照，每个快照版本都对应一个事务版本号，而在我们在备份数据的时候会申请一个事务版本号，在读取数据时候只需要读取比自己事务版本号小的数据即可，就是只备份在自己执行操作之前生成的数据。使用 mysqldump –single-transaction 命令备份，其他的数据引擎并不支持MVCC，所以还是会加全局锁的。

    #全局加读锁
    flush tables with read lock
    #全局设置只读
    set global readonly=true
    

• 表级锁: 表级锁分为表锁和元数据锁(MDL)

  • 表锁：在客户端断开的时候会自动释放锁。

    #t1加读锁,t2加写锁
    lock tables t1 read, t2 write
    #主动释放锁
    unlock tables
    

  • 元数据锁（metadata lock）：又称MDL锁。不需要显示使用，在访问表时自动加上，最主要的目的是防止DDL(数据定义语言)与DML(数据操作语言)并发冲突。
    1. MDL读锁：对表数据进行增删改查的时候自动对表加MDL读锁，其它事务就不能对表结构进行变更。
    2. MDL写锁：对表结构进行修改的时候会加MDL写锁，其他事务就不能对表数据进行增删改查也不能对表结构镜像修改。

• 页锁: 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。目前只有BDB引擎支持页级锁。

• 行锁: 行锁是粒度最小锁冲突概率最低并发度最高，但是加锁慢开销大容易发生死锁现象。目前只有innodb引擎支持行锁，行锁通过锁索引来实现，当无法通过索引来加锁时，行锁会转成表锁。MySQL具有自动优化SQ 的功能。低效的索引将被忽略， 在select、update、delete 语句中加锁时，where条件字段不是索引字段，那么这个事务会导致表锁，当索引字段值的重复率低时，甚至接近主键或者唯一索引的效果，普通的索引依然是行锁；当索引字段值重复率高时，MySQL不会把这个普通的索引当做索引，即造成了一个没有索引的 SQL，此时引发表锁。索引不是越多越好，每个表索引应控制在5个以内，当不确定表中哪个字段上建索引时，宁缺勿滥不要随便建，因为索引存放在和这个表相关的一个文件里，是会占用硬盘空间的，每个表都有主键（id），操作时能使用主键尽量使用主键。记录锁、间隙锁和临键锁都属于行锁。

四、乐观锁、悲观锁

---

• 乐观锁: 乐观锁假设数据不会发生冲突，只有在数据提交更新时才会检测是否冲突，如果冲突了才会返回错误信息。适用于读多写少的场景。
• 悲观锁: 悲观锁具有强烈的独占和排他特性，适用于并发量不大、写入比较频繁、数据一致性比较高的场景。Mysql中的共享锁和排他锁都属于悲观锁。

五、意向锁

---

意向锁(Intention Lock)：是针对表锁使用的，mysql自动维护的。当事务给表数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

六、记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)

---

• 记录锁(Record Lock): 在单行记录上加锁，会去锁住索引记录，如果建表的时候没有设置任何索引，InnoDB存储引擎会使用隐式的主键来进行锁定
• 间隙锁(Gap Lock): 锁住一段范围，不锁记录本身，通常表示两个索引记录之间，或者索引上的第一条记录之前，或者最后一条记录之后的锁。
• 临键锁(Next-Key Lock): 相当于是记录锁+间隙锁的组合，锁定一个范围及锁定记录本身。例如一个索引有10,11,13,20这四个值，那么该索引可能被临键锁的区间为(负无穷,10)，(10, 11), (11, 13), (12, 20), (20, 正无穷)。需要理解一点，InnoDB中加锁都是给所有记录一条一条加锁，并没有一个直接的范围可以直接锁住，所以会生成多个区间。

七、死锁

---

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

1. 直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来将等待时间设置短一点，默认是50s。
2. (建议)死锁检测，在发生死锁的时候能够快速发现并进行处理，回滚并重新启动。但是死锁检测会比较好资源。当每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作可能就是 100 万的量级。虽然最终检测的结果可能没有死锁，但是这期间要消耗大量的 CPU 资源。
   • 怎么解决由这种热点行更新导致的性能问题？
   1. 如果确保这个业务一定不会出现死锁，可以临时把死锁检测关掉
   2. 控制并发度
   3. 将一行改成逻辑上的多行来减少锁冲突。以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成员原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗