MySQL事务与锁

事务和锁是数据库处理并发操作的重要手段，而事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

事务

事务的四大特征：

  • 原子性（A）: 所有操作要么全部完成要么全部失败，并不存在完成一般的情况。
  • 一致性（C）: 一致性是指数据库的完整性约束没有被破坏，事务执行前后都是合法的状态。同时也可以理解为实际场景上的约束，例如：一个账户减少的金额和另一个账户上增加的金额是一致的。
  • 隔离性（I）: 事务之间彼此之间相互隔离、互不干扰。
  • 持久性（D）: 事务只要提交成功，那么对数据库的修改就会永久生效。

并发事务

  • 脏读： 一个事务在处理过程中，读取到了其他事务修改后但尚未提交的数据。

  • 丢失修改： 事务A读取到某个数据x，并对x进行了修改。而另一个事务B也读取了数据x，也对x进行了修改。那么事务A对x的修改发生了丢失。

  • 不可重复读： 指在一个事务内多次读取某个数据，发现读取的数据出现了不一致的现象。

  • 幻读： 在一个事务内读取数据，读取到原本不存在的数据，即被其他事务插入的数据。

  • 写偏斜： 可以理解为事务commit之前写前提被破坏，导致写入了违反业务一致性的数据。写倾斜发生在数据的写前提与写目标不相同的情况。例如：两个事务A和B，A将寄存器x的值复制到y，B将寄存器y的值复制到x。只有两个事务，串行执行A、B或者B、A都是x = y,但在快照隔离级别下出现以下结果：

    点击查看 
    1. 事务A读取x
2. 事务B读取y
3. 事务A将x写入y
4. 事务B将y写入到x
    即出现了业务上的不一致现象。

不可重复读注重在数据的修改，而幻读注重在数据的插入和删除。

隔离级别

隔离级别 脏读 不可重复读 幻读
读未提交 × × ×
读已提交 √ × ×
可重复读 √ √ ×
串行化 √ √ √

注： 事务的隔离级别越高并发度就越低。对于InnoDB而言，其可重复读级别就解决了幻读问题，因此MySQL默认的隔离级别就是可重复读。

锁

  • 按细粒度分：
    • 表锁：意向锁、自增锁
    • 行锁：间隙锁、临键锁、记录锁
  • 按锁的方式分：
    • 排他锁：写锁（X），在事务需要修改一条记录时，需要先获取该记录的写锁。写锁在一个时刻最多只能被一个事务持有。
    • 共享锁：读锁（S），在事务要读取一段数据时，需要先获取该记录的读锁。读锁可以同时被多个事务持有。

注： 当一个事务已经持有某行记录的写锁（x），则另一个事务无法对这行记录添加读锁（S），反之亦然。

意向锁

加意向锁的目的时为了表明某个事务将要锁定某行数据。意向锁包含以下两种：

  1. 意向共享锁（IS）：将要对数据加共享锁
  2. 意向排他锁（IX）：将要对数据加排他锁

当某个事务A对某行加X锁后，另一个事务B想对整张表进行修改，就不再需要对每行加锁，而是直接申请标级别的X锁。但是A在申请行级别的X锁之前一定已经拿到了IX锁，因此事务A便知道有其他事务在对表里的内容进行修改，从而被阻塞。

注： IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突，而意向锁之间并不出发生冲突。

自增锁

自增锁（auto-inc locks）是一种特殊的表级别锁。（设置为auto-increment的列必须是索引或是索引的一部分）

5.1.22 之前， InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过 表锁机制来完成的（AUTO-INC LOCKING），锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞

在5.1.22之后， InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode，该实现方式是通过 轻量级互斥量的增长机制 完成的。它是专门用来在使用auto_increment的情况下调整锁策略
innodb_autoinc_lock_mode：可以设定3个值，0，1，2

insert前提知识 
INSERT-LIKE：   指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
Simple inserts：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
Bulk inserts：  指在插入前不能确定得到插入行的语句。如INSERT…SELECT, REPLACE…SELECT, LOAD DATA.
Mixed-mode inserts:指其中一部分是自增长的，有一部分是确定的。insert t1(id,xx) values(1,11)(null,22)

0. 传统模式（traditonal） 通过表锁的方式进行,所有类型的insert都用AUTO-inc locking。可以发现当并发量增加时，MySQL的性能就会下降，因为同时只能执行一条语句。

image

1：连续模式（consecutive） 默认值，产生一个轻量锁，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。实际上是为能够提前指导插入数据行数的语句分配好自增id，然后把相应的空间预留出来，就可以执行下一句了。

2：交叉模式（interleaved） 对所有的insert-like 自增长值的产生使用互斥量机制完成，并发性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。
image

