表锁（偏读）

偏差 MyISAM 存储引擎 。开销小，加锁快，无死锁，锁定粒度大， 产生锁抵触的概率最高，并发最低。

• 查看以后数据库中表的上锁状况，0 示意未上锁。

show open tables;

• 增加读 / 写锁

lock table 表名 read(write) , 表名 2...

• 开释表锁

unlock table;

加读锁（共享）

• 以后会话和其余会话均可读取加了读锁的表
• 以后会话不能读取其余表（先把本人的事干好）
• 其余会话要批改加了读锁的表，必须期待锁开释（阻塞状态）

加写锁（独占）

• 以后会话能够读取和批改加了写锁的表
• 以后会话也不能读取其余的表（先把本人的事干好）
• 其余会话想要读取加了写锁的表，必须期待锁开释（阻塞状态）

论断

MyISAM 在执行查问语句时，会主动给波及的所有表加 读锁 。再执行增删改操作时，会主动给波及的表加 写锁。

简而言之，就是读锁会阻塞写，然而不会阻塞读。而写锁则会把读和写都阻塞。

表锁的剖析

• 查看哪些表被锁了，0 表锁未锁：show open table

• 通过 show status like 'table%'命令

  • Table_locks_immediate：产生表级锁定的次数，每立即获取一次，锁值加 1
  • Table_locks_waited：不能立即获取锁的次数，每期待一次锁值加 1。此值高阐明存在重大的表级锁 争用 状况。
• 此外，MyISAM 是写锁优先调度，这也是不适宜做主表的引擎。因为写锁后，其余线程不能做任何操作，大量的更新会使查问很难失去锁，从而造成永远阻塞。

行锁（偏写）

偏差 InnoDB 存储引擎。开销大，加锁慢；会呈现死锁；锁定粒度最小，产生锁抵触的概率最低，并发度也最高。

InnoDB 与 MyISAM 的最大两个不同点：①反对事务，②采纳了行级锁

• 操作同一行数据

批改表中的同一行数据，将导致 session2 产生 阻塞，一旦 session1 提交事务，session2 将执行更新操作。

• 操作不同行数据

因为采纳行锁，session2 和 session1 互不干涉，所以 session2 中的批改操作没有阻塞

• 无索引导致行锁降级为表锁

  • session1

    set autocommit=0; #敞开主动提交事务
    
    #varchar 数据类型不必‘’，导致系统主动转换类型，导致索引生效
    update test_innodb_lock set a=44 where b=4000; 

  • session2

    set autocommit=0;
    
    update test_innodb_lock set b='9001' where a=9; 
    #执行发现产生了阻塞

  • session 中的 SQL 语句索引生效，导致行锁变为表锁。session2 须要期待 session1 提交事务开释锁。

为什么建表后要创立索引？因为不创立索引的话，更新操作的时候，行锁会因为没有索引而变为表锁。

想要锁定某一行时，在前面加上：for update，其余的操作会被阻塞，晓得锁定行的会话提交。

总结

1. InnoDB 实现了行级锁定，带来的性能损耗比表级锁定会更高一些，然而在整体 并发解决能力 方面要远远优于 MyISAM
2. InnoDB 的行级锁也有软弱的一面，当咱们使用不当的时候（索引生效变为表锁），可能会让 InnoDB 的整体性能比 MyISAM 的差。

事务

事务的个性

一致性的例子：转账来说，A 和 B 的钱一共 5000，不论 A 和 B 如何转账，事务完结后 A 和 B 的钱加起来还得是 5000。

• 数据库层面怎么实现事务？

  • InnoDB 曾经帮咱们开启了事务，并且每一条语句都会主动提交。如果要保障多条语句的事务性，就要手动开启事务。

InnoDB 存储引擎对 ACID 的实现形式

利用回滚日志（undo log）和 重做日志（redo log）两种表实现事务，并实现 MVCC (多版本并发管制)；

redo log（重做日志）

• 用于记录数据批改后的状态
• 每当有操作执行前，先将相干操作写入 redo log。这样当断电等意外产生时，零碎复原后，能够持续实现这些更改。

Binlog 日志的两个最重要的应用场景？

• MySQL 主从复制
• 数据恢复（通过 mysqlbinlog 工具）

redolog 和 binlog 的区别？

1. redolog 是 InnoDB 特有的，binlog 是 MySQL 的 Server 层实现的，所有引擎都能应用。
2. redolog 是 物理日志，binlog 是逻辑日志
3. redolog 是循环着写的，会笼罩，因为空间是固定的会用完。binlog 是追加写的，写到肯定大小后会切换到下一个。
4. redolog 在 事务执行过程中 一直写入，binlog 是在 事务最终提交前 写入的。

undo log（回滚日志）

• 用于记录数据批改前的状态
• 当一些更改执行一半发生意外时，而无奈实现，则能够依据 undo log 复原到之前的版本。（undolog 记录的是逻辑日志）
• 相同的记录。当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然。当 update 一条记录时，它记录一条对应相同的 update 记录。

例如某一时刻数据库宕机了，有两个事务，一个事务曾经提交，另一个事务正在解决。数据库重启后，就要依据日志进行前滚或回滚。把已提交事务的更改写到数据文件，未提交的事务更改复原到事务开始前的状态。

MySQL 中 ACID 底层实现

