Mysql-innoDB存储引擎(事务，锁，MVCC)

川长思鸟来 2023-10-05 18:12 128阅读 0赞

innoDB的特性：

f69b40c8b9968a0dcd609f44556ddab1.png

　　从图中由上至下红色框中的信息是：基于主键的聚集索引 ，数据缓存，外键支持（逻辑上建立外键），行级别锁，MVCC多版本控制，事务支持。这些也是InnoDB最重要的特性。

事务:

　　数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合（工作逻辑单元）。典型事务场景(转账)：这是两个事务

　　update user_account set balance = balance - 1000 where userID = 3;

　　update user_account set balance = balance +1000 where userID = 1;

mysql中如何开启事务：

　　通过navicat使用命令 showvariables like ‘autocommit’； 查看自动提交是否开启。当开启后执行update语句会自动提交，当自动提交是关闭的，可以通过以下方式来创建事务提交：

复制代码

  1. BEGIN;-- 这两个二选一开启事务
  2. START TRANSACTION;
  3. -- 这是一个事务
  4. UPDATE ......
  5. UPDATE ......
  7. COMMIT;-- 提交或者回滚
  8. ROLLBACK;

复制代码

　　begin / start transaction — 手工开启事务。

　　commit / rollback — 事务提交或回滚。

　　set session autocommit = on/off; — 从Session的角度设定事务是否自动开启。

JDBC 编程：

　　connection.setAutoCommit（boolean）;

Spring 事务AOP编程：

　　expression=execution（com.gpedu.dao.*.*(..)）

事务ACID特性：

  • 原子性（Atomicity）：最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
  • 一致性（Consistency）：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致
  • 隔离性（Isolation）：数据并发的时候，一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
  • 持久性（Durability）：事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失

事务并发带来什么问题：

　　先来看第一张图：在下图中，一张表中记录只有一条，事务B修改该条记录的 age字段，而此刻 事务A来查询了，获得的age是18，接着事务B 回滚了，这样子就出现了脏读问题。

d9de21de6be513ef71e801e9719c7d2c.png

　　再来看第二个图：事务A先查询了数据信息，此刻事务B进行了修改并提交，然后事务A又去查询了一遍，这个时候就会出现不可重复读的问题。

0781fbb87892b95f503909382a13484d.png

　　第三张图：通过范围查询获得一条数据，此刻事务B 插入了一条数据，事务A又去查询获得了两条数据，此刻就发生了幻读。

ffba1619cba92cb626a228d8ab123549.png

　　综上，事务并发给我们带来了三个主要问题：脏读，不可重复读，幻读。

事务的隔离级别：

  • Read Uncommitted（未提交读） —未解决并发问题，事务未提交对其他事务也是可见的，脏读（dirty read）。
  • Read Committed（提交读） —解决脏读问题，一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatableread）。
  • Repeatable Read (可重复读) —解决不可重复读问题在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题。
  • Serializable（串行化） —解决所有问题，最高的隔离级别，通过强制事务的串行执行。

设置read uncommitted级别：set session transaction isolation level read uncommitted;

innoDB对隔离级别的支持程度：

1d1d15c2eb1ffd5853d632a597454740.png

　　在InnoDB中隔离级别到底如何实现的呢？ —通过锁、MVCC。

InnoDB中的锁：

　　锁是用于管理不同事务对共享资源的并发访问，InnoDB存储引擎支持行锁和表锁（另类的行锁，通过行锁锁住所有的行）。官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html。表锁与行锁的区别：

  • 锁定粒度：表锁 > 行锁
  • 加锁效率：表锁 > 行锁
  • 冲突概率：表锁 > 行锁
  • 并发性能：表锁 < 行锁

MYSQL innoDB锁类型：

  • l 共享锁（行锁）：Shared Locks
  • l 排它锁（行锁）：Exclusive Locks
  • l 意向共享锁（表锁）：Intention Shared Locks
  • l 意向排它锁（表锁）：Intention Exclusive Locks
  • l 自增锁：AUTO-INC Locks

行锁的算法：

  • l 记录锁 Record Locks
  • l 间隙锁 Gap Locks
  • l 临键锁 Next-key Locks

共享锁:

　　又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改，加锁释锁方式：

复制代码

  1. -- 共享锁加锁
  2. BEGIN
  3. select * from users WHERE id=1 LOCK IN SHARE MODE;
  4. rollback; 
  5. commit; 
  6. -- 在以上的SQL枷锁后未执行提交或者回滚执行其他事务执行
  7. select * from users where id =1; -- 可以执行，共享锁特性
  8. update users set age=19 where id =1;--会阻塞

复制代码

排他锁:

　　又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改，（其他事务要读取数据可来自于快照），加锁释锁方式：delete / update / insert 默认加上X锁。