存在的缺陷：

  1. 当使用连续模式或交叉模式并且bin log采用了statement格式进行存储，如果发生了bulk insert会出现主从不一致的情况（改成row格式则不会出现相应的现象）
  2. id不连续，当使用了连续模式，对bulk insert由于使用了预留i。
    例如：有两张结构相同的表 test1 和 test2。其中 test1 包含了5条数据，test2 是空的。
    image
    将 test1 的数据插入到 test2中
    image
    image
    当我们再次插入一条数据时，应该id自增成6，但实际上id为8。
    image
    第一次插入数据时，申请了1个id，第二次插入数据时申请了2个id，第三次插入数据时申请了4个id（id的申请是上次的2倍）。但对于第四次预留的id并没有使用完，因此当再次插入数据时，需要重新申请一个id，当前最大的id是7因此插入后的数据id为8。

注： 因此可以将innodb_autoinc_lock_mode设置为2，并将bin log设置成row记录格式。

InnoDB行级锁

InnoDB的行锁实际上是加在索引上，如果加锁查询时没有使用过索引则会对整个聚簇索引进行加锁，即相当于对整个表加锁。根据加锁的范围不同，主要包含以下锁：

  • 记录锁（Record locks） ：锁住某行记录
  • 间隙锁（Gap locks） ：指两个记录中逻辑上未填入数据的部分，例如（1，4）（4，7）等。间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 4 for update;就会将(1,4)区间锁定。
  • 临键锁（Next-key locks） ：临建指的是间隙加上右边的记录组成的左开右闭区间，例如（-∞，1]、（1，4]、（4，7]、（7，+∞）。临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。

注： RC及RC以下并不会使用间隙锁。

如何判断加的是表锁还是行锁

大部分博客的说法：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
实际上，上面的说法是不准确的，包含两个错误：1. 并不是使用表锁、而是间隙锁锁住了整张表。2.判断加表锁和行锁首先要确定隔离级别。对于RU和RC隔离度下，不存在间隙锁，对于这两种隔离级别，无论条件列是否有索引，都不会锁表，只锁行！

例如：当前隔离级别为RR，开启两个事务A和B，并且两个事务都进行如下操作。
image

不走任何索引的情况下，整张表被锁住了，但是实际上是对每张表的每行记录加了X锁。
image

走索引的情况如下，实际上只有一条记录被加了X锁。
image

MVCC

多版本并发控制（MVCC），一般在RC或RR两个隔离级别下。MySQL的MVCC是基于InnoDB的undo版本链和ReadView一致性视图实现的。

undo版本链

对于一条记录实际上包含隐藏列db_row_id、trx_id和db_roll_ptr等。

  • db_row_id ：行id,B+树索引要求每个表都有主键，如果创建表时没有定义主键会自动寻找一个非空的唯一键作为row_id，如果还是找不到则InnoDB系统会为每条记录生成一个6字节的row_id。row_id是存在的，区别在于是否能够通过select语句查出来，具体的如果有主键且是数值型是能够显示的，没有主键但有非空唯一键且也是数值型也能够显示。而系统自动分配的row_id是不能显示的，它从0开始递增，当用完时会自动覆盖。row_id并不是真正存在的列，只是非空唯一键的别名。
  • db_trx_id ：事务id，修改该行数据的事务id。
  • db_roll_ptr : 回滚指针，将不同的版本串联起来。
    image
ReadView

MySQL的一致性读是通过ReadView来实现的，ReadView是MySQL底层实现的一个结构体，是和SQL语句绑定的，在每个SQL语句执行前申请或获取。可以将其理解为构造快照的前提或者依据，一个快照所呈现的数据是什么样子(版本)的基本依赖于ReadView中所存储的数据。
主要包含以下变量：

  • low_limit_id : 当前最大的事务号 + 1，事务号 >= low_limit_id，对于当前Read View都是不可见的。理解起来就是在创建Read View视图之后创建的事务对于该事务肯定是不可见的。
  • up_limit_id : 当前已经提交的事务号 + 1，事务号 < up_limit_id ，对于当前Read View都是可见的。理解起来就是创建Read View视图的时候，之前已经提交的事务对于该事务肯定是可见的
  • trx_id : 为活跃事务id列表，即Read View初始化时当前未提交的事务列表。所以当进行RR读的时候，trx_ids中的事务对于本事务是不可见的（除了自身事务，自身事务对于表的修改对于自己当然是可见的）。理解起来就是创建RV时，将当前活跃事务ID记录下来，后续即使他们提交对于本事务也是不可见的。
  • creator_trx_id : 创建当前视图的事务id

