Mysql锁机制简记

1、概述：

1.1 三种锁

• 表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生冲突概率最高，并发度最低。
• 页面锁：一切参数适中。
• 行级锁：开销大，加锁慢；会出现死锁，锁定粒度小，发生冲突概率最低，并发度最高

1、MyISAM 引擎只支持表级锁，BDB支持页面索和表级锁（被 InnoDB 取代），InnoDB 支持表级锁和行级锁
2、表级锁更适合以查询为主，只需要少量按索引条件更新数据的应用，如 Web 应用。行级锁更适合有大量索引按条件更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。

2、 MyISAM 的表锁

• 表级锁有两种模式：表共享读锁和表共享写锁。

  是代表可以同时作用域同一张表，否代表不可以

  	NONE	读锁	写锁
  读锁	是	是	否
  写锁	是	否	否

• 一个进程对一个表加表锁之后：

  1、加读锁：自己可以读该表，但是***不可以读其他表（我愿称之为专一）****不可以对该表进行更新操作(update、insert、delete)。其他进程可以查询该表，不可以update、delete 。关于 insert 后面说
  2、加写锁：自己可以 CRUD ，其他线程啥都不可以干。

• 并发插入：MyISAM 有一个系统变量：concurrent_insert 专门控制读写的并发行为。空洞是指：表的中间有被删除的行

  concurrent_insert 值	说明
  0	不允许并发插入
  1	当表里没有空洞时，允许一个进程读表的同时，另一个进程在表末尾进行插入
  2	不论有没有空洞，都允许在表末尾进行并发插入

  一种提高效率的方法：将 concurrent_insert 总置为 2 ，同时定期在系统空闲时间执行 potimize table 语句整理空间碎片。  

• MyISAM 中的锁调度

  1、MyISAM 会自己给查询语句加读锁，自动给更新操作加写锁。当然也可以手动加锁。lock table table_name read local    、   lock table table_name write
  2、当一个进程请求某个 MyISAM表 的读锁的同时有一个进程请求同一个表的写锁，Mysql（没写错） 的处理方式是先处理请求写锁的进程。甚至，即使在同一个请求队列中，并且读锁排在写锁之前，还是会处理写进程。
  3、上述原因正是 MyIASAM 不适合有大量更新操作和查询操作的原因，这样会使读进程很难获得锁，甚至永远阻塞。当然可以通过调整一些参数来优化这些情况。
  4、同时，一些大规模的查询语句也可能会 “饿死” 写进程。

3、InnoDB 的锁

• InnoDB 与 MyISAM 的两大区别：支持事务 和 采用行级锁

3.1、背景知识

• 事务及其 ACID 属性：

  事务：一组 SQL 语句组成的逻辑处理单元
  1、原子性：事务是一个原子操作单元，对其要修改的数据，要么全不做，要么全部做
  2、一致性：在事务开始和完成时，数据都必须保持数据的一致状态。数据的完整性和正确性都必须被保证
  3、隔离性：数据库提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境执行。
  4、持久性：事务完成后，对于数据的修改时永久性的，即使出现系统故障也能够保证。

• 并发的问题：

  1、丢失更新：两个事务同时对一条记录进行更新，并不知道对方的存在，会导致数据被覆盖的情况。
  ２、脏读：一个事务在修改一条记录时，另一个事务来读取这条记录，会导致脏读。
  ３、不可重复读：一个事务在读取一条记录后，再来读这条数据，记录已被删除，造成不可重复读。
  ４、幻读：一个事务按相同条件查询之前查过的数据，却发现其他事务插入了满足条件的数据。
  一起修改－－》修改时读－－》读后删除－－》读后增加

• MySQL 的事务等级

  	读数据一致性	脏读	不可重复读	幻读
  读未提交	只保证不读取物理上损坏的数据	是	是	是
  读已提交	语句级	否	是	是
  可重复读	事务级	否	否	是
  序列化	最高级别，事务级	否	否	否

3.2、行锁

• 检查争用情况

  查看锁争用情况：show status like '%InnoDB_row_lock%'
  设置全局监视器：set global InnoDB_status_output=ON
  			 set global InnoDB_status_output_locks=ON
  设置监视器会产生大量日志，查完记得关。把上面两条改为 OFF 即可，可以在启动时设置 --InnoDB-status-file 参数将争用情况写入指定的 InnoDB_status.pid 文件

