innoDB 的特性:
从图中由上至下红色框中的信息是: 基于主键的聚集索引 , 数据缓存, 外键支持(逻辑上建立外键), 行级别锁, 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 语句会自动提交, 当自动提交是关闭的, 可以通过以下方式来创建事务提交:
BEGIN;-- 这两个二选一开启事务
START TRANSACTION;
-- 这是一个事务
- UPDATE ......
- UPDATE ......
COMMIT;-- 提交或者回滚
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 回滚了, 这样子就出现了脏读问题.
再来看第二个图: 事务 A 先查询了数据信息, 此刻事务 B 进行了修改并提交, 然后事务 A 又去查询了一遍, 这个时候就会出现不可重复读的问题.
第三张图: 通过范围查询获得一条数据, 此刻事务 B 插入了一条数据, 事务 A 又去查询获得了两条数据, 此刻就发生了幻读.
综上, 事务并发给我们带来了三个主要问题: 脏读, 不可重复读, 幻读.
事务的隔离级别:
Read Uncommitted(未提交读) -- 未解决并发问题, 事务未提交对其他事务也是可见的, 脏读(dirty read).
Read Committed(提交读) -- 解决脏读问题, 一个事务开始之后, 只能看到自己提交的事务所做的修改, 不可重复读(nonrepeatableread).
Repeatable Read (可重复读) -- 解决不可重复读问题在同一个事务中多次读取同样的数据结果是一样的, 这种隔离级别未定义解决幻读的问题.
Serializable(串行化) -- 解决所有问题, 最高的隔离级别, 通过强制事务的串行执行.
设置 read uncommitted 级别: set session transaction isolation level read uncommitted;
innoDB 对隔离级别的支持程度:
在 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 锁, 顾名思义, 共享锁就是多个事务对于同一数据可以共享一把锁, 都能访问到数据, 但是只能读不能修改, 加锁释锁方式:
-- 共享锁加锁
- BEGIN
- select * from users WHERE id=1 LOCK IN SHARE MODE;
- rollback;
- commit;
-- 在以上的 SQL 枷锁后未执行提交或者回滚执行其他事务执行
select * from users where id =1; -- 可以执行, 共享锁特性
update users set age=19 where id =1;-- 会阻塞
排他锁:
又称为写锁, 简称 X 锁, 排他锁不能与其他锁并存, 如一个事务获取了一个数据行的排他锁, 其他事务就不能再获取该行的锁(共享锁, 排他锁), 只有该获取了排他锁的事务是可以对数据行进行读取和修改,(其他事务要读取数据可来自于快照), 加锁释锁方式: delete / update / insert 默认加上 X 锁.
-- 自动获取排它锁
set session autocommit = OFF; -- 设置手动提交事务
update users set age = 23 where id =1; -- 执行该语句后未提交, 在其他线程上, 执行下列其他事务执行语句会处于阻塞 commit;
ROLLBACK;
-- 手动获取排它锁
- set session autocommit = ON;
- begin
- select * from users where id =1 for update;
- commit;
-- 其他事务执行
- select * from users where id =1 lock in share mode;
- select * from users where id =1 for update;
- select * from users where id =1;
innoDB-- 行锁到底锁了什么?
首先先来看一下测试表的结构, 其中用的是 InnoDB 引擎, 有一个 name 的唯一索引, 主键自增, 有 3 条数据
- DROP TABLE IF EXISTS `users`;
- CREATE TABLE `users` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `uname` varchar(32) NOT NULL,
- `userLevel` int(11) NOT NULL,
- `age` int(11) NOT NULL,
- `phoneNum` char(11) NOT NULL,
- `createTime` datetime NOT NULL,
- `lastUpdate` datetime NOT NULL,
- PRIMARY KEY (`id`),
- KEY `idx_name` (`uname`) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=100006 DEFAULT CHARSET=utf8mb4;
- -- ----------------------------
- -- Records of users
- -- ----------------------------
- INSERT INTO `users` VALUES ('1', '李二狗', '2', '18', '13666666666', '2018-12-01 15:39:46', '2018-12-01 15:39:50');
- INSERT INTO `users` VALUES ('2', '张三丰', '1', '29', '13777777777', '2018-12-01 16:35:41', '2018-12-01 16:35:44');
- INSERT INTO `users` VALUES ('3', '武大郎', '2', '44', '13888888888', '2018-12-01 16:36:01', '2018-12-01 16:36:03');
案例 1: 紧接着在一个事务中执行以下语句: 可以发现我们把事务设置成手动提交, 但是我并未提交或者回滚:
- set session autocommit = OFF;
- update users set lastUpdate=NOW() where phoneNum = '13666666666';
然后在其他事务中执行如下语句: 会发现, 上述 SQL 执行修改会获得默认的排它锁, 而此刻并未释放, 锁的列是 ID 为 1, 然后我们下列要修改 ID 为 2 的数据也是出于阻塞, 这是为什么呢?
- update users set lastUpdate=NOW() where id =2;
- update users set lastUpdate=NOW() where id =1;
案例 2, 执行以下语句, 可以发现我们把事务设置成手动提交, 但是我并未提交或者回滚:
- set session autocommit = OFF;
- update users set lastUpdate=NOW() where id = 1;
然后在其他事务上执行: 会发现下面 2 条 SQL 执行后 第一条会顺利执行, 而第二条会被阻塞.
- update users set lastUpdate=NOW() where id =2;
- update users set lastUpdate=NOW() where id =1;
案例三: 执行一下语句:
- set session autocommit = OFF;
- update users set lastUpdate=NOW() where `name` = '李二狗';
然后在其他事务上执行: 会发现前面两条会执行成功, 而后面两条执行失败
-- 其他查询执行
- update users set lastUpdate=NOW() where `name` = '李二狗';
- update users set lastUpdate=NOW() where id =1;
- update users set lastUpdate=NOW() where `name` = '张三丰';
- 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.
- begin;
- insert into users(name , age ,phoneNum ,lastUpdate ) values ('tom2',30,'1344444444',now());
- ROLLBACK;
针对行锁的算法:
临键锁 Next-key Locks:
Next-key locks:InnoDB 行锁的默认算法. 锁住记录 + 区间 (左开右闭), 当 sql 执行按照索引进行数据的检索时, 查询条件为范围查找(between and,<,> 等)并有数据命中则此时 SQL 语句加上的锁为 Next-key locks, 锁住索引的记录 + 区间(左开右闭). 先来搞一张表:
- DROP TABLE IF EXISTS `test`;
- CREATE TABLE `test` (
- `id` int(11) NOT NULL ,
- `name` varchar(32) NOT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- ----------------------------
- -- Records of users
- -- ----------------------------
- INSERT INTO `test` VALUES ('1', '1');
- INSERT INTO `test` VALUES ('4', '4');
- INSERT INTO `test` VALUES ('7', '7');
- INSERT INTO `test` VALUES ('10', '10');
在 InnoDB 的默认行级算法中会对数据行进划分: 可以看到是一个左开右闭的这个一个展现.
执行以下 sql 不提交: 由于有数据命中则会锁住(4,7](7,10] 两个区间. 未提交的情况下执行下列其他事务中前四条全部阻塞而最后一条会成功执行.
- begin;
- select * from test where id>5 and id<9 for update;
-- 其他事务
select * from test where id=4 for update; -- 阻塞
select * from test where id=7 for update; -- 阻塞
select * from test where id=10 for update; -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (9, '9'); -- 阻塞
INSERT INTO `test` (`id`, `name`) VALUES (11, '11');-- 成功
为什么 InnoDB 要选择(临键锁)Next-key locks 作为 InnoDB 行锁的默认算法? 解决幻读, 因为 B+Tree 是有顺序的, 从左往右顺序递增, 把临键区间也锁住, 其他事务要往里插入数据是插不进去的.
间隙锁 Gap Locks: 继临键锁要是没有命中数据的情况下:
Gap 锁只在 Repeatable Read (可重复读) 的隔离级别的情况下才存在.
记录锁 Record Locks: 继临键锁之后, 在条件为精准匹配的时候.
那么锁是怎么解决上述产生 脏读, 不可重复读, 以及幻读的情况呢?
解决脏读:
解决不可重复读:
解决幻读:
死锁:
多个并发事务(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 的效果:
-- 数据准备
insert into teacher(name,age) value ('seven',18) ;-- 假设事务版本为 1
insert into teacher(name,age) value ('qing',20) ;-- 假设事务版本为 1
- begin; ----------1
- select * from users ; ----------2
- begin; ----------3
- update teacher set age =28 where id =1;----------4
再每一行数据 插入数据表的时候, 都会开启一个事务, 每一行数据都会保存执行的时候所获取的事务版本号, 当进行修改的时候会先 copy 一份待修改的数据到 Undo 缓冲区, 在提交后然写入磁盘, 在此过程中会讲原先的数据行的删除版本号置为当前事务 ID, 然后再在新的数据行把数据行版本号置为当前事务 ID.
当我们按照 1,2,3,4,2 的顺序去执行的时候, 首先执行 1 拿到的事务 ID 是 2, 那么查询出来就是原始数据, 这个时候事务并没有提交或者回滚, 然后执行 3 开启一个事务拿到的事务 ID 为 3 , 此刻进行 update 操作的时候会 copy 数据到 Undo 缓冲区, 然后将原始数据的删除版本号置为 3, 把新数据的事务版本号置为 3, 再执行 3 的时候由于此刻事务 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 中的数据可作为数据旧版本快照供其他并发事务进行快照读.
如下图这样的处理就避免了脏读的问题.
当前读, 快照读:
快照读: 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 进行重做, 从而达到事务的未入磁盘数据进行持久化这一特性.
流程图如下:
指定 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 操作.
MySQL-innoDB 存储引擎(事物, 锁, MVCC)
来源: http://www.bubuko.com/infodetail-2945382.html