ReadView可见性判断

  • trx_id < up_limit_id || creator_trx_id == trx_id
    可见，事务的id小于最小已提交事务的id，即事务在当前事务创建前就已经存在了。或者事务的id等于当前事务自己的id，对于自己修改的数据，肯定可见。
  • trx_id > = low_limit_id
    不可见，事务id大于ReadView记录的最大的活跃事务id，说明该事务在当前事务开启后才创建，因此对于该事务的数据是不可见的。
  • trx_id是否在trx_id活跃列表里
    • 不存在：说明该事务在当前ReadView产生后就已经提交了，这种情况下数据可以显示。
    • 已存在：表示当前ReadView生成后，该事务仍然处于活跃状态，因此是不可见的。
      image

MVCC与事务隔离级别

  • 生成时机
    • RC：RC隔离级别下，每个快照读都会生成一个ReadView。
    • RR：只有同一个事务的第一个快照读才会创建ReadView，之后的快照读都是同一个ReadView。
  • 幻读问题
    • 快照读：通过MVCC来进行控制，不需要加锁（乐观并发机制）
    • 当前读：通过Next-key lock来解决问题

注： RC下，每一次快照读都会生成一个ReadView，因此在一个事务内能够看到其他事务提交后的修改数据。而在RR下，每个事务都只会使用当前事务内部第一次快照读生成的Read View。

MVCC与索引相关

  1. 无论是聚簇索引还是二级索引，只要键值发生了更新，就会产生新的版本，将老版本的delete位置为1，同时插入新版本。
  2. 对于聚簇索引，如果操作没有修改primary key，则直接在原有的基础上进行修改，老版本进入undo表空间，通过roll_ptr进行回滚。
  3. 对于二级索引，如果更新操作没有更新其键值，那么二级记录保持不变。（InnoDB的数据实际上都在聚簇索引中）
  4. 对于二级索引，更新操作无论更新primary key，或者是二级索引键值，都会导致二级索引产生新版本数据。
  5. 聚簇索引设置记录deleted位时，会同时更新TRX_ID列。老版本TRX_ID进入undo表空间；二级索引设置deleted位时，不写入undo。

注： 与主键索引不同，二级索引并不存储版本信息，那么二级索引如何进行可见性判断呢？实际上二级索引的多版本可见性需要通过聚簇索引来判断，二级索引存储了max_trx_id，用于快速判断当前页面所有项是否都可见，可以实现页面级别的索引覆盖。二级索引中的项需要与聚簇索引里的项相比较，保证可见性的一致。

MVCC出现幻读的特例
对于快照读，通过MVCC机制，MySQL可以避免幻读。对于当前读，MySQL通过MVCC + next-key lock能够避免幻读问题。
例如：左边事务A、右边事务B
image
事务B插入一条数据，由于MVCC机制事务A的ReadView是第一次快照读时生成的，因此并不会看到这条数据。
image
当事务A尝试更新事务B插入的这条数据时，发现该数据被锁住了，因此一直处于等待状态
image
现在将事务B提交，在更新该数据，发现事务A更新了成功并且查找出了不存在的数据，出现了幻读现象。
image

解决方法：1. 使用串行化隔离级别 2. 使用间隙锁，即select … for update;

常见当前读的操作 
```
除了第一个是共享锁，其他操作都是排他锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update;
delete;
```

Purge

InnoDB由于要支持多版本协议，因此无论是更新，删除，都只是设置记录上的deleted bit标记位，而不是真正的删除记录。后续这些记录的真正删除，是通过Purge后台进程实现的。

  1. purge是通过遍历undo实现的。
  2. purge的粒度是一条记录上的一个操作。如果一条记录被update了3次，产生3个old版本，均可purge。那么purge读取undo，对于每一个操作，都会调用一次purge。一个purge删除一个操作产生的old版本(按照操作从老到新的顺序)。
  3. purge按照先二级索引，最后聚簇索引的顺序进行。
  4. purge二级索引，通过构造出的索引项进行查找定位。不能直接针对某个二级页面进行，因为不知道记录的存放page。
  5. 对于二级索引设置deleted bit为不需要记录undo，因为purge是根据聚簇索引undo实现。因此二级索引deleted bit被设置为1的项，没有记录undo，仍旧可以被purge。
  6. purge是一个耗时的操作。二级索引的purge，需要search_path定位数据，相当于每个二级索引，都做了一次index unique scan。
  7. 一次delete操作，IO翻番。第一次IO是将记录的deleted bit设置为1；第二次的IO是将记录删除。

参考链接

posted @ 2022-10-02 14:39  厚礼蟹！  阅读(65)  评论(0)    收藏  举报