• 两种行锁 和 两种表锁

  行锁：
      1、共享锁：允许一个事务读一行，阻止其他事务获得该行数据的排他锁。
      2、排他锁：允许获得排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
  表锁：
  	1、意向共享锁：事务在对数据行加行共享锁前，要先取得该表的意向共享锁
  	2、意向排他锁：事务在对数据行加行排他锁前，要先取得该表的意向排他锁
   
  注意事项：
  	1、意向锁是InnoDB 自动加的，不需要用户干预。update、delete、insert 语句也会自动加排他锁。只有select 语句需要显式加锁。
  	2、显式加锁：
  	mysql 5.7:  
  		select * from table_name where ... lock in share mode
  		select * from table_name where ... for update
  	mysql 8.0:  
  		select * from table_name where ... for share 
  		select * from table_name where ... for update [nowait | skip locked]
  	3、in share mode 语句 在 mysql 8 中仍然可以使用；nowait 参数遇到锁争用直接返回错误，skip locked 则会跳过改行更新下一行。
  	4、对锁定行记录需要进行后续更新操作的应直接加排他锁，如果先加共享锁再加排他锁，很容易产生死锁。

• 锁之间的兼容性

  	共享锁（S）	意向共享锁（IS）	排他锁（X）	意向排他锁（IX）
  共享锁	是	是	否	否
  意向共享锁	是	是	否	是
  排他锁	否	否	否	否
  意向排他锁	否	是	否	是

  • 可以看出，IS、IX 之间都是兼容的。IS 只与 X 冲突，IX 和 S、X 冲突：当一个事务对表加了排他锁之后，其他事务不能再加 意向共享锁，进而也不能加共享锁；当一个事务对表加了共享锁或排他锁之后，其他事务不能再加意向排他锁，进而也不能加排他锁；

• 造成死锁的一种情况：

  	事务1先获得了记录的共享锁，事务2也获得了共享锁，接着 1 等待尝试获取该记录的排他锁，接着 2 也尝试获取该记录的排他锁，就会造成死锁，2 会被迫退出，释放他所占有的锁。
  	可能的解释：当 1 尝试获取排他锁的时候，已经有 2 占有了共享锁，这时如果强制获取的话，就可能造成脏读等错误。所以 1 等待 2 释放共享锁，结果 2 又尝试获取排他锁，也成为了 1 的情况，都想对方释放资源，就是死锁了。
   

3.3、行锁的实现方式

• 三种实现方式：

  1、record lock : 对索引项加锁；
  2、gap lock :  对索引项之间的间隙，即第一条之前的间隙或者最后一条之后的间隙加锁
  3、next-key lock : 对前两种都加锁。
   
  注意: *** 这种实现方式意味着，如果不通过索引项检索数据，那么将对整个表的数据加锁，效果与表锁一样。会造成大量的锁冲突。

• 几种情况解释：

  1、没有索引的情况。事务 1 获得一行记录的排他锁之后，事务 2 再请求其他行的排他锁，也会等待。因为没有索引时，是表锁。
  2、因为 InnoDB 的锁是行锁，所以如果两次 select 使用的是相同的键，也会造成锁的冲突，会等待。
  	select * from table where id=1 and name='4' 和 select * from table where id=1 and name='2' 会有冲突。
  3、不同的事务可以使用不同的索引对不同的数据记录加锁。相同的记录仍需要根据锁的兼容来判断是否可以加锁
  4、一些情况会导致索引失效，即使加了索引字段可能也不会起效。

• next-key 锁

  • 当使用范围条件检索而不是 eq 时，InnoDB 会给所有符合条件的数据加锁，包括在键值范围内但不存在的数据

  • 举个例子：

    select * from table where id>100 for update;
    	这时，即使这张表只有 101 条数据，也会对大于101的间隙（表空间的间隙）加锁。这样做一方面是为了防止幻读，满足更高隔离级别的要求。另一方面，也是满足恢复和赋值的需要。
    	但是，显然这样会影响并发插入，对于需要并发插入需求的业务。要尽量使用 eq 检索，而不要使用范围查询。
    **另外，如果使用 eq 给不存在的数据加锁，也会触发 next-key 机制。
    比如，表只有100 条数据：
    	1： select * from table where id=101 for update;
    	2: insert into table values (200,...)
    事务2 也会被阻塞。

    可以看出，如果使用了 next-key 锁，事务隔离级别就将提高到序列化

