Mysql Locks

###transaction locks

维护在不同的Isolation level下数据库的Atomicity和Consistency两大基本特性。

  • table locks

    对整个表加锁，影响所有记录。通常用在DDL语句中，如DELETE TABLE,ALTER TABLE等。

  • row locks

    对一行记录加锁，只影响一条记录。通常用在DML语句中，如INSERT, UPDATE, DELETE等。

InnoDB定义了如下的lock mode:

1
2
3
4
5
6
7
8
/* Basic lock modes */
enum lock_mode {
LOCK_IS = 0,     /* intention shared */
LOCK_IX,     /* intention exclusive */
LOCK_S,          /* shared */
LOCK_X,          /* exclusive */
LOCK_AUTO_INC,     /* locks the auto-inc counter of a table
......

  • shared lock （S) 容许获得锁的事务去 读一行

  • exclusive lock （X) 容许获得锁的事务去更新或者删除一行

  • Intention shared （IS） 获取IS表示事务希望获取S锁（或更高）。在获取这一行的S锁之前，需要先获取表的IS

  • Intention exclusive （IX） 获取IX表示事务希望获取X锁。在获取这一行的X锁之前，需要先获取表的IX。

####lock type compatibility：

image-20180912002757181

####解释：

S，X锁，就像是读写锁，S是读锁，X是写锁。（注：S锁只有IN SHARE MODE才会加。一般的select是consistent read）。在给一行记录加锁前，首先要给该表加意向锁。

####意向锁的作用：

1
The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

为方便检测table lock 和 row lock之间的冲突，引入了意向锁。

当再向一个表添加表级X锁的时候

  • 如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突
  • 如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果

###内存锁

为了维护内存结构的一致性，比如Dictionary cache、sync array、trx system等结构。 InnoDB并没有直接使用glibc提供的库，而是自己封装了两类：

  1. 一类是mutex，实现内存结构的串行化访问
  2. 一类是rw lock，实现读写阻塞，读读并发的访问的读写锁

###Row Level Lock 细分

####Record Locks

在index records上的锁。比方说：

1
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

这个就为c1 = 10的这个索引加了锁，防止其他事物对所有c1=10的行做inserting, updating,deleting操作。

Gap Locks

是一种在index records之间的一种锁。

1
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

阻止其他事物向c1 的10～20区间内，插入数据。（只有插入）

在gap lock锁定的范围内，可以有0～n个index records。

gap lock 是在性能和一致性上的一个折衷，只在某些事物隔离级别下生效。

特点：

1 使用unique index来查单条的语句不会用到gap lock

2 gap lock之间不会有冲突，X-gap lock 和S-gap lock没什么区别。

3 gap lock只是为了防止向一个区间内插入

Next-Key Locks

是record lock 和gap lock的组合：当前index record 的 record lock + 当前index record之前的区域的 gap lock。

举例：index record为10，11，13，20，则next-key lock可以有以下几种可能：

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

作用：

1
By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows

InnoDB使用next-key locks 来防止幻读。

幻读，就是在一个事物内，两次select，第二次得到了一个新的row。

所以在锁住当前记录的同事，要把他到前一个记录的‘gap’也锁住。才能保证。不会出现新的纪录。

Insert Intention Locks

//TODO

AUTO-INC Locks

是一个table level的锁，当传入有AUTO_INCREMENT列的字段的时候。如果一个事物在插入几条记录，其他事物也想插入时，必须等待。否则第一个事物就无法获取到连续的自增值。当然，自增序列的顺序的可预见性和插入的并发性能，也是一个权衡的点，可以通过innodb_autoinc_lock_mode来设置。

具体参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

###

###Transaction Isolation Levels

事物隔离级别，是ACID中的I。

前置知识：

Consistent Reads： 无锁的select，（plain select）,MVCC

1
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time.

Locking Reads：SELECT with FOR UPDATE or FOR SHARE, UPDATE, and DELETE statements

Repeatalbe Read

innoDB默认隔离级别。

  • Consistent Reads，即无锁的select，读的都是事物中第一个select得到的snapshot。
  • Locking Reads
    • 使用了唯一索引：只有record lock
    • 没使用惟一索引：使用next-key lock 锁住区间。

Read Commited

  • Consistent Reads：每一个select（即使在同一个事物内）都是一个新的snapshot。

  • Locking Reads：只上Record lock，无gap lock或next-lock

所以，幻读出现。

####READ UNCOMMITTED

SERIALIZABLE

Locks Set by Different SQL Statements in InnoDB

  • Select ... from...是consistent read,读一个snapshot。除非是SERIALIZABLE隔离级别，这个时候，设置shared next-key lock。
  • select ... for update 和 select ... for share会加X next-key lock。如果是唯一索引，会加record lock。
  • Update...where..会加 X next-key lock。如果是唯一索引，会加record lock。
  • delete from ... where加X next-key lock。如果是唯一索引，会加record lock。
  • insert会给当前插入的行加record lock。

参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Consistent Reads

consistent reads的含义是InnodB使用multi-versioning去给查询提供一个数据库的snapshot。查询只能看到这个点之前提交的事物的改动，而对之后的一无所知。

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTEDand REPEATABLE READ isolation levels.

A consistent read does not set any locks on the tables it accesses,

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:

1
SELECT * FROM t FOR SHARE;

参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

Undo

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用UndoLog来实现多版本并发控制(简称：MVCC)。 -事务的原子性(Atomicity) 事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。

参考：https://www.cnblogs.com/kongzhongqijing/articles/7905051.html

Redo

记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，
但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

InnoDB有buffer pool（简称bp）。bp是数据库页面的缓存，对InnoDB的任何修改操作都会首先在bp的page上进行，然后这样的页面将被标记为dirty并被放到专门的flush list上，后续将由master thread或专门的刷脏线程阶段性的将这些页面写入磁盘（disk or ssd）。这样的好处是避免每次写操作都操作磁盘导致大量的随机IO，阶段性的刷脏可以将多次对页面的修改merge成一次IO操作，同时异步写入也降低了访问的时延。然而，如果在dirty page还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是redo log file。这样的技术推迟了bp页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写redo log操作的开销（顺序IO，当然很快），以及数据库启动时恢复操作所需的时间。

redo log包括两部分：

  • 一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；
  • 二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。

也就是说，从redo log buffer写日志到磁盘的redo log file中，过程如下：

733013-20180508101949424-938931340

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

  • 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
  • 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
  • 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

733013-20180508104623183-690986409

参考：

https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html

https://www.cnblogs.com/kongzhongqijing/articles/7905051.html

MVCC

上述更新前建立undo log，根据各种策略读取时非阻塞就是MVCC，undo log中的行就是MVCC中的多版本，这个可能与我们所理解的MVCC有较大的出入，一般我们认为MVCC有下面几个特点：

  • 每行数据都存在一个版本，每次数据更新时都更新该版本
  • 修改时Copy出当前版本随意修改，各个事务之间无干扰
  • 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道，而Innodb的实现方式是：

  • 事务以排他锁的形式修改原始数据
  • 把修改前的数据存放于undo log，通过回滚指针与主数据关联
  • 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

二者最本质的区别是，当修改数据时是否要排他锁定，如果锁定了还算不算是MVCC？

Innodb的实现真算不上MVCC，因为并没有实现核心的多版本共存，undo log中的内容只是串行化的结果，记录了多个事务的过程，不属于多版本共存。但理想的MVCC是难以实现的，当事务仅修改一行记录使用理想的MVCC模式是没有问题的，可以通过比较版本号进行回滚；但当事务影响到多行数据时，理想的MVCC据无能为力了。

比如，如果Transaciton1执行理想的MVCC，修改Row1成功，而修改Row2失败，此时需要回滚Row1，但因为Row1没有被锁定，其数据可能又被Transaction2所修改，如果此时回滚Row1的内容，则会破坏Transaction2的修改结果，导致Transaction2违反ACID。

理想MVCC难以实现的根本原因在于企图通过乐观锁代替二段提交。修改两行数据，但为了保证其一致性，与修改两个分布式系统中的数据并无区别，而二提交是目前这种场景保证一致性的唯一手段。二段提交的本质是锁定，乐观锁的本质是消除锁定，二者矛盾，故理想的MVCC难以真正在实际中被应用，Innodb只是借了MVCC这个名字，提供了读的非阻塞而已。