MySQL事务

概念

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

MySQL数据库中事务默认自动提交

1
2
3
4
5
6
7
8
9
10
11
12
事务提交的两种方式：
	自动提交：
		mysql就是自动提交的
		一条DML(增删改)语句会自动提交一次事务。

	手动提交：
		Oracle 数据库默认是手动提交事务
		需要先开启事务，再提交

修改事务的默认提交方式：
	查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交 
	修改默认提交方式： set @@autocommit = 0;

事务的四大特征（ACID）

1. 原子性（Atomicity）

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

回滚可以用回滚日志（Undo Log）来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

2. 一致性（Consistency）

数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。

3. 隔离性（Isolation）

一个事务所做的修改在最终提交以前，对其它事务是不可见的。

4. 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的逻辑修改不同，重做日志记录的是数据页的物理修改。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

  • 只有满足一致性，事务的执行结果才是正确的。
  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
  • 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
  • 事务满足持久化是为了能应对系统崩溃的情况。
image-20200815101847222

Mysql怎么保证一致性的？

​ 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。

​ 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据。

Mysql怎么保证原子性的？

　　 利用Innodb的undo log。

　　　undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

​ 例如

  • (1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
  • (2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
  • (3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

-————————————————————————————————-

Mysql怎么保证持久性的？

　利用Innodb的redo log（重做日志）。

　　 正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

　　 怎么解决这个问题？

　　简单啊，事务提交前直接把数据写入磁盘就行啊。

　　这么做有什么问题？

  • 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
  • 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

　　采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

　　采用redo log的好处？

　　redo log进行刷盘比对数据页刷盘效率高，具体表现如下

    • redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
    • redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

    -————————————————————————————————-

Mysql怎么保证隔离性的？

　　利用的是锁和MVCC机制。

　　MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。 如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

并发一致性问题

丢失修改

丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。

读脏数据

读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

一个事务，读取到另一个事务中没有提交的数据

image-20200815102631015

不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

image-20200815102810415

幻影读

幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

image-20200815102940318

一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别

未提交读（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其它事务也是可见的。

提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同一数据的结果是一样的。

可串行化（SERIALIZABLE）

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。

该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

image-20200815103853857

幻读临键锁可以解决

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基本思想

在封锁一节中提到，加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

版本号

  • 系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
  • 事务版本号 TRX_ID ：事务开始时的系统版本号。

Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

1
2
3
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

image-20200815104813509

INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

  • TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
  • TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
    • 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
    • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

快照读与当前读

1. 快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

1
SELECT * FROM table ...;

2. 当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

1
2
3
INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

1
2
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

MySQL的锁

  • 表锁

  • 行锁

  • 间隙锁（Gap Locks）

  • 临键锁（Next-key Locks）

  • 共享锁/排他锁（Shared and Exclusive Locks）

  • 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

  • 插入意向锁（Insert Intention Locks）

  • 自增锁（Auto-inc Locks）

表锁

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
动增加表锁
lock table 表名字 read(write), 表名字2 read(write), 其他;
查看表上加过的锁
show open tables;
释放表锁
unlock tables;

线程1 加读锁 lock table 表名字 read;
	线程1不能查询它没有锁定的表，也不能更新这个锁定的表。
	其他线程可以查询该表的数据，也可以查询其他表的数据，但不能这个加锁的表进行修改，会一直等待获得锁。
	
线程1 加写锁 lock table 表名字 write;
	线程1可以对表进行查询、更新、删除等操作
	其他线程对这个表的操作会阻塞。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

img

表锁分析

1
2
3
4
5
6
7
8
#看看哪些表被加锁了：
show open tables;
#检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
show status like ‘table%

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

行锁(记录锁)

  • 偏向Innodb存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。

  • Innodb与MyISAM的最大不同有两点：

    • 一是支持事务（TRANSACTION）
    • 而是采用了行级锁

行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新

三级封锁协议

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

image-20200815095233877

二级封锁协议

在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

image-20200815095603609

三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

两段锁协议

在这里插入图片描述

加锁和解锁分为两个阶段进行。

事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

1
lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。

1
lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL 隐式与显示锁定

MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

1
2
3
4
5
6
7
8
9
10
11
12
13
**如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放**

假设要实现一个电影票在线交易业务，顾客A要在影院B购买电影票。业务需要涉及到以下操作：

1.从顾客A账户余额中扣除电影票价

2.给影院B的账户余额增加这张电影票价

3.记录一条交易日志

为了保证交易的原子性，要把这三个操作放在一个事务中。如何安排这三个语句在事务中的顺序呢？

如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度

InnoDB 也可以使用特定的语句进行显示锁定：

1
2
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

行锁升级表锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Session 1
begin;
select * from t1 where name = '张三' for update;

ROLLBACK
COMMIT

/****************************************************************************/
Session 2
begin;
select * from t1 where id = 3 for update;

ROLLBACK
COMMIT

session2 因为行锁升级为表锁一直在等待

InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁;否则，InnoDB使用表锁 在不通过索引(主 键)条件查询的时候，InnoDB是表锁而不是行锁。

如果在更新数据的时候出现了强制类型转换导致索引失效，使得行锁变表锁.

间隙锁（Gap Locks）

间隙锁则锁的是若干个索引间的间隙，每个间隙都是两端开放的区间

1
2
3
4
5
6
7
/*间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。*/


SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

除了手动加锁外，在执行完某些 SQL 后，InnoDB 也会自动加间隙锁。

面试常问：常考如何锁定一行

1
select * from 表 where 某一行的条件 for update;

临键锁（Next-Key Locks）

(间隙锁+行锁)

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
我们先来看看test表中存在的隐藏间隙：
(-∞, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +∞]


#SessionA
-- 根据非唯一索引列 UPDATE 某条记录
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
 
#SessionB
/* 插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

那最终我们就可以得知，在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。

即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为 (5, 11]。

共享锁/排他锁（Shared and Exclusive Locks）

共享锁(Share Lock)

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

1
SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

1
SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。

意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存InnoDB中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

意向锁是InnoDB自动加的，不需要用户干预。

q1 为什么意向锁是表级锁呢？

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

q2 意向锁怎么支持表锁和行锁并存？

（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。

插入意向锁（Insert Intention Locks）

插入意向锁本质上可以看成是一个Gap Lock

  • 普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据
  • 插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据

插入意向锁的作用是为了提高并发插入的性能， 多个事务 同时写入 不同数据 至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。

插入的过程

假设现在有记录 10， 30， 50， 70 ；且为主键 ，需要插入记录 25 。

  1. 找到 小于等于25的记录 ，这里是 10
  2. 找到 记录10的下一条记录 ，这里是 30
  3. 判断 下一条记录30 上是否有锁
    3.1 判断 30 上面如果 没有锁 ，则可以插入
    3.2 判断 30 上面如果有Record Lock，则可以插入
    3.3 判断 30 上面如果有Gap Lock/Next-Key Lock，则无法插入，因为锁的范围是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此时处于waiting状态），当 Gap Lock / Next-Key Lock 释放时，等待的事物（ transaction）将被 唤醒 ，此时 记录30 上才能获得 insert intention lock ，然后再插入 记录25

注意：一个事物 insert 25 且没有提交，另一个事物 delete 25 时，记录25上会有 Record Lock

开启两个会话，两个会话事务的隔离级别都设置为REPEATABLE-READ

Time 会话A 会话B
1 begin begin
2 select * from a where a<=13 for update
3 insert into a values (12) – waiting…… （被阻塞了，在这里等待） 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
此时执行show engine innodb status\G语句会看到以下结果
---TRANSACTION 4424, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update
--等待插入的SQL
insert into a values(12)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
--插入记录12的事物等待中（被终端会话A中的事物阻塞了），等待获得插入意向锁(lock_mode X locks gap before rec insert intention waiting)
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

------------------
TABLE LOCK table `test`.`a` trx id 4424 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

---TRANSACTION 4423, ACTIVE 55 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 241 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`a` trx id 4423 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4423 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000113f; asc      ?;;
 2: len 7; hex b3000001270110; asc     '  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b4000001280110; asc     (  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000001145; asc      E;;
 2: len 7; hex b70000012b0110; asc     +  ;;

Time 会话A 会话B
1 begin begin
2 select * from a where a<=13 for update
3 insert into a values (12) – waiting…… （被阻塞了，在这里等待）
4 commit
5 输出：Query OK, 1 row affected (17.40 sec) 前提条件是insert操作的锁没有超时

此时事务B插入成功但是还未commit，再执行show engine innodb status\G语句，会有以下输出

1
2
3
4
5
6
7
8
9
---TRANSACTION 4425, ACTIVE 26 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root
TABLE LOCK table `test`.`a` trx id 4425 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4425 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

从上面的输出可以看到在记录13上面加了一把插入意图锁（lock_mode X locks gap before rec insert intention）。
获得插入意图锁之后，我们就可以在11-13之间并发插入记录，而不需要一个事物等待另一事物，当所有相关的插入的事物都提交后， 13上的插入意向锁 便会释放。

自增锁（Auto-inc Locks）

自增锁,在提交前释放,并发插入高

s,x等锁,在COMMIT扣释放,并发插入需要等待

不能回滚到前面的值

在InnoDB中，每个含有自增列的表都有一个自增长计数器。当对含有自增长计数器的表进行插入时，首先会执行select max(auto_inc_col) from t for update来得到计数器的值，然后再将这个值加1赋予自增长列。我们将这种方式称之为AUTO_INC Lock。

AUTO_INC Lock是一种特殊的表锁，它在完成对自增长值插入的SQL语句后立即释放，所以性能会比事务完成后释放锁要高。由于是表级别的锁，所以在并发环境下其依然存在性能问题。

从MySQL 5.1.22开始，InnoDB中提供了一种轻量级互斥量的自增长实现机制，同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，进而提高自增长值插入的性能。innodb_autoinc_lock_mode和插入类型有关，在介绍它之前，我们先来看看都有哪些插入类型

  • “INSERT-like” statements

    泛指所有的插入语句, 它包括 “simple-inserts”, “bulk-inserts”, 和 “mixed-mode inserts”.

  • “Simple inserts”

    插入的记录行数是确定的：比如：insert into values，replace
    但是不包括： INSERT … ON DUPLICATE KEY UPDATE.

  • “Bulk inserts”

    插入的记录行数不能马上确定的，比如： INSERT … SELECT, REPLACE … SELECT, and LOAD DATA

  • “Mixed-mode inserts”

    这些都是simple-insert，但是部分auto increment值给定或者不给定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

1
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另外一种 “mixed-mode insert” 就是 INSERT ... ON DUPLICATE KEY UPDATE

介绍完插入类型之后，我们再来看看锁模式，即innodb_autoinc_lock_mode。

image-20200814235726139

1
2
3
4
5
6
7
0：traditonal （每次都会产生表锁）
 
 
1：consecutive （会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）
 
 
2：interleaved （不会锁表，来一个处理一个，并发最高）

事务使用的几点建议

  • 控制事务大小，减少锁定的资源量和锁定时间长度。
  • 所有的数据检索都通过索引来完成，从而避免因为无法通过索引加锁而升级为表锁。
  • 减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的数据。
  • 在业务条件允许下，尽量使用较低隔离级别的事务隔离。减少隔离级别带来的附加成本。
  • 合理使用索引，让innodb在索引上面加锁的时候更加准确。
  • 在应用中尽可能做到访问的顺序执行。
  • 如果容易死锁，就可以考虑使用表锁来减少死锁的概率

关系数据库设计理论

关系数据结构

数据结构、数据操作、数据约束

函数依赖

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖。

异常

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

不符合范式的关系，会产生很多异常，主要有以下四种异常：

  • 冗余数据：例如 学生-2 出现了两次。
  • 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
  • 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
  • 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1. 第一范式 (1NF)

属性不可分。

2. 第二范式 (2NF)

每个非主属性完全函数依赖于键码。

可以通过分解来满足。

分解前

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

  • Sno -> Sname, Sdept
  • Sdept -> Mname
  • Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno Sname Sdept Mname
1 学生-1 学院-1 院长-1
2 学生-2 学院-2 院长-2
3 学生-3 学院-2 院长-2

有以下函数依赖：

  • Sno -> Sname, Sdept
  • Sdept -> Mname

关系-2

Sno Cname Grade
1 课程-1 90
2 课程-2 80
2 课程-1 100
3 课程-2 95

有以下函数依赖：

  • Sno, Cname -> Grade

3. 第三范式 (3NF)

非主属性不传递函数依赖于键码。

上面的 关系-1 中存在以下传递函数依赖：

  • Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

Sno Sname Sdept
1 学生-1 学院-1
2 学生-2 学院-2
3 学生-3 学院-2

关系-12

Sdept Mname
学院-1 院长-1
学院-2 院长-2

ER 图

Entity-Relationship，有三个组成部分：实体、属性、联系。

用来进行关系型数据库系统的概念设计。

实体的三种联系

包含一对一，一对多，多对多三种。

  • 如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
  • 如果是一对一，画两个带箭头的线段；
  • 如果是多对多，画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系。

image-20200815114836972

表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系。

image-20200815115129109

联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

image-20200815115235884

表示子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。

image-20200815115400700

https://www.jianshu.com/p/dca007208a58

https://blog.csdn.net/u010841296/article/details/87909468

https://www.cnblogs.com/Terry-Wu/p/12219019.html

https://github.com/CyC2018/CS-Notes/blob/master/notes/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F%E5%8E%9F%E7%90%86.md#mysql-%E9%9A%90%E5%BC%8F%E4%B8%8E%E6%98%BE%E7%A4%BA%E9%94%81%E5%AE%9A

ER图

https://blog.csdn.net/qq_43255017/article/details/106442887?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param

https://www.cnblogs.com/CuiHongYu/p/10845354.html