表锁（偏读）

偏差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的差。

事务

事务的个性

个性阐明
原子性（Atomicity）一个事务中的所有操作，要么全副胜利，要么全副失败
一致性（Consistency）事务开始之前和完结之后，数据库的完整性没有被毁坏
隔离性（Isolation）不同事务之间互不影响（有四种隔离级别）
持久性（Durability）事务一旦提交，其后果就会长久化，就算产生宕机也能复原数据
一致性的例子：转账来说，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算法将这区域锁上了，其余事务无奈批改。

次要蕴含以下操作：

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

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

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

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

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

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