复制代码

  1. -- 自动获取排它锁
  2. set session autocommit = OFF; -- 设置手动提交事务
  3. update users set age = 23 where id =1; --执行该语句后未提交，在其他线程上，执行下列其他事务执行语句会处于阻塞commit;
  4. ROLLBACK;
  6. -- 手动获取排它锁
  7. set session autocommit = ON;
  8. begin
  9. select * from users where id =1 for update;
  10. commit;
  12. -- 其他事务执行
  13. select * from users where id =1 lock in share mode;
  14. select * from users where id =1 for update;
  15. select * from users where id =1;

复制代码

innoDB—行锁到底锁了什么？

　　首先先来看一下测试表的结构，其中用的是InnoDB引擎，有一个name的唯一索引，主键自增，有3条数据

复制代码

  1. DROP TABLE IF EXISTS `users`;
  2. CREATE TABLE `users` (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `uname` varchar(32) NOT NULL,
  5.   `userLevel` int(11) NOT NULL,
  6.   `age` int(11) NOT NULL,
  7.   `phoneNum` char(11) NOT NULL,
  8.   `createTime` datetime NOT NULL,
  9.   `lastUpdate` datetime NOT NULL,
  10.   PRIMARY KEY (`id`),
  11.   KEY `idx_name` (`uname`) USING BTREE
  12. ) ENGINE=InnoDB AUTO_INCREMENT=100006 DEFAULT CHARSET=utf8mb4;
  14. -- ----------------------------
  15. -- Records of users
  16. -- ----------------------------
  17. INSERT INTO `users` VALUES ('1', '李二狗', '2', '18', '13666666666', '2018-12-01 15:39:46', '2018-12-01 15:39:50');
  18. INSERT INTO `users` VALUES ('2', '张三丰', '1', '29', '13777777777', '2018-12-01 16:35:41', '2018-12-01 16:35:44');
  19. INSERT INTO `users` VALUES ('3', '武大郎', '2', '44', '13888888888', '2018-12-01 16:36:01', '2018-12-01 16:36:03');

复制代码

案例1：紧接着在一个事务中执行以下语句：可以发现我们把事务设置成手动提交，但是我并未提交或者回滚：

  1. set session autocommit = OFF;
  2. update users set lastUpdate=NOW() where phoneNum = '13666666666';

然后在其他事务中执行如下语句：会发现，上述SQL执行修改会获得默认的排它锁，而此刻并未释放，锁的列是ID为1，然后我们下列要修改ID为2的数据也是出于阻塞，这是为什么呢？

  1. update users set lastUpdate=NOW() where id =2;
  2. update users set lastUpdate=NOW() where id =1;

案例2，执行以下语句，可以发现我们把事务设置成手动提交，但是我并未提交或者回滚：

  1. set session autocommit = OFF;
  2. update users set lastUpdate=NOW() where id = 1;

然后在其他事务上执行：会发现下面2条SQL执行后 第一条会顺利执行，而第二条会被阻塞。

  1. update users set lastUpdate=NOW() where id =2;
  2. update users set lastUpdate=NOW() where id =1;

案例三：执行一下语句：

  1. set session autocommit = OFF;
  2. update users set lastUpdate=NOW() where `name` = '李二狗';

然后在其他事务上执行：会发现前面两条会执行失败，而后面两条执行成功

  1. -- 其他查询执行
  2. update users set lastUpdate=NOW() where `name` = '李二狗';
  3. update users set lastUpdate=NOW() where id =1;
  4. update users set lastUpdate=NOW() where `name` = '张三丰';
  5. update users set lastUpdate=NOW() where id =2;

　　InnoDB的行锁是通过给索引上的索引项加锁来实现的。对于二级索引，会对一级索引也加锁。只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB将使用表锁（锁住索引的所有记录）表锁：lock tables xx read/write；

意向共享锁(IS)：表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁，意向共享锁之间是可以相互兼容的。

意向排它锁(IX)：表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁，意向排它锁之间是可以相互兼容的。

　　意向锁(IS、IX)是InnoDB数据操作之前自动加的，不需要用户干预。

　　意义：相当于一个标记flgs，当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁。

自增锁 AUTO-INC Locks：

　　针对自增列自增长的一个特殊的表级别锁，查看自增锁默认值：show variables like ‘innodb_autoinc_lock_mode’;默认取值1，代表连续，事务未提交ID永久丢失。当级别为1，执行一下SQL：在插入数据的时候，这个表的ID为自增，连续回滚3次，这3次的ID会永久消失，在下次执行commit的时候ID会在原来的数值上加3.

  1. begin;
  2. insert into users(name , age ,phoneNum ,lastUpdate ) values ('tom2',30,'1344444444',now());
  3. ROLLBACK;

针对行锁的算法：

临键锁 Next-key Locks：

