MySQL Transaction |
您所在的位置：网站首页 › 查询mysql行锁语句 › MySQL Transaction |
MySQL Transaction
|
数据库中事务，指一组逻辑操作单元，使数据从一种状态变换到另一种状态。 操作全部完成时，数据被保留，一致性可以保持，一部分操作失败时，整个操作全部视为错误，所有被操作数据回退到开始状态，放弃修改。 事务的 ACID 属性：https://dev.mysql.com/doc/refman/8.3/en/mysql-acid.html Atomicity(原子性)：事务中的所有操作，要么全部完成，要么全部不完成，不可分割。 Consistency(一致性)：事务必须使数据库从一个合法性状态变换到另一个 合法性状态。状态和具体业务有关，而不是语法上的状态。 Isolation(隔离性)：事务的执行不能被其它事务干扰，即一个事务内部的操作及使用的数据对并发的其它事务是隔离的，并发执行的各个事务间不能互相干扰。 Durability(持久性)：事务一旦被提交，它对数据库中数据的改变是永久性的，接下来的其它操作和数据库故障等不应对其有任何影响。事务的状态分为：active(活动的)、partially committed(部分提交的)、failed(失败的)、aborted(中止的)、committed(提交的)
 
-- completion=0，默认。执行 COMMIT 时会提交事务，在执行下一个事务时，还需使用 START TRANSACTION 或 BEGIN。
-- completion=1，相当于 COMMIT AND CHAIN，也就是开启链式事务，当提交事务后会开启一个相同隔离级别的事务。
-- completion=2，相当于 COMMIT AND RELEASE，提交事务后会自动与服务器断开连接。
SET @@completion_type = 0;
-- 开启一个事务
BEGIN;
START TRANSACTION;
-- START TRANSACTION 相较于 BEGIN 特别之处在于，后边能接修饰符(默认 READ WRITE)
-- READ ONLY：标识当前事务是一个 只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
-- READ WRITE：标识当前事务是一个 读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
-- WITH CONSISTENT SNAPSHOT：启动一致性读。
-- !!!!! 一系列事务中的操作（主要是 DML，不含 DDL） !!!!!
-- 提交事务。当提交事务后，对数据库的修改是永久性的。
COMMIT;
-- 回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;
-- 将事务回滚到某个保存点。
ROLLBACK TO 保存点名称;
-- 在事务中新建保存点
SAVEPOINT 保存点名称;
-- 删除某个保存点
RELEASE SAVEPOINT 保存点名称;
View Code
隐式事务
SHOW VARIABLES LIKE 'autocommit';
-- 关闭自动提交
SET autocommit = OFF;
SET autocommit = 0;
-- 设置 autocommit=0 时，不论是否用 START TRANSACTION 或 BEGIN 来开启事务，都需要 COMMIT 提交，让事务生效，使用 ROLLBACK 回滚。
-- 设置 autocommit=1 时，每条 SQL 都会自动提交。如果用 START TRANSACTION 或 BEGIN 显式开启事务，那么这个事务只在 COMMIT 时才会生效，在 ROLLBACK 时才回滚。
-- 以下语句会自动提交事务
-- CREATE、ALTER、DROP、GRANT、RENAME USER、REVOKE、SET PASSWORD
-- LOCK TABLES、UNLOCK TABLES
-- LOAD DATE
-- 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
-- autocommit 由 OFF 变为 ON 和 BEGIN 会自动提交上一个未提交的事务
View Code
二、隔离级别 MySQL 是 C/S 架构，同一 Server 可以有多个 Client 连接，一个 Client 连接可以有多个 Session。每个 Session 都可以向 Server 发 SQL，SQL 可能是某个事务的一部分，也就是 Server 可能同时处理多个事务。 事务有隔离性，理论上某个事务对某个数据进行访问时，其它事务应该排队，等该事务提交后，其它事务才可继续访问。但这样对性能影响太大，想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高，就需要在隔离性和并发性之间权衡取舍。 数据并发问题看一下访问相同数据的事务在不保证串行执行(也就是执行完一个再执行另一个)的情况下可能会出现的问题。 Dirty Write(脏写)对于事务 A、B，A 修改了另一个未提交事务 B 修改过的数据，这种现象称为脏写。 Dirty Read(脏读)对于事务 A、B，A 读取了已经被 B 更新但还没有被提交的字段。之后若 B 回滚，A 读取的内容就是临时且无效的。 例如：B 事务先将 studentno 列为 1 的记录的 name 列更新为'张三'，然后 A 事务再去查询这条 student_no 为 1 的记录，如果读到列 name 的值为'张三'，而 B 事务稍后进行了回滚，那么 A 事务相当于读到了一个不存在的数据。这种现象称为脏读 。 Non-Repeatable Read(不可重复读)对于事务 A、B，A 读取了一个字段，然后 B 更新了该字段。之后 A 再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。 例如：在 Session B 中提交了几个隐式事务(语句结束事务就提交了)，这些事务都修改了 student_no 列为 1 的记录的 name 列的值，每次事务提交后，如果 Session A 中的事务都可以查看到最新的值，这种现象称为不可重复读。 Phantom(幻读)对于事务 A、B，A 从表中读取了一个字段，然后 B 在该表中插入了一些新的行。之后，如果 A 再次读取同一个表，就会多出几行。那就意味着发生了幻读。 例如：A 根据条件 student_no > 0 查询表 student，得到了 name 列值为'张三'的记录。B 向 student 表中插入了新记录并提交。之后 A 再根据相同条件查询，得到的结果集中包含 B 新插入的记录，这种现象称为幻读。新插入的记录称为幻影记录 。 四种隔离级别上面的问题可以按严重性排序：脏写 > 脏读 > 不可重复读 > 幻读。 舍弃部分隔离性换取部分性能：设立一些隔离级别，隔离级别越低，并发问题发生的就越多。SQL 标准(ANSI SQL92)中设立了 4 个隔离级别： READ UNCOMMITTED(读未提交)：事务可以看到其它未提交事务的执行结果。不能避免脏读、不可重复读、幻读。 READ COMMITTED(读已提交)：事务只能看到已经提交事务所做的改变。可避免脏读，但不可重复读、幻读问题仍然存在。是大多数据库系统的默认隔离级别(但不是 MySQL 的) REPEATABLE READ(可重复读，MySQL 默认)：事务 A 读到一条数据，事务 B 对该数据修改并提交，事务 A 再读该数据，读到的还是原来的内容。可避免脏读、不可重复读，但幻读仍存在。 SERIALIZABLE(可串行化)：事务 A 持续期间，禁止其它事务对事务 A 所用到的数据进行操作。所有的并发问题都可避免，但性能低。能避免脏读、不可重复读和幻读。SQL 标准中规定，针对不同隔离级别，并发事务可以发生不同严重程度的问题： 事务隔离级别脏读可能性 不可重复读可能性幻读(虚读)可能性加锁读 READ UNCOMMITTED(读未提交) 是 是 是 否 READ COMMITTED(读已提交) 否 是 是 否 REPEATABLE READ(可重复读) 否 否 是 否 SERIALIZABLE(可串行化) 否 否 否 是脏写没涉及到是因为脏写问题太严重，无论哪种隔离级别都不允许脏写情况发生。 不同的隔离级别有不同的现象，并有不同的锁和并发机制，隔离级别越高，数据库的并发性能就越差：
-- 查看
SHOW VARIABLES LIKE 'tx_isolation';
SHOW VARIABLES LIKE 'transaction_isolation';
SELECT @@transaction_isolation;
-- 修改
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
-- 其中，隔离级别格式：
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ
-- SERIALIZABLE
View Code
http://mysql.taobao.org/monthly/2018/10/06 三、Redo Log(重做日志) Redo Log 用于保证事务的持久性。提供再写入(保证幂等)操作和恢复提交事务修改页操作。 是存储引擎层(innodb)生成的日志，记录物理操作，例如：页号 xxx，偏移量 yyy，写入了 zzz 数据。主要为了保证数据的可靠性。 InnoDB 以页为单位来管理存储空间。在访问页之前，需要把磁盘上的页缓存到内存(BufferPool)中。所有变更都必须先更新内存(BufferPool)中的数据，然后内存(BufferPool)中的脏页会以一定的频率被刷入磁盘(checkpoint)。
-- redo log 在内存中的大小默认 16M ，最大值是 4096M，最小值 1M。
SHOW VARIABLES LIKE '%innodb_log_buffer_size%';
-- redo log 文件组所在的路径，默认 ./，表示在数据库的数据目录下。
-- MySQL 的默认数据目录(var/lib/mysql)下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中。
-- 此 redo 日志文件位置可以修改。
SHOW VARIABLES LIKE 'innodb_log_group_home_dir';
-- redo log file 的个数，命名方式如：ib_logfile0，iblogfile1...iblogfilen。默认 2 个，最大 100 个。
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
-- redo log 刷新到磁盘的策略，默认 1。
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 单个 redo log 文件大小，默认 48M 。最大 512G。
-- 最大 512G 指的是整个 redo log 系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于 512G。
SHOW VARIABLES LIKE 'innodb_log_file_size';
View Code
Redo Log 的存储(物理)结构
从逻辑上看，redo log 记录是连续递增的，由多个不同 Type 的多个 redo 记录收尾相连组成，有全局唯一的递增的偏移 sn，InnoDB 会在全局 log_sys 中维护当前 sn 的最大值，并在每次写入数据时将 sn 增加 redo 内容长度。 从物理文件上看，redo log 被设计成多个可循环写入的文件。InnoDB 要求 redo log 文件至少有 2 个，初始为 ib_logfile0 和 ib_logfile1， ib_logfile0 写完后写 ib_logfile1，等到 ib_logfile1 也写完了，从头又开始写 ib_logfile0，形成环形写入结构。 覆盖写入的前提是要确定哪个位置点是可以覆盖写的，哪些位置是不能覆盖写的，这个是 checkpoint 的工作。 Log File
从 ib_logfile0 和 ib_logfile1 的物理结构可以看出，文件以 Block 为单位划分，每个文件的开头固定预留 4 个 Block 来记录一些额外的信息，其中第一个 Block 称为 Header Block，之后的 3 个 Block 在 0 号文件上用来存储 Checkpoint 信息，而在其它文件上留空。 Log File Header Block
在 ib_logfile0 中有两个 checkpoint block：LOG_CHECKPOINT_1 和 LOG_CHECKPOINT_2，是因为 redo log 被设计为交替写入，避免因为介质失败而导致无法找到可用的 checkpoint 的情况。 LSN 几乎是 redo 中最重要的概念之一了，LSN 表示 redo 的写入量，标识了 checkpoint 的位置，标识了 page 的版本。 LSN 不仅存在于 redo log 中，还存在于每个 page 中。在每个 page 的头部，有一个 FIL_PAGE_LSN，记录了 page 的 LSN，表示该页最后刷新时的 LSN 大小。 redo 中记录的是每个 page 的日志，因此 page 中的 LSN 用来判断是否需要进行恢复操作，这对于 MySQL 的崩溃恢复及其重要。 Block
每个 Block 是 512(OS_FILE_LOG_BLOCK_SIZE) 字节，对应到磁盘每个扇区也是 512 字节，因此 redo log 写磁盘是原子写，保证能够写成功，而不像 index page 一样需要 double write 来保证安全写入。 除了 redo 数据以外，Block 中还需一些额外的信息： Block Header：前 4 字节中 Flush Flag 占用最高位 bit，标识一次 IO 的第一个 Block，剩下 31 个 bit 是 Block 编号。Data Length：取值在[12，508]First Record Offset：用来指向 Block 中第一个 REDO 组的开始，这个值的存在使得我们对任何一个 Block 都可以找到一个合法的 REDO 开始位置。Checkpoint Number：记录写 Block 时的 next_checkpoint_number，用来发现文件的循环使用。中间剩余的 496 字节：存放 redo log 数据。可能一个 Block 中有多个 redo log，也可能一个 redo log 被拆分到多个 Block 中。Checksum：通过这个值，读取 Log 时可以明确 Block 数据有没有被完整写盘。 Log Record
redo 记录，分为 header 和 body： redo_log_type：重做日志的类型space ID：表空间IDPage Numer：用于定位哪个 page redo_log_type 有 MLOG_WRITE_1BYTE、MLOG_WRITE_2BYTE、MLOG_WRITE_4BYTE 和 MLOG_WRITE_STRING 等，其 header 格式如下：
一条完整的 INSERT redo record 结构：
redo log 的写入并不是直接写入磁盘，InnoDB 引擎会在写 redo log 的时候先写 redo log buffer，之后以一定频率写入到 redo log file。这里的一定频率就是指刷盘策略。 注意，redo log buffer 刷盘到 redo log file 的过程并不是真正的刷到磁盘中去，只是刷入到文件系统缓存(page cache，这是现代操作系统为了提高文件写入效率做的一个优化)，真正的写入会交给操作系统决定(例如 page cache 足够大了)。 那么对于 InnoDB 来说就存在一个问题，如果交给系统来同步，同样，如果系统宕机，那么数据也就丢失了(虽然整个系统宕机的概率还是比较小的)。 针对这种情况，InnoDB 给出 innodb_flush_log_at_trx_commit 参数，控制提交事务时如何将 redo log buffer 中的日志刷新到 redo log file 中。支持三种策略： 0：每次事务提交时不进行刷盘操作，也就是数据只在 redo log buffer 中。但 InnoDB 存储引擎有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的内容写到文件系统缓存(page cache)，然后调用刷盘(fsync)操作。 1(默认)：每次事务提交时都主动进行同步(刷盘操作)，也就是直接把数据写入到文件中去。 2：每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步，由 OS 或后台线程去同步(fsync)到磁盘文件。相比于 0 在于，MySQL 挂了但 OS 没挂时，数据就还在。而 0 只要 MySQL 挂了数据就没了。 Write Ahead Log(预先日志持久化)在持久化一个数据页之前，先将内存中相应的日志页持久化。Redo Log File 的写入是顺序(速度快) IO。 写入 redo log buffer 的过程Mini-Transaction：一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo 日志。 四、Undo Log(回滚(撤销)日志) Undo Log 用于保证事务的原子性(事务回滚，崩溃恢复)和隔离性(MVCC)。回滚行记录到某个特定版本。事务中更新数据的前置操作其实是要先写入一个 undo log，即：undo log(记录旧数据) -> redo log(写入新数据到记录) -> xxx.ibd(记录被正确完整写入后才写入到表中) 是存储引擎层(innodb)生成的日志，记录逻辑操作，例如：进行了 INSERT 操作，那 undo log 就记录一条与之相反的 DELETE 操作。主要用于事务的回滚(undo log 记录的是每个修改操作的逆操作)和一致性非锁定读(undo log 回滚行记录到某个特定的版本(MVCC，多版本并发控制))。
在 MySQL5.6 之前，undo log 全部存储在 system space(ibdata1) 中，从 5.6 开始也可以使用 undo space(undo_xxx) 存储。 system space 的第六个页(page 5，页类型为 FSP_TRX_SYS_PAGE_NO)记录了事务系统信息，其中包括最多 128(TRX_SYS_N_RSEGS) 个 rollback segment：
MySQL 8 中 InnoDB 默认有 2(最大 127) 个 undo tablespace。创建额外的 undo tablespace 有助于防止单个 undo tablespace 变得过大。
SELECT T1.SPACE AS SPACE_ID,
T1.NAME AS TABLESPACE_NAME,
T2.FILE_NAME,
ROUND(T2.INITIAL_SIZE / 1024 / 1024, 2) AS "INITIAL_SIZE(M)",
ROUND(T2.AUTOEXTEND_SIZE / 1024 / 1024, 2) AS "AUTOEXTEND_SIZE(M)",
ROUND(T1.FILE_SIZE / 1024 / 1024, 2) AS "FILE_SIZE_DISK(M)",
ROUND(T2.DATA_FREE / 1024 / 1024, 2) AS "DATA_FREE(M)",
T2.STATUS,
T1.STATE
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES T1,
INFORMATION_SCHEMA.FILES T2
WHERE T1.SPACE = T2.FILE_ID;
-- innodb_undo_directory：设置 rollback segment 文件所在的路径。这意味看 rollback segment 可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为 .，表示当前 InnoDB 存诸引擎的目录。
-- innodb_undo_tablespaces：设置构成 rollback segment 文件的数量，这样 rollback segment 可以较为平均地分布在多个文件中。设置该参数后，会在路径 innodb_undo_directory 看到 undo 为前缀的文件，该文件就代表 rollback segment文件。
SHOW VARIABLES LIKE '%undo%';
View Code
Rollback Segment
InnoDB 在 undo tablespace 中使用 rollback segment 来组织 undo log。同时为了保证事务的并发操作，在写 undo log 时不产生冲突，InnoDB 使用 rollback segment 来维护 undo log 的并发写入和持久化。 每个 rollback segment 记录了 1024(TRX_RSEG_UNDO_SLOTS) 个 rollback segment slot，每个 rollback segment slot 对应一个 undo 页链表。
Undo Segment Slot：记录活跃事务的 Undo 页链表第一页的页号。Slot 如果被占用，则将 Undo 页链表段第一个页的 page no 填入，否则填入 FIL_NULL。 Rollback Segment Header通常通过 Rollback Segment Header 来管理 Rollback Segment，Rollback Segment Header 通常在 Rollback Segment 的第一个页。
TRX_RSEG_MAX_SIZE：回滚段允许的最大大小(以页为单位)。4 字节最大值 0xFFFFFFFF，InnoDB 页默认 16KB，即每个 rollback segment 能持有 64T 的数据。TRX_RSEG_HISTORY_SIZE：History 链表的长度。TRX_RSEG_HISTORY：History 链表的头节点。history list 把所有已提交但还没被 purge 事务的 undo log 串联起来，purge 线程可以通过此 list 对没有事务使用的 undo log 进行 purge。TRX_RSEG_FSEG_HEADER：当前 rollback segment 对应的 segment 信息。包括 space id、page no、offset，可以唯一定位一个 INODE Entry。 每个事务在需要记录 undo log 时都会申请一个或两个 slot(insert/update分开)，同时把事务的第一个 undo page 放入对应 slot 中。所以理论上 InnoDB 允许的最大事务并发数为 128(undo tablespace) * 128(Rollback Segment) * 1024(TRX_RSEG_UNDO_SLOTS)。 Undo Page每个 Undo Segment Slot 对应一个页类型为 FIL_PAGE_UNDO_LOG 的 Undo 页链表。Undo 页结构：
undo header page 是事务需要写 undo log 时申请的第一个 undo page，一个 undo header page 同一时刻只隶属于同一个活跃事务，但是一个 undo header page 上的内容可能包含多个已经提交的事务和一个活跃事务。 undo normal page 是当活跃事务产生的 undo record 超过 undo header page 容量后，单独再为此事务分配的 undo page。此 page 只隶属于一个事务，只包含 undo page header 不包含 undo segment header。 Undo Page Header
TRX_UNDO_PAGE_TYPE：Undo 页还会细分：TRX_UNDO_INSERT、TRX_UNDO_UPDATE。TRX_UNDO_PAGE_START：表示在当前页中是从什么位置开始存储 Undo Record 的，或者说表示第一条 Undo Record 在本页中的起始偏移量。TRX_UNDO_PAGE_FREE：与 TRX_UNDO_PAGE_START 对应，表示当前页中存储的最后一条 Undo Record 结束时的偏移量，或者说从这个位置开始，可以继续写入新的 Undo Record。TRX_UNDO_PAGE_NODE：一个 List Node 结构。通过该 Node 将 Undo 页连成双向链表。 Undo Segment Header
同一事务向 Undo 页链表中写入的 Undo Record 算一个组，每组 Undo Record 前(Undo Log Header)会保存关于该组的一些属性。所以 Undo 页链表的第一页会被填充 Undo Page Header、Undo Segment Header、Undo Log Header。 TRX_UNDO_TRX_ID：生成本组 undo 日志的事务 id。TRX_UNDO_TRX_NO：事务提交后生成的一个需要序号，使用此序号来标记事务的提交顺序（先提交的此序号小，后提交的此序号大）。TRX_UNDO_DEL_MARKS：标记本组 undo 日志中是否包含由于 Delete mark 操作产生的 undo 日志。TRX_UNDO_LOG_START：表示本组 undo 日志中第一条 undo 日志在页面中的偏移量。TRX_UNDO_XID_EXISTS：本组 undo 日志是否包含 XID 信息。TRX_UNDO_DICT_TRANS：标记本组 undo 日志是不是由 DDL 语句产生的。TRX_UNDO_TABLE_ID：如果 TRX_UNDO_DICT_TRANS 为真，那么本属性表示 DDL 语句操作的表的 table id。TRX_UNDO_NEXT_LOG：下一组 undo 日志在页面中的开始偏移量。TRX_UNDO_PREV_LOG：上一组 undo 日志在页面中的开始偏移量。TRX_UNDO_HISTORY_NODE：一个 List Nod结构，代表一个称之为 History 链表的节点。 当 update/delete 事务结束后，undo log header 将会被加入到 hisotry list 中。insert 事务的 undo log 会被立即释放。有了 undo log header 后就可以记录 undo record 了。 Undo Record
五、Lock 并发事务访问相同记录的几种情况：读-读、写-写、读-写或写-读。会产生的几种问题在上面隔离级别已经提到了。解决方法有两种：读操作利用多版本并发控制(MVCC)，写操作进行加锁。或者读写都加锁。 从数据操作的类型划分：读锁、写锁对于数据库中开发事务的读-读情况不会引起么可题。对于写-写、读-写或写-读这些情况可能会引起一些可题，需要使用 MVCC 或者加锁的方式来解决。 在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以 MySQL 实现一个由两种类型的锁组成的锁系统来解决。 这两种类型的锁通常被称为共享锁(SharedLock，SLock)和排他锁(ExclusiveLock，XLock)，也叫读锁(readlock)和写锁(writelock）。 读锁(共享锁)：用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。 写锁(排他锁)：用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
BEGIN;
-- 写锁
SELECT * FROM mysql.user LOCK IN SHARE MODE;
COMMIT;
BEGIN;
-- 读锁
SELECT * FROM mysql.user FOR UPDATE;
COMMIT;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 在 5.7 及之前的版本，SELECT...FOR UPDATE，如果获取不到锁，会一直等待，直到 innodb_lock_wait_timeout 超时。
-- 在 8.0 版本中，SELECT...FOR UPDATE，SELECT...FOR SHARE 添加 NOWAIT、SKIP LOCKED 语法，跳过锁等待，或者跳过锁定。
-- 通过添加 NOWAIT、SKIP LOCKED 语法，能够立即返回。如果查询的行已经加锁：
-- 那么 NOWAIT 会立即报错返回
-- 而 SKIP LOCKED 也会立即返回，只是返回的结果中不包含被锁定的行
View Code
从数据操作的粒度划分
表锁
1、Shared and Exclusive Locks(读锁、写锁)
SHOW STATUS LIKE '%lock%';
-- Innodb_row_lock_current_waits：当前正在等待锁定的数量
-- Innodb_row_lock_time：从系统启动到现在锁定总时间长度（等待总时长）
-- Innodb_row_lock_time_avg：每次等待所花平均时间（等待平均时长）
-- Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间
-- Innodb_row_lock_waits：系统启动后到现在总共等待的次数（等待总次数）
-- 查看当前有那些表是打开的
SHOW OPEN TABLES;
-- In_use：有多少线程正在使用某张表
SHOW OPEN TABLES WHERE In_use > 0;
-- 解锁
-- 显示连接列表(查询 ID)
SHOW FULL PROCESSLIST;
-- 结束指定(ID)连接
KILL ID;
-- 释放当前会话所有表锁
UNLOCK TABLES;
-- 加锁
SET autocommit = 0;
SET innodb_table_locks = 1;
-- 指定表加锁
LOCK TABLES mysql.user READ, mysql.user WRITE;
-- 所有表加锁
FLUSH TABLES WITH READ LOCK;
View Code
2、Intention Locks(意向锁) 事务 T1、T2，T2 试图在表级别上应用共享或排它锁： 无意向锁：T2 需要去检查各个页或行是否存在锁。无则加锁，有则阻塞。 有意向锁：T2 在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。无则加锁，有则阻塞。简单说，当给某行数据加锁时，InooDB 会自动给更大一级(数据页或数据表)加相应(读或写)的意向锁。 意向锁的存在是为了协调行锁与表锁的关系，支持多粒度(表锁与行锁)的锁并存。 意向读锁和意向写锁时可以共存的。 3、AUTO-INC Locks(自增锁) 插入数据的方式可分为三类： Simple inserts(简单插入)：可预先确定要插入的行数 Bulk inserts(批量插入)：事先不知道要插入的行数 Mixed-mode inserts(混合模式插入)：部分指定自增主键，部分不指定
-- 传统锁定模式
SET innodb_autoinc_lock_mode = 0;
-- 在此锁定模式下，每当具有 AUTO_INCREMENT 列的表执行 insert 时，都会得到一个表级锁(AUTO-INC 锁)，使得语句中生成的 auto_increment 为顺序，
-- 且在 binlog 中重放的时候，可以保证 master 与 slave 中数据的 auto_increment 是相同的。
-- 因为是表级锁，当在同一时间多个事务中执行 insert 的时候，对于 AUTO-INC 锁的争夺会限制并发能力。
-- 连续锁定模式
SET innodb_autoinc_lock_mode = 1;
-- 在 MySQL 8.0 之前，默认是连续锁定模式。
-- 在此锁定模式下，bulk inserts 仍使用 AUTO-INC 表级锁，并保持到语句结束。这适用于所有 INSERT ... SELECT，REPLACE ... SELECT 和 LOAD DATA。同一时刻只有一个语句可持有 AUTO-INC 锁。
-- 对于 simple inserts，则通过 mutex(轻量锁) 获得所需数量的自动递增值来避免 AUTO-INC 表级锁，它只在分配过程的持续时间内保持，而不是直到语句完成。如果另一个事务保持 AUTO-INC 锁，则 simple inserts 需等待，如同它是一个 bulk inserts。
-- 交错锁定模式
SET innodb_autoinc_lock_mode = 2;
-- 从 MySQL 8.0 开始，默认是交错锁模式。
-- 在此锁定模式下，自动递增值保证在所有并发执行的所有类型的 insert 语句中是唯一且单调递增的。
-- 但是，由于多个语句可同时生成数字(跨语句交叉编号)，所以给插入语句生成的值可能不是连续的。
View Code
4、Metadata Locking(元数据锁) MySQL 5.5 引入了 meta data lock，简称 MDL 锁，属于表锁范畴。 MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 因此，当对一个表做增删改查操作的时候，加 MDL 读锁。当要对表做结构变更操作的时候，加 MDL 写锁。 行锁1、Record Locks(记录锁) 官方类型名称：LOCK_REC_NOT_GAP，把一条记录锁上。 2、Gap Locks(间隙锁) MySQL 在 REPEATABLE READ 隔离级别下解决幻读问题的方案有两种，使用 MVCC 方案解决，或采用加锁。 但是在使用加锁方案解决时有个问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。 InnoDB 提出了Gap Locks，官方类型名称：LOCK_GAP，简称 Gap 锁。 例如表中有 infimum、8、19、27、supremum 三条记录。这时查询主键为 30 的记录就会在 (27, supremum) 加 Gap 锁，同一时间另一个事务插入主键为 100 的记录就会被阻塞。 3、Next-Key Locks(临键锁) 有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录。 InnoDB 提出了 Next-Key Locks，官方类型名称：LOCK_ORDINARY，简称 next-key 锁。Next-Key Locks 是 innodb 存储引擎在事务级别为可重复读的情况下使用的数据库锁。 例如表中有 infimum、8、19、27、supremum 三条记录。这时查询主键 8 的记录就会在 (8, 19] 加 next-key 锁(相当于 (8, 19) 为 Gap Locks，19 为 Record Locks)，同一时间另一个事务就无法操作主键在 (8, 19] 的记录。 简单理解 Next-Key Locks 就是 Record Locks 和 Gap Locks 的组合。 4、Insert Intention Locks(插入意向锁) 事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap 锁(next-key 锁也包含 gap 锁)，如果有的话，插入操作需要等待，直到拥有 gap 锁的那个事务提交。 InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。 InnoDB 把这种类型的锁命名为 Insert Intention Locks，官方类型名称：LOCK_INSERT_INTENTION，是一种 Gap 锁，不是意向锁，在 insert 操作时产生。 插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。插入意向锁不会阻止其它事务获取该记录上任何类型的锁。 页锁页锁锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。所以当使用页锁的时候，可能会出现数据浪费的现象(最多浪费一个页上的数据行)。 页锁的粒度和开销都介于表锁和行锁之间，并发度一般，会出现死锁。 每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间的大小是有限的。当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。 锁升级就是用大粒度的锁替代多个小粒度的锁，例如行锁升级为表锁，好处是锁空间的占用降低了，但数据的并发度也降低了。 从对待锁的态度划分态度指看待数据并发的思维方式。乐观锁和悲观锁并不是锁，是锁的设计思想。 Pessimistic Locking(悲观锁)悲观锁总是假设最坏的情况，每次拿数据时都认为别人会修改，所以每次拿数据时都会上锁，这样别人想拿这个数据就会阻塞直到拿到锁。 比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其它线程想要访问数据时，都需要阻塞挂起。 Java 中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想。 Optimistic Locking(乐观锁)乐观锁认为对同一数据的并发操作属于小概率事件，不用上锁，但在更新的时候会判断在此期间有没有数据被更新过。也就是不采用数据库自身的锁机制，而是通过程序实现。 在程序上，可以采用版本号机制或者 CAS 机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。 1、乐观锁的版本号机制：在表中新增字段 version，读的时候，获取 version 字段值。更新或删除时，执行 UPDATE ... SET version = version + 1 WHERE version = version。若期间该数据被更改过，操作就不会成功。 2、乐观锁的时间戳机制：和版本号机制一样，也是在更新时，将当前数据的时间戳和更新前取得的时间戳比较，如果一致则更新成功，否则就是版本冲突。 可以看到乐观锁就是自己控制数据并发操作，基本是通过给数据行增加一个字段(版本号或者时间戳)，从而证明当前拿到的数据是最新的。 Java 中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS。 两种锁的适用场景乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其它事务对该数据的操作权限，防止读-写和写-写的冲突。 按加锁的方式划分 隐式锁对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的事务id。如果当前事务插入一条聚簇索引记录后，该记录的 trx_id 代表的就是当前事务的 id，其它事务此时想对该记录添加读锁或写锁时，会看下该记录的 trx_id 代表的事务是否是活跃事务，如果是的话，那么就帮助创建一个写锁(创建一个锁结构，is_waiting 属性是 false)，然后自己进入等待状态(为自己也创建一个锁结构，is_waiting 属性是 true)。 对于二级索引记录来说，虽然没有 trx_id 隐藏列，但在二级索引页的 Page Header 有 PAGE_MAX_TRX_ID 属性，该属性代表对该页做改动的最大事务 id，如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务 id，那么说明对该页做修改的事务已经提交了，否则就需要在页中定位到对应的二级索引记录，然后回表找到对应的聚簇索引记录，然后再重复聚簇索引中的做法。 即：一个事务对新插入的记录可以不显式的加锁(生成一个锁结构)，但是由于事务 id 的存在，相当于加了一个隐式锁。别的事务在对这条记录加读锁或者写锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。 隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式锁转化为显式锁。 InnoDB 的 insert 操作，对插入的记录不加锁，但此时如果另一个线程进行读，类似以下的用例，session2 会锁等待 session1，这是如何实现的呢？
-- session 1
BEGIN;
INSERT INTO student VALUES(1, '1');
-- session 2
BEGIN;
SELECT * FROM student LOCK IN SHARE MODE; -- 执行完，当前事务会被阻塞
-- session 3
SELECT * FROM performance_schema.data_lock_waits; -- 会看到 session 1 的隐式锁
View Code
显式锁
通过特定的语句进行加锁，例如显示加共享锁：select .... lock in share mode，显示加排它锁：select .... for update。 全局锁全局锁就是对整个数据库实例加锁。 当需要让整个库处于只读状态时，可以使用这个命令，之后其它线程的某些语句会被阻塞：数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。 全局锁的典型使用场景是做全库逻辑备份。全局锁命令：FLUSH TABLES WITH READ LOCK 死锁死锁指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。出现死锁后，有两种策略 ： 直接进入等待，直到超时。超时时间可通过参数 innodb_lock_wait_timeout 设置。 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务(将undo量最小的事务进行回滚)，让其它事务得以继续执行。将参数 innodb_deadlock_detect 设为 on 表示开启这个逻辑。死锁的检测会增加额外的开销。应尽量避免死锁： 合理设计索引，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。 调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更小。 在并发比较高的系统中，不要显式加锁，特别在事务里显式加锁。如 select ... for update 语句，如果是在事务里运行了 start transaction 或设置了 autocommit 等于 0，那么就会锁定所查找到的记录。 降低隔离级别。如果业务充许，将隔离级别调低也是较好的选择，比如将隔离级别从 RR 调整为 RC，可以避免掉很多因为 Gap 锁造成的死锁。 锁的内存结构锁的内存结构由 struct lock_t 定义，大致包括： trx：锁所在的事务信息的指针。不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记载着指向这个事务的信息的指针。 index：索引信息。对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。 tab_lock/rec_lock：表锁/行锁信息。表锁结构和行锁结构在这个位置的内容是不同的。 表锁：主要记录对哪个表加的锁。 行锁：主要记录：Space ID(记录所在表空间)、Page Number(记录所在页号)、n_bits(一条记录对应 1-bit，一个页包含很多记录，用 bit 区分是哪一条记录加了锁。为此在行锁结构末尾放一堆比特位，这个属性代表使用了多少比特位)type_mode：一个 uint32_t，被分成了三部分 lock_mode(锁的模式)：占用低 4 位，可选的值：LOCK_IS(共享意向锁，表锁)、LOCK_IX(独占意向锁，表锁)、LOCK_S(共享锁，表/行锁)、LOCK_X(独占锁，表/行锁)、LOCK_AUTO_INC(AUTO-INC 锁，表锁)。 lock_type(锁的类型)：占用第 5-8 位，现阶段只有第 5 位和第 6 位被使用：LOCK_TABLE，当第 5 个比特位为 1 表示表级锁。LOCK_REC，当第 6 个比特位为 1 表示行级锁。 rec_lock_type(行锁的具体类型)：只有在 lock_type 为 LOCK_REC 时，才细分更多类型：LOCK_ORDINARY(表示next-key 锁)、LOCK_GAP(第10个比特位为1时，表示gap锁)、LOCK_REC_NOT_GAP(第11个比特位为1时，表示记录锁)、LOCK_INSERT_INTENTION(第12个比特位为1时，表示插入意向锁)。。。 关于 is_waiting：LOCK_WAIT：第 9 个比特位为 1 时，表示 is_waiting 为 true，当前事务尚未获取到锁，处在等待状态。相应为 0 时，表示 is_waiting 为 false，当前事务获取锁成功。hash：是 Inodb 中构造 Hash 表需要，当锁插入到 Lock_sys->hash(所有锁都保存在 Lock_sys->hash 哈希表中。无论是表锁还是行锁，都是用结构 lock_t 描述) 中，Hash 值相同就形成链表，使用变量 hash 相连。 如果是行锁结构，在 struct lock_t 末尾会放置了一堆比特位，数量由 n_bits 表示： n_bits 的值一般比页中记录条数大(由 size_t lock_size(const page_t *page) 方法计算)。以免之后在页中插入新记录重新分配锁结构。 InnoDB 数据页中的每条记录在记录头中都包含一个 heap_no 属性。 伪记录 Infimum 的 heap_no 值为 0，Supremum 的 heap_no 值为 1，之后每插入一条记录，heap_no 值就增 1。锁结构最后的一堆比特位对应一个页中的记录，一个比特位映射一个 heap_no，即一个比特位映射到页内的一条记录。 锁的监控
-- 查看当前所有事务
SELECT * FROM information_schema.innodb_trx;
-- 查看正在锁的事务
SELECT * FROM performance_schema.data_locks;
-- SELECT * FROM information_schema.innodb_locks;
-- 查看等待锁的事务
SELECT * FROM performance_schema.data_lock_waits;
-- SELECT * FROM information_schema.innodb_lock_waits;
-- 查看当前连接
SHOW STATUS LIKE 'Threads%';
-- Threads_connected：打开的连接数
-- Threads_created：表示创建过的线程数
-- Threads_running：激活的连接数(并发数，一般远低于 connected)
-- 如果 Threads_created 值过大的话，表明 MySQL 服务器一直在创建线程，可以适当增加 thread_cache_size 值
SHOW VARIABLES LIKE 'thread_cache_size';
-- 查询最大连接数
SHOW VARIABLES LIKE '%max_connections%';
-- 设置最大连接数
SET GLOBAL max_connections=1000;
-- 在 /etc/my.cnf 里设置数据库最大连接数
-- [mysqld]
-- max_connections = 1000
-- 显示连接状态，可以把 connect 换成其它参数
SHOW STATUS LIKE '%connect%';
-- Aborted_clients：由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
-- Aborted_connects：尝试已经失败的 MySQL 服务器的连接的次数。
-- Connections：试图连接 MySQL 服务器的次数。
-- Created_tmp_tables：当执行语句时，已经被创造了的隐含临时表的数量。
-- Delayed_insert_threads：正在使用的延迟插入处理器线程的数量。
-- Delayed_writes：用 INSERT：DELAYED 写入的行数。
-- Delayed_errors：用 INSERT：DELAYED 写入的发生某些错误(可能重复键值)的行数。
-- Flush_commands：执行 FLUSH 命令的次数。
-- Handler_delete：请求从一张表中删除行的次数。
-- Handler_read_first：请求读入表中第一行的次数。
-- Handler_read_key：请求数字基于键读行。
-- Handler_read_next：请求读入基于一个键的一行的次数。
-- Handler_read_rnd：请求读入基于一个固定位置的一行的次数。
-- Handler_update：请求更新表中一行的次数。
-- Handler_write：请求向表中插入一行的次数。
-- Key_blocks_used：用于关键字缓存的块的数量。
-- Key_read_requests：请求从缓存读入一个键值的次数。
-- Key_reads：从磁盘物理读入一个键值的次数。
-- Key_write_requests：请求将一个关键字块写入缓存次数。
-- Key_writes：将一个键值块物理写入磁盘的次数。
-- Max_used_connections：同时使用的连接的最大数目。
-- Not_flushed_key_blocks：在键缓存中已经改变但是还没被清空到磁盘上的键块。
-- Not_flushed_delayed_rows：在 INSERT：DELAY 队列中等待写入的行的数量。
-- Open_tables：打开表的数量。
-- Open_files：打开文件的数量。
-- Open_streams：打开流的数量(主要用于日志记载）
-- Opened_tables：已经打开的表的数量。
-- Questions：发往服务器的查询的数量。
-- Slow_queries：要花超过 long_query_time 时间的查询数量。
-- Threads_connected：当前打开的连接的数量。
-- Threads_running：不在睡眠的线程数量。
-- Uptime：服务器工作了多长时间，单位秒。
View Code
六、Multi-Version Concurrency Control(MVCC，多版本并发控制) MVCC 在 InnoDB 中是为了提高并发性能。做到读写冲突时不加锁和非阻塞并发读。这里读是快照读，而非当前读。 不加锁的简单的 SELECT 都属于快照读。加锁的 SELECT 或对数据进行增删改都会进行当前读。 MVCC 的实现本质是采用乐观锁思想，依赖于：隐藏字段、Undo Log、Read View。 隐藏字段InnoDB 中聚簇索引记录都包含必要的隐藏列： DB_TRX_ID(trx_id)：插入或更新记录的最后一个事务的事务标识符。 DB_ROLL_PTR(roll_pointer)：回滚指针指向写入 rollback segment 的 undo log 记录。 Undo Log 版本链每次对记录的改动都会记录一条 undo 日志(该记录的一个旧版本)，每条 undo 日志都有一个 roll_pointer 属性(INSERT 操作对应的 undo 日志没有该属性，因为该记录并没有更早的版本)。随着更新次数的增多，所有的版本会被 roll_pointer 属性连接成一个链表。这个链表称之为版本链，版本链的头节点就是当前记录最新的值。 每个版本中还包含生成该版本时对应的事务 id。 Read View对于使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。 对于使用 SERIALIZABLE 隔离级别的事务，InnoDB 使用加锁的方式来访问记录。 而使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，须保证读到已提交事务修改过的记录。假如一个事务已经修改了记录但尚未提交，另一个事务是不能直接读取最新版本的记录的，核心问题就是要判断版本链中的哪些是当前事务可见的，这是 Read View 要解决的主要问题，也就是行的可见性问题。 Read View 是一个事务在使用 MVCC 机制进行快照读时产生的，不同的事务有各自的 Read View。 Read View 的结构主要包含以下属性： m_low_limit_id：大于等于此值的是未开启的事务，不可见。 m_up_limit_id：小于此值的是已提交事务，可见。 m_creator_trx_id：创建该 ReadView 的事务的 ID，可见。只有对表中的记录做改动时(INSERT、DELETE、UPDATE)才会为事务分配 id，否则在只读事务中的事务 id 默认为 0。 m_ids：创建 Read View 时系统中活跃的读写事务的事务 id 列表，不可见。 m_low_limit_no：Purge 利用该信息清理 Undo Log。READ COMMITTED：每次读取数据前都生成一个 ReadView。
-- Transaction 10
BEGIN;
UPDATE student SET name = 'B' WHERE id = 1;
UPDATE student SET name = 'C' WHERE id = 1;
-- Transaction 20
BEGIN;
-- 更新了一些其它表的记录(好让数据库分配事务ID)
-- 此时 student 表中 id 为 1 的记录的版本链
-- id, name, trx_id, roll_pointer
-- 1, C , 10 , 指向下面一行
-- 1, B , 10 , 指向下面一行
-- 1, A , 5 ,
-- Transaction 30，用 READ COMMITTED 隔离级别执行
BEGIN;
-- SELECT 1：Transaction 10、20 未提交
SELECT * FROM student WHERE id = 1; -- 得到 name 的值为 A
-- Transaction 10 提交
COMMIT;
-- Transaction 20 更新 student 表记录，不提交
UPDATE student SET name = 'E' WHERE id = 1;
UPDATE student SET name = 'F' WHERE id = 1;
-- 此时 student 表中 id 为 1 的记录的版本链
-- id, name, trx_id, roll_pointer
-- 1, F , 20 , 指向下面一行
-- 1, E , 20 , 指向下面一行
-- 1, C , 10 , 指向下面一行
-- 1, B , 10 , 指向下面一行
-- 1, A , 5 ,
-- Transaction 30
-- SELECT 2：Transaction 10 提交，Transaction 20 未提交
SELECT * FROM student WHERE id = 1; -- 得到 name 的值为 C
COMMIT
View Code
REPEATABLE READ：只会在第一次执行查询语句时生成一个 ReadView，之后的查询不会重复生成。
-- Transaction 10
BEGIN;
UPDATE student SET name = 'B' WHERE id = 1;
UPDATE student SET name = 'C' WHERE id = 1;
-- Transaction 20
BEGIN;
-- 更新了一些其它表的记录(好让数据库分配事务ID)
-- 此时 student 表中 id 为 1 的记录的版本链
-- id, name, trx_id, roll_pointer
-- 1, C , 10 , 指向下面一行
-- 1, B , 10 , 指向下面一行
-- 1, A , 5 ,
-- Transaction 30，用 REPEATABLE READ 隔离级别执行
BEGIN;
-- SELECT 1：Transaction 10、20 未提交
SELECT * FROM student WHERE id = 1; -- 得到 name 的值为 A
-- Transaction 10 提交
COMMIT;
-- Transaction 20 更新 student 表记录，不提交
UPDATE student SET name = 'E' WHERE id = 1;
UPDATE student SET name = 'F' WHERE id = 1;
-- 此时 student 表中 id 为 1 的记录的版本链
-- id, name, trx_id, roll_pointer
-- 1, F , 20 , 指向下面一行
-- 1, E , 20 , 指向下面一行
-- 1, C , 10 , 指向下面一行
-- 1, B , 10 , 指向下面一行
-- 1, A , 5 ,
-- Transaction 30
-- SELECT 2：Transaction 10 提交，Transaction 20 未提交
SELECT * FROM student WHERE id = 1; -- 得到 name 的值为 A
COMMIT
View Code
MVCC 下 REPEATABLE READ 不会出现幻读
-- 假设现在 student 表中只有一条数据，它的 undo log 如下
-- id, name, trx_id, roll_pointer
-- 1, C , 10 ,
-- Transaction 10，用 REPEATABLE READ 隔离级别执行
BEGIN;
select * from student where id >= 1;
-- 开始查询前，MySQL 会为 Transaction 10 产生一个 ReadView：trx_ids=[20,30],up_limit_id=20,low_limit_id=31,creator_trx_id=20
-- 由于此时 student 表有一条数据，且符合 where id >= 1，因此会查出来。
-- 然后根据 ReadView 机制，该行数据的 trx_id=10，小于 Transaction 10 的 ReadView 里 up_limit_id，表示这条数据是 Transaction 10 开启之前，其它事务已经提交了的数据，因此事务 A 可以读取到。
-- 结论：Transaction 10 的第一次查询，能读取到一条数据：id=1。
-- Transaction 30
BEGIN;
insert into student(id, name) values (2, 'B');
insert into student(id, name) values (3, 'C');
COMMIT;
-- Transaction 10
select * from student where id >= 1;
-- 根据可重复读隔离级别的规则，此时 Transaction 10 不会重新生成 ReadView。
-- 此时表 student 中的 3 条数据都满足 where id >= 1，因此会先查出来。然后根据 ReadView 机制，判断是不是每条数据都可以被 Transaction 10 看到。
-- 1) 首先 id=1 的数据，前面已经说过了，可以被看到。
-- 2) 然后 id=2 的数据，它的 trx_id=30，此时 Transaction 10 发现，这个值处于 up_limit_id 和 low_limit_id 之间，因此还需判断 30 是否处于 trx_ids 内。
-- 由于 Transaction 10 的 trx_ids=[20,30]，在数组内，这表示 id=2 的这条数据是与事务 A 在同一时刻启动的其它事务提交的，所以这条数据不能让 Transaction 10 看到。
-- 3) 同理 id=3 的数据，trx_id 也为 30，因此也不能被 Transaction 10 看见。
-- 结论：Transaction 10 的第二次查询，只能查询出 id=1 的这条数据。没有出现幻读。
View Code
关于 purge
insert undo 在事务提交后就可以被释放掉了，而 update undo 由于还需支持 MVCC，不能立即删除掉。 为了支持 MVCC，对于 delete mark 操作来说，也仅仅是在记录上打一个删除标记，并没有真正删除。 随着系统的运行，在确定系统中包含最早产生的那个 ReadView 的事务不会再访问某些 update undo 日志以及被打了删除标记的记录后，有一个后台运行的 purge 线程会把它们真正的删除掉。 https://dev.mysql.com/doc/refman/8.3/en/sql-transactional-statements.html & https://dev.mysql.com/doc/refman/8.3/en/innodb-locking-transaction-model.html https://blog.jcole.us/innodb & https://github.com/jeremycole/innodb_diagrams & https://github.com/hedengcheng/tech/tree/master/database/MySQL https://relph1119.github.io/mysql-learning-notes & https://juejin.cn/book/6844733769996304392 http://mysql.taobao.org/monthly/2020/02/01 & https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_INNODB_REDO_LOG.html http://mysql.taobao.org/monthly/2021/10/01 & http://mysql.taobao.org/monthly/2021/12/02 & http://mysql.taobao.org/monthly/2015/04/01 |
【本文地址】
今日新闻 |
推荐新闻 |