文章目录
事务管理
原子性（Atomicity）
现实世界中转账操作是一个不可分割的操作，也就是说要么压根儿就没转，要么转账成功，不能存在中间的状态，也就是转了一半的这种情况。设计数据库的大叔们把这种要么全做，要么全不做的规则称之为 原子性 。也就是时候事务的完整性
隔离性（Isolation）
在状态转变的时候，要保证其他的状态不会影响本次状态的转换，这个规则被称之为隔离性。
Read Uncommitted(读未提交)：所有的事务都可以看到其他为提交的事务的数据。读未提交会造成脏读（Dirty Read）。
Read Committed(读取提交内容)：大多数数据库的默认隔离级别，就是只能读到已经提交了的事务。有不可重复读问题。update问题
REPEATABLE READ(可重复读)：mysql默认隔离级别，可以重复读取，不可以update操作。产生幻读问题。
Serializable(串行化)：事务排队执行。脏读：读到了其他事务回滚前的数据。 不可重复读：两次读取之间，其他事务将数据修改了。
幻读：两次读取之间，其他事务新增了在搜索条件范围内的数据。
一致性（Consistency）
如果数据库中的数据全部符合现实世界中的约束，我们说这些数据就是一致的，或者说符合一致性的。
一致性就是指事务的完整性再加上事务操作的逻辑正常
如何实现一致性：
- 触发器可以
- 数据库某些操作的原子性和隔离性都是保证一致性的一种手段，在操作执行完成后保证符合所有既定的约束则是一种结果。
持久性（Dureblility）
就是指转换对应的数据库操作所修改的数据都应该在磁盘总保留下来
事务的状态
- 活动的（active) = = =》 事务正在执行过程中
- 部分提交的（partially committed）= = =》 当事务的最后一个操作执行完成，但由于操作都在内存中执行，但是影响没有刷新到磁盘
- 失败的（failed） = = =》当事务处在活动的，部分提交的转态时，可能 遇到了某些错误，而无法继续执行，或者人为的停止当前事务的执行。
- 中止的（aborted）= = =》如果事务执行了一半 然后变为了失败的状态，就会撤销失败事务对当前数据库的影响，这个过程叫做回滚，然而当回顾你操作完成时，数据库回到了执行任务之前的状态，这个状态就是该事务出在了中止状态
- 提交的（committed）= = =》修改的数据提交到磁盘只有这个状态叫做提交状态；
开启事务
BIGIN [WORK] ;
bigin语句代表着开启一个事务，后边的单词可有可无，后面可以接着写语句，这些语句都属于此次事务
START TRANSCATION
START TRANSCATION语句和上面的语句效果相同，只是后面可以加一些修饰词
- READ ONLY: 标识当前事务是只读事务不允许修改那些其他事务也能访问到表中的数据，也就是属于该事物的数据库操作只能读去数据，不能修改数据，但是对于临时表来说，由于他们只能在当前会话中可见，所以只读事务其实也是可以对临时表增删改；
- READ WRITE：读写事务，既可以读也可以写
- WITH CONSISTENT SNAPSHOT: 启动一致性读
提交事务
COMMIT [WORK]
整个流程：
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
手动中止事务
当在事务中写语句写错了，可以手动使用下面的语句将数据库恢复到事务执行样子：
ROLLBACK [WORK]
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
这个是需要程序员手动回滚事务时才使用的，如果执行事务的过程中遇到了错误会自动回滚事务
自动提交
MySQL中有一个系统变量 autocommit：
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
根据以上查出来的该变量的值是ON，意思就是在默认情况下，如果我们不使用START TRANSATION开启事务，那么每一条语句就都算是一个独立的事务，这种特性被叫做事务的自动提交
如何关闭自动提交
- 直接显示的创建事务
BEGIN或者START TRANSACTION开启事务 - 把系统变量autocommit的值设置为OFF
SET autocommit = OFF
隐式提交
当我们使用START TRANSACTION或者GEGIN开启了事务时，或者把autocommit的值改成了off时，事务就不会在提交，，但是如果输入了某些语句系统会悄悄提交事务，就像我们输入了commit一样，这种情况叫做隐式提交：
当隐式提交之后就相当于退出了此事务需要重新开启事务
什么情况下会隐式提交
- 定义或者修改数据库对象的数据定义语言。（DDL）
数据对象就是指数据库，表，视图，存储过程等等一系列东西- 隐式使用或修改mysql数据库中的表
当我们使用 ALTER USER 、 CREATE USER 、 DROP USER 、 GRANT 、 RENAME USER 、 REVOKE 、 SET
PASSWORD 等语句时也会隐式的提交前边语句所属于的事务。- 事务控制或者关于锁的语句
当我们还在一个事务还没提交或者回滚时，又开启了另一个事务，或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句。- 加载数据的语句
- 关于MySQL复制的一些语句
使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前边语句
所属的事务。- 使用其他的一些语句
使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。
保存点
防止回滚一夜回到解放前： 既是在事务对应的数据库语句中打几个点，我们在调用 ROLLBACK 语句时可以指定会滚到哪个点，而不是回到最初的原点：
设置保存点：
SAVEPOINT [WORK] TO [SAVEPOINT] 保存点名称
删除保存点：
RELEASE SAVEPOINT 保存点名称;
锁
解决并发事务访问相同记录的情况大致可以划分为：
并发事务访问相同记录的情况大致可以分为3种：
- 读-读 情况：并发事务读取相同的记录。
- 读取操作对记录没有影响，所有允许这中情况发生。
- 写-写 情况：并发事务对相同记录做出改动。
- 在这个情况下，会发生脏写问题。所有需要进行排队执行，而排队是通过锁实现的。
- 锁中有trx和is_waiting信息，trx代表是哪个事务生成的锁，is_waiting代表事务是否在等待。
- 读-写 和 写-读 操作：一个事务进行读取，另一个事务进行改动操作。
- 会产生脏读、不可重复读、幻读问题。
- 方案一：读操作使用多版本并发控制（MVCC）,写操作进行加锁。
- MVCC就是生成一个ReadView，然后通过ReadView找到符合条件的记录版本（在undo日志中）。
- 方案二：读写操作都使用锁。
- 方案一：读操作使用多版本并发控制（MVCC）,写操作进行加锁。
- 会产生脏读、不可重复读、幻读问题。
当一个事务相对这条记录做改动时，首先会看有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联：
- trx信息：代表锁结构是哪个事务的
- is_waiting：代表当前事务是否在等待。
当第一个事务成功的修改了这条记录，因为没有之前的事务为这条记录加锁，多以is_wating就是false
如果这时有另一个事务进来同时操作这一条记录，且前一条事务没有提交时：
那么就会产生新的锁结构与这条记录关联，不过这条做结构的is_wating 是true，当前事务需要等待，这个场景就是获取锁失败，假锁失败，没有成功的获取到锁
如果提交T1事务之后，接下来就会看看还有没有别的事务在等待获取锁，发现事务T2还在等待获取锁，所以把T2对应的锁结构的is_waiting设置为false，然后该事物对相应的线程就会被唤醒，此时事务T2就算获取到锁了
如何解决脏读，不可重复读，幻读这些问题
- 读操作利用多版本并发控制，写操作进行
加锁 - 读、写操作都加锁
锁定读
- 共享锁，Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
- 独占锁，也常称排它锁，英文名：Exclusive Locks。简称锁。在事务要改动一条记录时，需要先获取该记录的X锁。
说人话就是事务一在读某一条记录时，会先获取一个S锁，事务二也来读取这一条记录那么，此时事务二也可以获取一个S锁，但是这两个S锁是不互斥的，也就是两个事务可以一起读
若是T2想要在获取一个 X锁，那么这个操作会被阻塞，知道T1S锁被释放掉；
锁定读的语句
- 对读取的记录加锁
SELECT ... LOCK IN SHEARE MODE;
也就是在普通的SELECT语句后加上面的语句，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录S锁， （比方说别的事务也使用 SELECT … LOCK IN SHARE MODE 语句来读取这些记录），但是不能继续获取这些记录的X锁（比方说使用 SELECT … FOR UPDATE语句来读取这些记录，或者直接修改这些记录），若是想要获取这些记录的X锁，那么它们会阻塞，直到当前事务上面的S锁释放掉。
- 对读取的记录加X锁：
SELECT ... FOR UPDATE;
也就是在普通的 SELECT 语句后边加 FOR UPDATE ，如果当前事务执行了该语句，那么它会为读取到的记录加 X锁 ，这样既不允许别的事务获取这些记录的 S锁 （比方说别的事务使用 SELECT … LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X锁 （比方也说使用 SELECT … FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。
写操作
- DELETE：
对一条记录做delete操作就是现在B+tree下面定位到记录的位置，在获取这条记录的X锁，在执行delete Mark操作 - UPDATE:
- 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在 B+ 树中位到这条记录的位置，然后再获取一下记录的 X锁 ，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在 B+ 树中位置的过程看成是一个获取 X锁 的 锁定读 。
- 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+ 树中定位到这条记录的位置，然后获取一下记录的 X锁 ，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在 B+ 树中位置的过程看成是一个获取 X锁 的 锁定读 ，新插入的记录由 INSERT 操作提供的 隐式锁 进行保护。
- 如果修改了该记录的键值，则相当于在原记录上做 DELETE 操作之后再来一次 INSERT 操作，加锁操作就需要按照 DELETE 和 INSERT 的规则进行了。
表锁（X锁、S锁）
之前所讲的锁都是针对行级锁也叫粒度锁；
所以表锁也称为 多粒度锁 （事务也可以对表进行加锁）
-
给表加S锁：
如果一个事务给表加了 S锁 ，那么：
别的事务可以继续获得该表的S锁
别的事务可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁 -
给表加 X锁 ：
如果一个事务给表加了 X锁 （意味着该事务要独占这个表），那么：
别的事务不可以继续获得该表的S锁
别的事务不可以继续获得该表中的某些记录的S锁
别的事务不可以继续获得该表的X锁
别的事务不可以继续获得该表中的某些记录的X锁
根据以上得出结论：
- 给表加S锁：
需要判断该表里面有没有行级X锁。如果有则需要X锁全部释放才能添加 - 给表加X锁：
需要判断该表有没有事务正在读记录或者是修改记录，也就是说要看表里面有没有S锁或者X锁，如果有则需要它们全部释放才能加上
此时会出现一个问题，那么如何判断表里的每一行有没有这样类似的锁呢，当然可能用便利的方式来：
所以设计了一个叫做==意向锁（ Intention Locks）==的东西
- 意向共享锁，（Intention Shared Lock），简称IS锁。当事务准备给某条记录加上S锁时，需要先在表级别加上一个S锁
- 意向独占锁，（ Intention Exclusive Lock ），简称IX锁。当事务准备给某条记录加上X锁时，需要先在表级别加一个X锁
这个时候就简单了，如果我想要给表加S锁我只需要看表前面是否有有加意向共享锁
如果我想要给表加X锁，我也只需要看表前是否加意向共享锁或者有没有加意向独占锁
总结：
IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的
AUTO-INC锁
系统如何实现自动给AUTO_INCREMENT修饰的列递增赋值的原理：
- 采用AUTO-INC锁，
需要注意一下的是，这个AUTO-INC锁的作用范围只是单个插入语句，插入语句执行完成后，
这个锁就被释放了，跟我们之前介绍的锁在事务结束时释放是不一样的。 - 采用轻量级锁
采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。
InnoDB行级锁
- Recourd Locks（记录锁）：
前面提到的记录锁也就是这种类型，仅仅把一条记录锁上。
LOKC_REC_NOT_GAP这个是官方的类型名称。
同时也分为X锁和S锁作用同上。
- Gap Locks（间隙锁）：
REPEATABLE READ 隔离级别下是可以解救幻读问题，可以用MVCC方法解决，也可以采用加锁方案解决，但是在使用加锁方案解决时那些
幻影记录还不存在，就无法加上record lock所以设计者就提供了一种Gap Locks的锁，LOCK_GAP
- 间隙锁也就是锁的是该记录和前一条记录的范围区间上锁，只要加了间隙锁，那么该区间就无法插入新数据，或者已经存在的数据无法修改
- Infimum 记录，表示该页面中最小的记录。
- Supremum 记录，表示该页面中最大的记录。
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）
什么情况下会产生间隙锁：
- Next-Key Locks ：
这个也就是Record-LOCK + GAP-LOCK
- Insert Intention Locks （插入意向锁）：
- 当插入位置有gap锁时，后面事务想继续插入记录，获得的锁就是插入意向锁，此时事务开始等待
- 只有等该gap锁彻底释放掉，此时插入意向锁就会把锁结构的is_waiting改成false
- 但是此锁并不会阻止别的事务继续获取该记录上的锁
- 隐式锁
- 聚簇索引记录：有一个trx_id隐藏列，记录着最后改动该记录的事务id。其他事务想对该记录添加S锁或X锁，首先看trx_id是否是当前活跃的事务，如果是，就帮助这个事务创建X锁，自己进入等待状态。
- 二级索引记录：本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如果PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复 情景一 的做法。
InnoDB中的锁结构
- 锁所在的事务信息 ：
不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个 锁结构 ，这里就记载着这个 - 事务的信息。
小贴士：
实际上这个所谓的锁所在的事务信息在内存结构中只是一个指针而已，所以不会占用多大内存
空间，通过指针可以找到内存中关于该事务的更多信息，比方说事务id是什么。下边介绍的所谓的
索引信息其实也是一个指针。
索引信息 ：
对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。
表锁／行锁信息 ：
表锁结构 和 行锁结构 在这个位置的内容是不同的：
表锁：
记载着这是对哪个表加的锁，还有其他的一些信息。
行锁：
记载了三个重要的信息：
Space ID ：记录所在表空间。
Page Number ：记录所在页号。
n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比
特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个 n_bits 属
性代表使用了多少比特位。
小贴士：
并不是该页面中有多少记录，n_bits属性的值就是多少。为了让之后在页面中插入了新记
录后也不至于重新分配锁结构，所以n_bits的值一般都比页面中记录条数多一些。
type_mode ：
这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：
锁的模式（ lock_mode ），占用低4位，可选的值如下：
LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁 。
LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁 。
LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁 。
LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁 。
LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁 。
小贴士：
在InnoDB存储引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表级锁的模式，LOCK_S
和LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。
锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁。
LOCK_REC （十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁。
行锁的具体类型（ rec_lock_type ），使用其余的位来表示 。只有在 lock_type 的值为 LOCK_REC 时，
也就是只有在该锁为行级锁时，才会被细分为更多的类型：
LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁 。
LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁 。
LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示 正经记录锁 。
LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向
锁。
其他的类型：还有一些不常用的类型我们就不多说了。
怎么还没看见 is_waiting 属性呢？这主要还是设计 InnoDB 的大叔太抠门了，一个比特位也不想浪
费，所以他们把 is_waiting 属性也放到了 type_mode 这个32位的数字中：
LOCK_WAIT （十进制的 256 ） ：也就是当第9个比特位置为 1 时，表示 is_waiting 为 true ，也
就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为 false ，
也就是当前事务获取锁成功。
其他信息 ：
为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表，为了简化讨论，我们忽略这
部分信息哈～
一堆比特位 ：
如果是 行锁结构 的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性表示
的。我们前边唠叨InnoDB记录结构的时候说过，页面中的每条记录在 记录头信息 中都包含一个 heap_no 属
性，伪记录 Infimum 的 heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no
值就增1。 锁结构 最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，不过为
了编码方便，映射方式有点怪：
元数据锁
- 什么是元数据锁
MDL 全称为 metadata lock，即元数据锁，一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。 - 什么情况下会产生元数据锁
每一条DDL和DML语句都会申请元数据搜，只是DDL申请,MDL写锁，DML申请MDL读锁，因此DML语句和DML语句不互斥，若是一个事务在执行DML语句的时候，另一个事务执行DDL这时候，这个事务就会去获取MDL写锁，而读锁和写锁是互斥的，因此这个事务会进入等待状态，而一旦等待，不但当前操作会阻塞，后续的所有操作也会进入等待 - 如何优化和避免DML锁
- 开启metadata_locks表记录MDL锁。
- 设置参数lock_wait_timeout为较小值，使被阻塞端主动停止。
- 规范使用事务，及时提交事务，避免使用大事务。
- 增强监控告警，及时发现MDL锁。
- DDL操作及备份操作放在业务低峰期执行。
- 少用工具开启事务进行查询，图形化工具要及时关闭。
出自：MySQL是怎样运行的
144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
