**DML**
语句(**insert、update、delete**
),现在 **MySQL**
中有 **InnoDB & NDB**
存储引擎支持事务。**Atomicity**
):在 **InnoDB**
存储引擎中通过 **undo log**
来实现了,它记录了数据修改之前的值(逻辑日志),当发生异常时就可以使用 **undo log**
来实现回滚操作。所以要嘛成功,否则失败;**Consistency**
):指数据库的完整性约束没有被破坏,事务执行的前后都是合法的数据状态;除了数据库自身的完整性约束外,还有用户自定义的完整性,该方式通常在代码中控制;**Isolation**
):在有了事务的定义后,在数据库里会有很多的事务同时会去操作同一张表或同一行数据,这时会产生并发或干扰的操作;那么对于隔离性的定义就是多个事务对表或行进行并发操作时,应该是透明且互不干扰的,通过这种方式来最终也是保证业务数据的一致性;**Durability**
):在对数据库的任何操作,只要事务提交成功,数据就是永久性的,不能因为系统宕机或重启数据库的服务器又恢复到原来的状态。持久性是通过 **redo log & double write**
双写缓冲来实现的。在操作数据库时,会先写到内存的 **buffer pool**
里并且记录 **redo log**
;如果在刷盘之前出现了异常,在重启后就可以读取 **redo log**
的内容,写入到磁盘,保证数据的持久性。当然,恢复成功的前提是数据页本身是没有被破坏的,这个是通过 **double write**
来保证。**Spring**
框架的事务或类似 **Navicat**
客户端工具操作数据库,最终都是发送一个指令到数据库中执行。在 **InnoDB**
存储引擎中的事务默认情况下是开启自动提交的,所以在下面的 **update SQL**
语句是自动开启了一个事务并提交,最终写入到了磁盘;当设置 **autocommit = false**
时,数据库的事务需要手动地区开启与结束。手动开启事务有 **begin | start transaction**
方式,手动结束事务有 **commit | rollback**
** **方式;在回滚时和当客户端的连接断开时,事务也会结束。-- 数据库版本 8.0.11
select version();
-- 默认引擎 InnoDB
show variables like '%engine%';
-- 查看是否自动提交,默认是
show variables like 'autocommit';
-- 做更新操作
update employees set gender = 'M' where emp_no = 1;
**TransactionA & TransactionB**
** **两个事务情况下: **TransactionA**
里首先通过 **where id = 1**
的条件去查询一条数据,返回 **name = John,age = 23**
的数据;**TransactionB**
同样去操作 **where id = 1**
的这行数据,通过一个 **update**
的操作把这行数据的 **age**
修改为了 **18**
,但事务还没提交;**TransactionA**
再次去执行相同的查询操作,发现数据发送了变化,获取到 **age = 18**
的数据;那么在一个事务里,由于另一个事务在一个时间段内修改了数据并且没有提交而导致前后两次读取的数据不一致的情况就是事务并发里的脏读问题**TransactionA & TransactionB**
** **两个事务情况下: **TransactionA**
通过 **where id = 1**
查询到一条数据,然后在 **TransactionB**
里执行了一个 **update**
操作并提交修改的事务;接着在 **TransactionA**
里读取到了其他事务已经提交的数据而导致前后两次读取数据不一致的情况,这种一个事物读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况在事务并发里称之为不可重复读问题。**TransactionA & TransactionB**
** **两个事务情况下: **TransactionA**
中执行了一个范围查询,这时满足条件的数据只有一条;在 **TransactionB**
里插入了一行数据并提交该事务;然后在 **TransactionA**
中再次去查询时就发现多了一行数据;这种一个事务前后两次读取数据不一致是由其他事务插入数据造成的的情况称之为事务中的幻读问题。**SQL92**
标准提供一定的事务隔离级别来解决事务并发的问题。但是不同的数据库厂商或存储引擎实现有部分的差异,比如 **Oracle**
里只有两种 **ReadCommited**
已提交读和 **Seriallizable**
。 **Read Uncommitted**
未提交读:表示当一个事务可以读取到其他事务未提交的数据时,就会出现脏读,它没有解决任何问题;**Read Committed**
已提交读:表示一个事务只能读取到其他事务已提交的数据,不能读取到其他事务未提交的事务,它解决了脏读的问题;**Repeatable Read**
可重复读:表示在同一个事务里多次读取同样数据的结果是一致的,它解决了不可重复读的问题;**Serializable**
串行化:在这个隔离级别中的所有事务都是串行执行的,对数据的操作需要排队,已经不存在事务的并发操作,它解决了所有的问题。**MySQL**
中的 **InnoDB**
存储引擎支持的隔离级别与 **SQL92**
定义的基本一致,隔离级别越高,事务的并发度就越低。在 **InnoDB**
存储引擎中的 **Repeatable Read**
解决了幻读的问题,这个也是 **InnoDB**
默认使用 **RR**
作为事务隔离级别的原因,既能保证数据的一致性,又支持比较高的并发度。
**Read Uncommitted**
**:**在 **RU**
隔离级别下不加锁;
**Read Committed**
:在 **RC**
隔离级别下,普通的 **select**
都是快照读,使用 **MVCC**
实现,加锁的 **select**
都是使用记录锁,因为没有 **Gap Lock**
;另外外键约束检查(foreign-key constraint checking
)和重复键检查(**duplicate-key checking**
)时会使用间隙锁来封锁区间,所以 **RC**
会出现幻读的原因。
**Repeatable Read**
:在 **RR**
隔离级别下,普通的 **select**
使用快照读,底层使用 **MVVC**
来实现。但加锁的 **select(select .... in share mode / select ... for update)**
和更新操作 **delete/update**
等语句使用当前读,底层使用记录锁、间隙锁、临键锁实现。
**Serializable**
:在 **Serializable**
隔离级别下的所有 select
语句都会被隐式地转换为 **select ... in share mode**
,会和 **update/delete**
语句互斥。
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read Uncommitted 未提交读 | × | × | × |
Read Committed 已提交读 | √ | × | × |
Repeatable Read 可重复读 | √ | √ | √ InnoDB |
Serializable 串行化 | √ | √ | √ |
**RU & Serializable**
不能选用,因为它们要嘛不能解决事务的并发问题,要嘛不能性能太低。**RC & RR**
之间的区别: **RR**
的间隙锁会导致锁定范围的扩大;**RR**
会锁表,**RC**
会锁行;**RC**
的半一致性(**semi-consistent**
)读可以增加 **update**
操作的并发性;另外在 **RC**
中的一个 **update**
操作在读到一行已经加锁的记录,此时的 **InnoDB**
返回记录最近提交的版本,由 **MySQL**
的上层判断此版本是否满足 **update**
的 **where**
条件。如果满足的情况下,**MySQL**
会重新发起一次读操作,此时会读取行的最新版本并加锁。LBCC
:在需要保证前后两次读的数据一致情况下可以在读取数据时,锁定需要操作的数据,不让其他事务有机会对其进行修改;这种方式叫基于锁的并发控制 **Lock Based Concurrency Control**
。如果只是基于锁来实现事务的隔离,一个事务读取时不允许其他事务进行修改就意味着不支持并发的读写操作。而大多数的应用场景都是读多邪少的,这样会降低对数据的操作效率。**MVCC**
:在需要保证前后两次读的数据一致情况下可以在读取数据时,可以在修改数据时建立一个备份(快照),后面的读操作就读取该快照;这种方式称为 **Multi Version Concurrency Control**
多版本的并发控制。**MVCC**
主要思想是查询在 **TransactionA**
开始之间已经存在的数据,即使它在后面已经被修改或删除,在 **TransactionB**
之后的新增数据是查询不到的。而且在 **InnoDB**
存储引擎为每行记录都实现了两个隐藏字段对快照的创建时间与保证读取到的是快照而不是最新的数据问题解决: **DB_TRX_ID**
:表示事务 ID
(**6 byte**
),在插入或更新行的最后一个事务的事务 **ID**
,该 **ID**
是自动递增的;也可以理解为创建版本号,当数据新增或修改为新数据时就记录当前的事务 **ID**
。**DB_ROLL_PTR**
:表示回滚指针(**7 byte**
),可以理解为删除版本号,当数据被删除或记录为旧数据时就记录当前事务 **ID**
;**DB_ROW_ID**
:前面说过当没有定义主键和满足条件的唯一主键时,在 **InnoDB**
中会有该隐藏字段。**MVCC**
模拟: 其查询规则是只能查找 **DB_TRX_ID < Current_DB_TRX_ID | DB_ROLL_PTR > DB_TRX_ID**
,也就是不能查询在事务开始之后插入的数据,Ken
的 **DB_TRX_ID**
大于 **2**
,所以还是只能查到两条数据。
**TransactionA**
初始化数据-- 开启事务
BEGIN;
-- 插入两条数据
INSERT INTO user VALUES ( NULL, 'John' );
INSERT INTO user VALUES ( NULL, 'Sky' );
-- 提交事务
COMMIT;
**ID**
,删除版本为空;id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
**TransactionB**
开启事务第一次查询数据,但不提交事务;读取到两条数据,此时的事务 **ID = 2**
;-- 开启事务
BEGIN;
-- (1) 第一次查询
SELECT * FROM user;
id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
**TransactionC**
插入一条数据,此时多了一条数据,它的创建版本号是当前事务编号 3
-- 开启事务
BEGIN;
-- 插入数据
INSERT INTO user VALUES ( NULL, 'Ken' );
-- 提交事务
COMMIT;
id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
3 | Ken | 3 | undefined |
**TransactionB**
第二次执行查询语句 **SELECT * FROM user;**
id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
**TransactionD**
删除 **id = 1**
的数据;-- 开启事务
BEGIN;
-- 删除 id = 1 的数据
DELETE FROM `user` WHERE id = 1;
-- 提交事务
COMMIT;
**John**
的删除版本记录为当前的事务 **ID = 4**
,其他数据不变id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | 4 |
2 | Sky | 1 | undefined |
3 | Ken | 3 | undefined |
**TransactionB**
第三次执行查询语句 **SELECT * FROM user;**
**DB_TRX_ID < Current_DB_TRX_ID | DB_ROLL_PTR > DB_TRX_ID**
或未删除,在事务开始之后的删除数据还是可以查询出来。id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
**TransactionE**
执行更新操作,这个的事务 **ID = 5**
-- 开启事务
BEGIN;
-- 更新 id = 2 的数据
UPDATE `user` SET `name` = 'Jack' WHERE id = 2;
-- 提交事务
COMMIT;
**ID = 5(undo)**
产生了一条新数据,DB_TRX_ID = Current_DB_TRX_ID = 5
;id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | 4 |
2 | Sky | 1 | undefined |
3 | Ken | 3 | 5 |
3 | Jack | 5 | undefined |
**TransactionB**
第四次执行查询语句 **SELECT * FROM user;**
**DB_TRX_ID < Current_DB_TRX_ID | DB_ROLL_PTR > DB_TRX_ID**
或未删除, **Jack**
的 **DB_TRX_ID > 2**
代表着在事务之后增加的,所以查询不出来;**Ken**
的 **DB_ROLL_PTR > 2**
代表着事务之后删除的,可以查询出来;**TransactionB**
几次查询到的数据都没有发生变化。id | name | DB_TRX_ID | DB_ROLL_PTR |
---|---|---|---|
1 | John | 1 | undefined |
2 | Sky | 1 | undefined |
**InnoDB**
存储引擎中的 **MVCC**
是通过 **undo log**
实现的;但 **MVCC**
和锁是协同使用的,这两种方案并不是互斥的。**MySQL**
官网中对 **InnoDB**
的锁分为了 8
类: **InnoDB**
中的表锁是锁住整张表,而行锁是锁住表中的一行数据;所以表锁的粒度大于行锁;TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM user LOCK IN SHARE MODE; | |
BEGIN; | |
SELECT * FROM user LOCK IN SHARE MODE; |
**FOR UPDATE**
给一行数据手动地加排它锁。TransactionA | TransactionB |
---|---|
BEGIN; | |
UPDATE user SET name=‘Jack’ WHERE id = 1; | |
BEGIN; | |
– 下面操作都是阻塞的 |
SELECT * FROM user
LOCK IN SHARE MODE;
** SELECT * FROM user
FOR UPDATE;**
** DELETE FROM user
WHERE id = 1;** |
**InnoDB**
中就可以支持更多粒度的锁;以及提高加锁的效率,如果没有意向锁的情况下准备给一张表加上表锁时必须先判断是否有其他事务锁定了其中的某行,有的情况下就不能加锁;也就是说需要去扫描整张表才能确定是否加表锁,当数据量特别大时,加锁的效率就很低。在引入意向锁后只是需要判断这张表上是否有意向锁,如果有就返回失败,否则就加锁成功。在 **InnoDB**
里面的表锁可以理解成一个标志,这就是提高加锁效率的原因。TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM user FOR UPDATE; | |
BEGIN; | |
– 阻塞 |
** LOCK TABLES user
WRITE;**
– 释放锁
** UNLOCK TABLES;** |
**where id > 4 and id < 7 | where id = 6**
;间隙锁主要是阻塞插入,相同的间隙锁之间不冲突。 **Gap Lock**
只是在 **RR**
中存在,如果要关闭间隙锁就是把事务隔离级别设置成 **RC**
且设置 **innodb_locks_unsafe_for_binlog = ON**
TransactionA | TransactionB |
---|---|
BEGIN; | |
select * from t2 where id = 6 for update; | |
select * from t2 where id > 20 for update; | |
BEGIN; | |
– 执行 where id = 6 时阻塞 |
INSERT INTO t2
(id
, name
) VALUES (5, ‘5’);
– 执行 where id = 6 时阻塞
** INSERT INTO t2
(id
, name
) VALUES (6, ‘6’);**
– 执行 where id > 20 时阻塞
INSERT INTO t2
(id
, name
) VALUES (11, ‘11’);
– 不阻塞,因为相同间隙锁不冲突
select * from t2 where id = 6 for update; |
**MySQL**
里默认的行锁算法,相当于记录锁加上了间隙锁。当唯一性索引在等值查询匹配到一条记录时,会退化成记录锁,在没有匹配到任何记录时,会退化成间隙锁。例如:**where id > 5 and < 9**
,它包含了记录不存在的区间,也包含了一个 **Record 7**
。临键锁锁住的是最后一个 **key**
的下一个左开右闭的区间,这样是为了解决幻读的问题。TransactionA | TransactionB |
---|---|
BEGIN; | |
锁住的范围是 (5, 10] | |
select * from t2 where id > 5 and id < 9 for update; | |
BEGIN; | |
** – 因为锁住是 (5, 10],Non-Block** |
SELECT * FROM t2 WHERE id = 4 FOR UPDATE;
– 因为锁住是 (5, 10],Block
INSERT INTO t2
(id
, name
) VALUES (6, ‘6’);
– 因为锁住是 (5, 10],Block
INSERT INTO t2
(id
, name
) VALUES (8, ‘8’);
– 因为锁住是 (5, 10],Block
SELECT * FROM t2 WHERE id = 10 for update; |
**InnoDB**
的锁是锁住的整张表,而不是具体行;现在创建一个无索引的表且手工的在两个会话中开启两个事务,在 **TransactionA**
中锁住 **where id = 1**
的数据,然后在 **TransactionB**
中尝试给 **where id = 2**
的记录加锁时被阻塞了;接着再插入一条不存在的数据也发现被阻塞了。所以在没有索引的表在 **InnoDB**
中是锁住整张表的,而不是 **Record**
。TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM user where id = 1 FOR UPDATE; | |
BEGIN; | |
– 阻塞 |
SELECT * FROM user
WHERE id = 2 FOR UPDATE;
– 阻塞
** INSERT INTO user
VALUES(4, ‘Jack’);** |
**InnoDB**
的锁是锁住的是具体行,在使用相同 **id**
值去加锁会出现冲突;使用不同的 **id**
去加锁就可以加锁成功TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM user where id = 1 FOR UPDATE; | |
BEGIN; | |
– 阻塞 |
SELECT * FROM user
WHERE id = 1 FOR UPDATE;
– 成功
** SELECT * FROM user
WHERE id = 2 FOR UPDATE;** |
**InnoDB**
的锁是锁住的是索引,在 **id is primary key & name is unique key**
的情况下;首先在 **TransactionA**
中通过 **name = 'John'**
去进行加锁,然后在 **TransactionB**
中获取一样的锁会失败。然后使用 **id = 1**
的去加锁发现也是会失败的。TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM user where name = ‘John’ FOR UPDATE; | |
BEGIN; | |
– 阻塞 |
SELECT * FROM user
where name = ‘John’ FOR UPDATE;
– 阻塞
** **SELECT * FROM user
where id = 1 FOR UPDATE; |
**Primary Key**
时,**InnoDB**
存储引擎会选择主键作为聚集索引;**InnoDB**
存储引擎会选择第一个不含有 **NULL**
值的唯一索引作为主键索引;**InnoDB**
存储引擎会选择内置 **6**
个字节长的 **ROWID**
作为隐藏的聚集索引,它会随着行记录的写入而主键递增。**MySQL**
中有一个参数控制获取锁的等待时间,默认是 **50m**
。show VARIABLES like 'innodb_lock_wait_timeout';
**TransactionA**
中检测到了死锁就马上退出了;在 **TransactionB**
不需要等待 **50m
**就可以获取到锁。由于死锁的发送需要满足一定的条件,当死锁发生时,一般情况在 **InnoDB**
中可以通过算法 **wait-for graph**
自动检测到。那么导致死锁的产生条件有: TransactionA | TransactionB |
---|---|
BEGIN; | |
SELECT * FROM t2 WHERE id = 1 FOR UPDATE; | |
BEGIN; | |
DELETE FROM t2 WHERE id = 4 ; | |
** – 执行完后,执行 Session 2 中的第二句会马上退出** | |
** UPDATE t2 SET name = ‘test_dead_lock’ WHERE id = 4;** | |
– 出现死锁,Session 1 退出,获取到锁 | |
DELETE FROM t2 WHERE id = 1 ; |
-- Innodb_row_lock_current_waits: 当前正在等待锁定的数量;
-- Innodb_row_lock_time: 从系统启动到现在锁定的总时间长度,单位 ms;
-- Innodb_row_lock_time_avg : 每次等待所花平均时间;
-- Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花的时间;
-- Innodb_row_lock_waits : 从系统启动到现在总共等待的次数;
show status like 'innodb_row_lock_%';
-- 查看当前运行的所有事务和具体的语句
select * from information_schema.INNODB_TRX;
-- 查看当前出现的锁
select * from information_schema.INNODB_LOCKS;
-- 查看锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;
**kill**
事务对应的线程 **ID**
;也尽量的在应用端编码的过程中避免死锁。**where**
条件的操作,避免锁表;