• 原子性：次要依附 undo.log 日志实现，即在事务失败时执行回滚。
• 持久性：次要依附 redo.log 日志实现。首先，MySQL 长久化通过 缓存 来提高效率。但因为缓存断电就没了，所以须要 redo.log 日志。在执行批改操作时，sql 会先写入到 redo.log 日志，再写入缓存中。这样即便断电，也能保证数据不失落，达到持久性
• 隔离性：数据库四种隔离级别，通过 MVCC 和锁 实现。
• 一致性：而下面三种个性就是为了保障数据库的有一致性（redolog+undolog）

事务并发带来的问题

• 失落更新：（更新操作 加上排它锁来防止）
• 某一个事务的 回滚 或提交，导致另一个事务已更新数据的失落
• 脏读：读取了其余并发事务 未提交 的数据。毁坏了事务的隔离性。
• 不可反复读：读取了其余并发事务提交的数据。针对 update 和 delete，也是毁坏了事务的隔离性。
• 幻读：读取了其余并发事务提交的数据。针对 insert

隔离级别：读未提交—> 读已提交RC—> 可反复读RR—> 可串行化

• MySQL5.5+ 的 InnoDB，在 RR 级别下能解决幻读。

• V1、V2、V3 的值在不同隔离级别下别离是多少？

  • 读未提交：20、20、20
  • 读已提交：18、20、20
  • 可反复读：18、18、20
  • 可串行化：18、18、20

事务隔离级别的实现形式

LBCC（Lock-Based Concurrent Control）基于锁的并发管制

InnoDB 锁：

• 共享锁又名读锁，共享锁是能够重入的，但无奈批改。
• 排它锁又名写锁
• 意向锁：意向锁是表锁，是创立共享锁或排它锁时主动创立的，无奈手动创立。作用是，用来通知你有没有曾经锁定的数据，能进步加表锁的效率。

InnoDB 锁的底层实现

这是一个只有主键 id 为 1、5、9、11 的表，锁的区间如下：

Gap 是开区间，Next-Key 左边是闭区间。

• Recrod Locks——记录锁（等值匹配，精准匹配）

select * from user where id=1 for update;

锁住本条 id = 1 的记录

• Gap Locks——间隙锁（范畴匹配）

select * from user where id>5 and id<9 for update;

锁住 (5,9)的区间，这时候就不能插入了。间隙锁只存在于 RR 隔离级别。

思考：select * from user where id>15 for update; 锁哪里？

范畴匹配，触发间隙锁，因为表里最大只有 11，索引间隙锁会把(11,+oo) 的区间锁住，插入 id=12 的数据也不行。

• Next-Key Locks——临键锁（范畴匹配，且命中某一条记录）

临键锁是 记录锁和间隙锁的联合。

select * from user where id>5 and id<11 for update;

范畴匹配，且命中表中的记录 9，触发临键锁。锁住了 (5,9] 和(9,11]，即(5,11]。所以插入 id=11 的数据也不行。

MVCC（Multi-Version Concurrent Control）多版本的并发管制。

• 一条记录在零碎中能够存在多个版本，是 InnoDB 实现事务并发的重要性能。

具体的实现时是，在数据库的每一行，增加额定的三个字段。

1. DB_TRX_ID：记录更新该行的最初一个事务 ID
2. DB_ROLL_PTR：指向该行对应的 undo log 的指针
3. DB_ROW_ID：枯燥递增的行 ID，就是 AUTO_INCREMENT 的主键 id

快照读与以后读

InnoDB 领有一个自增的全局事务 ID，每一个事务开启，都会记录以后事务的惟一 ID。同时，新事务创立时，事务零碎会将以后 未提交 的所有事务 id 组成的数组 提供 给这个新事务，这个数组咱们称为 TRX_ID 汇合

• 快照读

单纯的 select 操作。

每一个事务更新数据时，都会记录最初更新的事务 DB_TRX_ID。

如果这一行数据的 DB_TRX_ID 在TRX_ID汇合中 大于 以后事务的事务 ID，那么就阐明这行数据是在以后事务开启后提交的。否则阐明这行数据是在以后事务开启之前提交的。

如果遇到了以后事务开启后提交的数据，以后事务会通过 DB_ROLL_PTR 找到回滚日志，而后进行逻辑上的回滚拿到事务开启时的原数据。

这个通过 undolog+ 数据行 获取到事务开启时的原始数据的过程就是“快照读”。

具体解释：BD_TRX_ID 是他人的事务 ID，咱们的事务 ID 和他人的事务 ID 都是在未提交事务汇合里的，如果他人的事务 ID 大于我的事务 ID，阐明这个事务是比我后开启的，那么就阐明这行数据是我开启事务后，其余事务改的

• 以后读

很多时候，咱们读取数据库的时候，须要读取的是行的以后数据，不是事务开启时的原始数据。

以后读是通过锁实现的，通过 next-key 算法将这区域锁上了，其余事务无奈批改。

次要蕴含以下操作：

insert
update
select ... lock in share mode         #共享锁
select ... for update                #排它锁

Innodb 在 RR 级别如何防止幻读？

• 在快照读状况下（select），通过 MVCC 来防止幻读

  通过 undo log，找到原始数据。

• 在以后读状况下，通过 next-Key 来防止幻读

问题：InnoDB 能解决幻读，然而解决的并不完满。用 MVCC 实现快照读存在缺点，就是一旦某个事务的批改操作，笼罩到了其余事务插入的“幻行”，那么这些“幻行”在下次查问时就会再次出现，从而呈现幻象问题。