【数据库】事务与锁

参考： https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

事务ACID：bytebytego 彩图 https://mp.weixin.qq.com/s/8Q9Ze82cJx-BzRxchkbiIQ

事务隔离

事务是确保数据库操作完整性的一种机制，其特性概括为 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。其中，隔离性是通过设置不同的事务隔离级别来实现的，以平衡性能与数据一致性。主要的事务隔离级别包括：

读未提交（Read Uncommitted）：允许事务查看其他未提交事务的更改，可能导致“脏读”。
读提交（Read Committed）：一个事务只能看见已经提交的更改，解决了脏读问题，但可能出现“不可重复读”现象。
可重复读（Repeatable Read）：在一个事务内多次读取同一数据结果一致，解决了不可重复读问题，但可能遇到“幻读”。
串行化（Serializable）：最高隔离级别，通过完全锁定避免了脏读、不可重复读和幻读，但性能影响最大。

涉及到的并发问题：

脏读：在一个事务中，能够读取到另一个未提交事务中的数据，如果这个未提交的事务后来被回滚，那么之前读取的数据就是无效的，即脏数据。
不可重复读：在同一个事务中，多次查询同一数据时，由于其他事务的提交，导致查询结果不一致，后面读取到了初次读取后其他事务修改过的数据。
幻读：在一个事务内，多次执行相同的查询语句返回的结果集记录数不同，这是因为其他事务插入了新数据，虽然这些新数据符合原事务最初的查询条件，但并没有在第一次查询结果中出现，仿佛凭空出现一样，造成了幻觉。

为了解决隔离级别中的并发问题，MySQL 引入了多版本并发控制（MVCC），它通过维护数据的多个版本来实现事务间的隔离。具体实现涉及：

每个事务拥有唯一的事务ID（transaction id）。
更新操作会在undo log中保存旧版本数据，并标记新数据的事务ID。
查询时根据事务的视图（由已提交的最大事务ID界定）决定可见版本，从而实现不同隔离级别的逻辑。

针对特定问题，如幻读（Phantom Read），InnoDB通过引入间隙锁（Gap Lock）来防止新记录插入造成的幻觉，这主要发生在可重复读隔离级别下进行范围查询时。

锁：

全局锁：Flush tables with read lock (FTWRL)。全库逻辑备份，期间数据库变为只读状态。相比 set global readonly=true，FTWRL 在客户端异常断开时能自动释放锁，降低了库长时间不可写的风险。
表级锁：分为表锁（通过lock tables ... read/write 命令实现，限制其他线程读写，同时限制本线程后续操作类型）和元数据锁（MDL，自动应用于 DML 和 DDL 操作间，保证数据一致性和操作隔离性，分为读锁和写锁，遵循读读共享、读写/写写互斥原则。）
行锁：InnoDB 引擎支持，提供细粒度并发控制，遵循两阶段锁协议，减少锁争用，提升系统并发能力。
间隙锁（Gap Lock）：锁的就是两个值之间的空隙，解决幻读问题，仅在可重复读隔离级别下生效。降低并发并可能引发死锁，其冲突目标为插入操作而非另一个锁。
Next-Key Lock：结合间隙锁和行锁，是 InnoDB 默认的行锁算法。基本单位是前开后闭区间，实际加锁依据查询条件及索引情况动态调整，能够退化为行锁或间隙锁。

autocommit, Commit, and Rollback

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own.

By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

Some statements implicitly end a transaction, as if you had done a COMMIT before executing the statement. For details, see Section 13.3.3, “Statements That Cause an Implicit Commit”.

A COMMIT means that the changes made in the current transaction are made permanent and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

SQL 行锁类型说明

INSERT ... 排他锁自动加锁
UPDATE ... 排他锁自动加锁
DELETE ... 排他锁自动加锁
SELECT（正常）不加任何锁
SELECT ... LOCK IN SHARE MODE 共享锁需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE 排他锁需要手动在SELECT之后加FOR UPDATE