3.4、数据恢复和复制的需要对锁机制的影响

• 恢复机制：MySQL 通过 BINLOG 记录执行成功的insert、delete、update 操作，并基于此实现数据库的恢复和主从复制。

• 三种日志格式和四种复制模式

  日志格式：基于语句SBL、基于行RBL、混合格式
  复制模式：
  	--基于语句SBR：最早的复制模式
  	--基于行RBR：优点是支持对非安全 sql 的复制
  	--混合复制模式：对安全的 sql 采取SBR、对不安全的采用 RBR
  	--使用全局事务ID(GTIDs) ：主要解决主从自动同步问题

• 对 基于SBL 的恢复和复制而言，由于 BINLOG 是按照事务提交的先后顺序记录的，因此想要正确恢复数据，就必须序列化，不可以并发插入。

• 例子：

  	事务1：inset into target_tab select * from source_tab where name='1';
  	事务2：update source_tab set name='1' where name='8'
  	事务2会被阻塞，mysql 给记录加了共享锁。目的还是为了保证数据的可恢复和可复制。我们之前说过，在 MySQL 里面，数据的更新操作优先级要高于查询操作，如果不加锁，并发量较大时是更新语句先执行，造成结果和我们预想的不一致。
  	**当然也可以通过设置系统变量 InnoDB_locks_unsafe_for_binlog=on (默认值未 off )来取消这种情况下的共享锁。

• 因此 mysql 不推荐使用 inset into target_tab select * from source_tab where name='1'; 这样会阻止并发更新的语句

  如果不得不使用，解决方式：

  1、设置 InnoDB_locks_unsafe_for_binlog=on ，强制采用多版本数据一致性读。但是可能会造成 BINLOG 错误无法复制和恢复
  2、通过使用 select * from source_tab ... into outfile 来间接实现。
  3、使用基于行的 BINLOG　格式和基于行的数据的复制。

• InnoDB 在不同隔离级别下的一致性读和加锁策略是不同的。这里不详细说了。
  

• 在特殊情况下选择表锁

  • 一个事务需要更新大部分或者全表数据。直接加表锁可以减少冲突和等待。
  • 一个事务涉及多个表，可能会引起死锁，就直接锁定所有需要的表。

• 使用表锁的注意事项：

  • 表锁不是有InnoDB 引擎管理的而是由其上一级 MysqlServer来管理的。只有 autocommit=0 && inno_table_locks=1 时，InnoDB才能感知到表锁的存在以及表锁造成的死锁等状况。
  • 使用 lock tables给表加锁时，要注意在事务提交之前不能直接使用 unlock tables 解锁，它会隐含提交事务。

4、死锁

• MyISAM 总是一次性获得所有的锁，所以不会产生死锁情况。而 InnoDB 则是逐步获得记录的锁，所以可能产生死锁。
• 对于行锁的死锁，InnoDB 能自动检测，并使一个事务回退使另一个能正常完成。但是对于外部锁或者表锁，需要同古设计超时等待时间 innodb_lock_wait_timeout 来解决。并发访问较高时，也可以通过设置这个参数来解决大量事务挂起的问题

4.1几种常见的避免死锁的方法：

• 如果不同的程序会并发存取多个表，尽量约定采用相同的顺序来访问表。
• 在程序以批量方式处理数据时，如果实现对数据排序，使每个线程按照固定的顺序来处理记录，可以大大降低死锁出现的可能。
• 如果一个事务要更新记录，直接加排他锁，而不要先加共享锁再加排他锁，前面也讨论过这种情况
• 在 repeatable-read 隔离级别下，如果两个线程同时对一条记录使用 select ... for update 来加给记录加排他锁，如果记录不存在，两个记录都会加锁成功。但如果接着两个线程都尝试插入新记录，就会出现死锁。如果将隔离级别改为 read-committed 就可以避免。在 read-committed 级别下，一个事务会枷锁成功，另一个会等待，等待一个事务插入成功后，会出现主键重复的错误。虽然出现了错误，但是没有死锁，这个事务会给该记录加上排他锁。
• 关于上面一点，我还是不太清楚，如果了解的，欢迎评论