　　Next-key locks：InnoDB行锁的默认算法。锁住记录+区间（左开右闭），当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭）。先来搞一张表：

复制代码

  1. DROP TABLE IF EXISTS `test`;
  2. CREATE TABLE `test` (
  3.   `id` int(11) NOT NULL ,
  4.   `name` varchar(32) NOT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
  8. -- ----------------------------
  9. -- Records of users
  10. -- ----------------------------
  11. INSERT INTO `test` VALUES ('1', '1');
  12. INSERT INTO `test` VALUES ('4', '4');
  13. INSERT INTO `test` VALUES ('7', '7');
  14. INSERT INTO `test` VALUES ('10', '10');

复制代码

　　在InnoDB的默认行级算法中会对数据行进划分：可以看到是一个左开右闭的这个一个展现。

e92420311517ea9cc45b2a077a90d7a5.png

　　执行以下sql不提交：由于有数据命中则会锁住(4,7](7,10] 两个区间。未提交的情况下执行下列其他事务中前四条全部阻塞而最后一条会成功执行。

复制代码

  1. begin;
  2. select * from test where id>5 and id<9 for update;
  3. -- 其他事务
  4. select * from test where id=4 for update; -- 阻塞
  5. select * from test where id=7 for update; -- 阻塞
  6. select * from test where id=10 for update; -- 阻塞
  7. INSERT INTO `test` (`id`, `name`) VALUES (9, '9'); -- 阻塞
  8. INSERT INTO `test` (`id`, `name`) VALUES (11, '11');-- 成功

复制代码

　　为什么InnoDB要选择（临键锁）Next-key locks作为InnoDB行锁的默认算法？解决幻读，因为B+Tree是有顺序的，从左往右顺序递增，把临键区间也锁住，其他事务要往里插入数据是插不进去的。

间隙锁 Gap Locks：继临键锁要是没有命中数据的情况下：

698d2df5d51c6e9336045ce2b6e80206.png

　　Gap锁只在 Repeatable Read (可重复读) 的隔离级别的情况下才存在。

记录锁 Record Locks:继临键锁之后，在条件为精准匹配的时候。

0375f67a28d0f44c4d337fc0f71dc2f0.png

那么锁是怎么解决上述产生 脏读，不可重复读，以及幻读的情况呢？

解决脏读：

0f1390c4dcd6186c0999e183a754c17c.png

解决不可重复读：

4b3147d4feda33ca366b3a9f589d048b.png

解决幻读：

a00948b53f04d584423cfc3d9834a88f.png

死锁：

  • 多个并发事务（2个或者以上）；
  • 每个事务都持有锁（或者是已经在等待锁）;
  • 每个事务都需要再继续持有锁；
  • 事务之间产生加锁的循环等待，形成死锁

避免死锁：

  • 类似的业务逻辑以固定的顺序访问表和行。
  • 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
  • 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
  • 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

Mysql 中MVCC版本控制：

　　MVCC是multiversion concurrency control的缩写，并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题 。提供MySQL事物隔离级别下无锁读，例如一个事物在执行update等修改数据的sql，并未提交时其他事物进行数据读取是不影响的，而且读取内容为数据变更之前的数据。

　　MVCC多本版快照由innodb的rollback segment构照的，一个sql进行查找数据当查找到某一个数据需要到回滚段中查找数据时，就会根据当前页上行数据的一个指针到回滚段中查找对应数据，在innodb的表主键中都会存在三个隐藏的字段：

  • DB_TRX_ID：该字段存储最后一个修改该行数据的事务ID，占用6byte的空间，MySQL的delete操作是标记删除，所以对应行数据的该字段就为一个删除标记。
  • DB_ROLL_PTR：该字段就记录执行roll segment的指针信息，当事务需要rollback时就通过该字段寻找记录重新构照行数据，该字段占用7byte空间。
  • DB_ROW_ID：记录每个行ID，该ID值为单调递增型整数，在innodb表指定了主键之后DB_ROW_ID存在于主键索引上，如果无主键该值就不会存在，占用6byte空间。

　　在一个sql进行查询时，读取到一行数据的DB_TRX_ID值和自己事物ID的对比，假如隔离级别为MySQL的默认级别，就只读取该ID值小于本身事物ID的数据，其余数据就需要通过DB_ROLL_PTR的信息到回滚段中读取。MVCC是否起到相应的作用需取决于数据库隔离级别的配置。

　　在insert和update、delete的操作是有区别的，一个insert语句插入数据再rollback就是直接对undo log的删除，他并不会影响其他事物的读取操作，而update、delete操作是在原有数据做更改，可能有其他事物在对该行数据做读取操作，所以update、delete产生的undo log数据是由内部线程自动清理，在该数据无任何事务在使用时清理掉，所以在undo log中insert和update、delete产生的数据存于不同位置。

　　下面通过一个案例来熟悉一下MVCC的效果：

复制代码

  1. -- 数据准备
  2. insert into teacher(name,age) value ('seven',18) ;--假设事务版本为1
  3. insert into teacher(name,age) value ('qing',20) ;--假设事务版本为1
  4. begin;                                 ----------1
  5. select * from users ;                  ----------2
  7. begin;                                 ----------3
  8. update teacher set age =28 where id =1;----------4

复制代码

　　在每一行数据 插入数据表的时候，都会开启一个事务，每一行数据都会保存执行的时候所获取的事务版本号，当进行修改的时候会先copy一份待修改的数据到 Undo 缓冲区，在提交后然写入磁盘，在此过程中会将原先的数据行的删除版本号置为当前事务ID，然后再在新的数据行把数据行版本号置为当前事务ID。

　　当我们按照 1，2，3，4，2 的顺序去执行的时候，首先执行 1 拿到的事务ID 是2，那么执行2查询出来就是原始数据，这个时候事务并没有提交或者回滚，然后执行3开启一个事务拿到的事务ID 为3 ，此刻执行4(在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。)进行 update 操作的时候会 copy 数据到Undo 缓冲区，然后将Undo.log的原始数据的删除版本号置为3，把新数据的事务版本号置为3，再执行2的时候由于此刻事务ID 还是为2，所以根据查询规则查找数据行版本号小于当前事务版本的数据行，查找删除版本号大于当前事务版本的或者删除版本为nul的数据行，由于修改操作未提交，所以最终得到的结果数据还是原始数据的值，并不会把修改的数据加载回来，解决了不可重复读的问题。

　　如果按照这样的逻辑通过 3，4，1，2的顺序去执行，那么首先修改的操作会拿到事务ID为2，将原来的数据行copy出来，将原来的删除版本号置为当前事务ID，接着将备份数据的版本号置为当前版本号，然后执行查询操作再开启一个新事务，拿到的事务ID为3，根据查询规则，拿到的是进行了update 操作但并未提交的新数据，造成了脏读，这是为什么呢？那么是由谁去解决这个问题的呢？其实这里面涉及到了 Undo.log的机制以及当前读，快照读的问题，那么接下来看看他们是怎么处理这个问题的 。

Undo Log：

　　Undo Log 是什么：undo意为取消，以撤销操作为目的，返回指定某个状态的操作，undo log指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地方 (Undo Log)，UndoLog是为了实现事务的原子性而出现的产物。

　　Undo Log实现事务原子性：事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句,Mysql可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

　　UndoLog在Mysql innodb存储引擎中用来实现多版本并发控制。

　　Undo log实现多版本并发控制：事务未提交之前，Undo保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

　　如下图这样的处理就避免了脏读的问题。

2429bf99cc43ddf3c501b514995c3132.png

当前读，快照读：

快照读：SQL读取的数据是快照版本，也就是历史版本，普通的SELECT就是快照读innodb快照读，数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成

当前读：SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

Redo Log:

　　Redo Log 是什么：Redo，顾名思义就是重做。以恢复操作为目的，重现操作；Redo log指事务中操作的任何数据,将最新的数据备份到一个地方 (Redo Log)。

　　Redo log的持久：不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体的落盘策略可以进行配置.RedoLog是为了实现事务的持久性而出现的产物。

　　Redo Log实现事务持久性：防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redolog进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

　　流程图如下：

10af1d6c65dd29d70ba69ede8808a2ae.png

　　指定Redo log 记录在{datadir}/ib_logfile1&ib_logfile2 可通过innodb_log_group_home_dir 配置指定目录存储。一旦事务成功提交且数据持久化落盘之后，此时Redo log中的对应事务数据记录就失去了意义，所以Redo log的写入是日志文件循环写入的。

  • 指定Redo log日志文件组中的数量 innodb_log_files_in_group 默认为2
  • 指定Redo log每一个日志文件最大存储量innodb_log_file_size 默认48M
  • 指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默认16M

　　Redo buffer 持久化Redo log的策略， Innodb_flush_log_at_trx_commit：

  • 取值 0 每秒提交 Redo buffer —> Redo log OS cache —>flush cache to disk[可能丢失一秒内的事务数据]。
  • 取值 1 默认值，每次事务提交执行Redo buffer —> Redo log OS cache —>flush cache to disk[最安全，性能最差的方式]。
  • 取值 2 每次事务提交执行Redo buffer —> Redo log OS cache 再每一秒执行 ->flush cache todisk操作。

来源：https://www.cnblogs.com/wuzhenzhao/p/10348566.html

发表评论

表情：
评论列表 （有 0 条评论，128人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL-事务；存储引擎

    一、事务（transaction） MySQL 事务主要用于处理操作量大，复杂度高的数据。如，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人

    野性酷女野性酷女/ 0 赞/ 62 阅读