MySQL锁

MySQL提供了三种类型的锁定机制（粒度从大到小）：表级锁、页级锁、行级锁

• 表级锁

• MyISAM、Memory、CSV引擎

• 表粒度锁定

• 优点：实现简单，获取锁和释放锁速度快，不会发生死锁

• 缺点：资源争用率高，系统并发度低

• 页级锁

• BerkeleyDB引擎

• 粒度介于表级锁和行级锁之间，会发生死锁
  

• 行级锁 

• InnoDB、NDB Cluster引擎

• 行粒度锁定，由引擎实现，基于索引

• 优点：资源争用率低，系统并发度高

• 缺点：实现复杂，锁定同样的数据量需要消耗的内存大

• 分类：共享锁（S）、排他锁（X）

• InnoDB锁

• 行锁

• 基于索引实现，查询条件无索引时，RR级别会对所有扫描行加锁，RC级别会对所有扫描行加锁然后不符合条件是行释放锁，可以认为退化成了表锁
  

• 参数innodb_locks_unsafe_for_binlog

• 默认0（disable，启用gap锁，即使用Next-key锁）

• 1（enbale，禁用gap锁，即使用Record锁）

• 注：外键和唯一索引（含主键）需要对gap进行加锁，innodb_locks_unsafe_for_binlog=disable的设置无效
  

• Record锁

        锁定一行记录的索引


• Gap锁

• 间隙锁，锁定索引区间，左开右闭

• Gap锁使用条件

    1、locking read，update、delete操作

    2、select操作，隔离级别RR或以上且检索条件走索引

    3、唯一索引、等值查询会用Record锁

• RR条件下解决幻读

            避免间隙内插入新数据、间隙外数据更新成间隙内

            间隙：如表中有id为1、3、5的数据，则可以划分为(-∞,1，无间隙]、(1,3，有间隙]、(3,5，有间隙]、(5,索引最大值，无间隙]

示例：id>=2 and id<4之间数据条数不能变，gap锁会锁定一个范围（2所在区间最小值, 4所在区间最大值）即（1,5），record锁会锁定id=2

• Next-key锁

        相当于Record锁+Gap锁

• 表锁

• 意向锁

  InnoDB为了同时兼容行级锁和表级锁，使用了意向锁的概念，也就有了意向共享锁（IS）、意向排他锁（IX）




• 事物特性

• Atomicity：原子性

• Consistency：一致性

• Isolation：隔离性，多个事务操作同一数据，不互相影响

• Durability：持久性
  

• 数据库并发冲突

• 读-读：不存在任何问题，也不需要并发控制

• 读-写：有线程安全问题，可能会造成事务隔离性问题，脏读、幻读、不可重复读

• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

• 第一类更新丢失：回滚丢失，A事务回滚覆盖B事务已经提交的数据，B事务数据丢失

• 第二类更新丢失：覆盖丢失，A事务提交覆盖B事务已经提交的数据，B事务数据丢失

• 数据库并发冲突产生的问题

• 脏读

• 不可重复读

• 幻读
  

• 数据库并发冲突问题解决方案（隔离级别，锁实现）

• Read Uncommitted

  直接不隔离
  

• Read Committed

  Record锁，解决脏读

• Repeatable Read（默认）

  Next-key锁，解决脏读、不可重复读、幻读
  

• Serializable

  读加共享锁，写加排它锁

• Repeatable Read如何解决幻读

• 读分为快照读和当前读，快照读使用MVCC解决幻读，当前读使用Gap锁解决幻读

• PS：前提是事务中读类型一样，如果依次出现快照读+当前读还是会出现幻读
  

• 隔离级别应该用哪个？

  推荐Read Committed，产生冲突和死锁的可能性较小
  

• 为什么MySQL默认隔离级别是RR

  5.6版本之前MySQL的binlog格式使用statement，在RC级别下statement有bug，所以只能使用RR

• MySQL中binlog格式

• statement：记录每一条修改数据的sql

• row：记录修改后的数据

• mixed：根据sql类型选择statement或ro

• 隔离级别和binlog最佳实践

RC + row

• 当前读（悲观锁）

  读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。如select lock in share mode(S锁), select for update ; update, insert ,delete(X锁)等操作都是当前读

  B+树索引存储的都是最新数据（可能未提交）

• 快照读（乐观锁）

  读取的是记录的可见版本（可能是历史版本），不加锁，快照读的实现基于MVCC，主要是为了提高并发性能。如不加锁的select操作就是快照读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读

• MVCC（Multiversion Concurrency Control，多版本并发控制）

• 为了解决读-写冲突时不加锁非阻塞并发读方案，提高数据库并发性能（PS：MVCC解决不了写写冲突）

• RC、RR
  

• 实现原理

每个事务都有一个事务id（按时间顺序递增），数据每次修改都保存一个版本，版本与事务id关联，读操作只读该事务开始前的数据库的快照；

主要依赖3个隐式字段（每行记录都存在），undo日志 ，Read View实现

• 隐式字段

• DB_TRX_ID

  6byte，插入/最近修改该行记录的事务id；select会读取DB_TRX_ID小于或等于当前事务id版本号

• DB_ROLL_PTR

  7byte，回滚指针；指向undo日志记录的上一个版本数据（存储于rollback segment里）

• DB_ROW_ID

  6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

• undo日志

• insert undo log

  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

• update undo log（MVCC使用）

  事务在进行update或delete时产生的undo log，记录update或delete之前的记录

  事务回滚和快照读时都需要，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

  多个undo log形成一个链表，每次更新产生新log都会放在链首
  

• Read View（快照）

• 事务进行快照读操作时生成一个数据库系统当前的快照，记录并维护系统当前活跃事务的ID列表

• 主要是用来做可见性判断，当事务执行快照读的时候，对该记录创建一个Read View读视图，根据可见性判断确定当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据

• 遵循一个可见性算法，即将被修改数据的最新记录中的DB_TRX_ID（即当前事务ID）与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID与
  

• 工作过程
  

• select操作，InnoDB只查找版本早于当前事务版本的数据行

• INSERT：InnoDB为新插入的每一行保存当前事务编号作为行版本号。

• DELETE：InnoDB为删除的每一行保存当前事务编号作为行删除标识。

• UPDATE：InnoDB为插入一行新记录，保存当前事务编号作为行版本号，同时保存当前事务编号到原来的行作为行删除标识