事务（TRANSACTION）

事务是数据库操作的程序执行单元，保证成批的DML语句要么全执行，要么全不执行。如中间发现错误，会被回滚（Rollback）到事务开始前的状态。
MySQL常见的数据库引擎中支持事务的是InnoDB，不同数据库引擎原理和表现不同，以下内容皆基于InnoDB。

四大特性（ACID）

1）原子性 Atomicity：指整个数据库事务是不可分割的原子，要么都成功，要么都不成功。
2）一致性 Consistency：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3）隔离性 Isolation：也可以叫做并发控制、可串行化、锁等，要求并发的事务间互不干扰。通过事务的隔离级别进行设置。
4）持久性 Duration：事务一旦提交，结果就应该永久保存下来。

事务提交

1. 通过 ROLLBACK / COMMIT 显式提交
2. 通过 DDL 或 DCL 语句隐式提交：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
   EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
3. 自动提交（默认开启）
   MVCC下，单行指令都是事务。如果关闭自动提交，则所有单行指令都需要commit之后才能持久化。

select @@autocommit; --查看开启状态
SET AUTOCOMMIT ON；

隔离级别

一个事务与其他事务隔离的程度称为隔离级别，InnoDB通过锁与MVCC共同实现隔离。

• 读未提交
  最宽松，不加锁，可以读到其他事务未提交的数据
• 读提交
  只能读到其他事务已提交的数据，但如果在两次select间，其他事物进行了提交，就有可能导致不可重复读或幻读
• 可重复读
  InnoDB的默认值，通过快照防止不可重复读
• 串行
  事务将会顺序执行，性能最差但最安全

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED（读未提交）	可能	可能	可能
READ COMMITTED（读提交）	不可能	可能	可能
REPEATABLE READ（可重复读）	不可能	不可能	可能
SERIALIZABLE（串行）	不可能	不可能	不可能

• 脏读 读到了其他事务未提交的数据（意味着这些数据可能会回滚）。
• 不可重复读 在一个事务内，不同时刻读到的可能是不同的数据（针对其他事务的UPDATE）。
• 幻读 在一个事务内，不同时刻读到的可能是不同的行（针对其他事务的INSERT/DELETE）。

常用语法

• BEGIN 或 START TRANSACTION 显式地开启一个事务
• ROLLBACK 或 ROLLBACK WORK 回滚并结束事务
• COMMIT 或 COMMIT WORK 提交事务，并使修改成为永久性的
• SAVEPOINT identifier 创建保存点（子事务）
• RELEASE SAVEPOINT identifier 删除一个保存点
• ROLLBACK TO identifier 回滚到保存点
• SET TRANSACTION 设置事务的隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 、SERIALIZABLE

begin;
update temp_user set mobile=111 where id =1;
rollback;

• 查看/设置当前会话/系统的隔离级别

select @@tx_isolation;
select @@global.tx_isolation;
set session transaction isolation level read committed;
set global transaction isolation level repeatable read;

MVCC（multi version concurrent control 多版本并发控制）

InnoDB中根据MVCC，每个单独的操作也是一个事务。
每开启一个事务，事务版本号+1
每行记录都有两个隐藏列：创建版本号和删除版本号

• select：事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。
• update：插入一条新记录，并把当前系统版本号作为行记录的版本号，同时保存当前系统版本号到原有的行作为删除版本号。
• delete：把当前系统版本号作为行记录的删除版本号
• insert：把当前系统版本号作为行记录的版本号

REPEATABLE-READ模式下的快照读与当前读

• 快照读
  事务内，简单select操作执行后会生成快照（记录当时的MVCC版本号）。
  同一事务内再次执行select时，查询的其实是前面生成的快照（可能是过期数据），因此不受其他事务的影响，保证了可重复读。
  同一事务内进行插入/更新/删除操作时，快照也会相应更新（但不会更新外部事务导致的变化）。

select * from table where ?;

• 当前读
  带锁的读操作，以及插入/更新/删除操作，总是会获得最新数据，并对其加锁，防止其他事务并发地对其修改。
  锁会在事务结束后自动释放。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;

案例一：事务A等待事务B的锁

1. 事务A

start transaction;
select * from user_token;--产生快照

2. 事务B

start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
--当前读，获取了共享锁

3. 事务A

select * from user_token;--从快照读取，没有新数据
update user_token set user_id = 1 where id = 4;--当前读，暂无响应，等待B事务的锁释放

4. 事务B

commit;--提交并释放锁

5. 事务A

--上一步update操作获得锁，完成更新
select * from user_token;--此时快照也更新，获得了最新数据
commit;--提交并释放锁

案例二：手动加锁解决幻读

1. 事务A

start transaction;
select * from user_token;--产生快照

2. 事务B

start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
commit;--提交并释放锁

3. 事务A

select * from user_token;--从快照读取，没有新数据
update user_token set user_id = 1 where id = 4;--当前读，更新成功，快照也更新
select * from user_token;--从新快照读取，多了id=4的数据,出现幻读！
commit;--提交并释放锁

其实应在事务A初次select时即添加锁，则事务B会在A结束后才执行：

start transaction;
select * from user_token lock in share mode;--共享锁
--或
select * from user_token for update;--排他锁

---

锁

操作已上锁的数据时，数据库会自动等待锁释放。长时间不释放会超时报错：

Err] 1205 - Lock wait timeout exceeded; try restarting transaction

---

锁粒度

表级锁（MySQL Server实现）

开销小，加锁快；不会出现死锁；
锁定粒度大，发生锁冲突的概率高，并发度低。
当前会话结束时，表锁会自动unlock。

lock tables 表名 read; --表添加共享锁
lock tables 表名 write; --表添加排他锁
flush tables with read lock; --所有表添加共享锁
flush tables with write lock; --所有表添加排他锁
unlock tables; --解除所有表级锁

页级锁（仅BDB支持）

介于行级锁和表级锁之间

行级锁（InnoDB支持）

开销大，加锁慢；会出现死锁；
锁定粒度小，发生锁冲突的概率低，并发度高。

• InnoDB的行锁是针对索引的锁，不是针对记录加的锁
  因此访问相同索引键的不同行，也会出现锁冲突。
  当通过索引检索数据，InnoDB使用行级锁，否则，InnoDB将使用表级锁。
• 间隙锁（Next-Key锁）
  InnoDB通过间隙锁解决REPEATABLE-READ模式下的幻读问题，但也因此锁了多余的数据

---

加锁时机

悲观锁

每次读取数据的时候都默认其他线程会更改数据，因此先锁定，再修改。这种方式称为悲观并发控制【Pessimistic Concurrency Control，“PCC”】，安全但效率较低。

乐观锁

乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新时才进行锁定。乐观锁适用于读多写少的场景，效率较高。

---

锁的兼容性

共享锁【shared locks】

又称为读锁，S 锁。其他事务能访问到数据，但是只能读不能修改。
当前事务可以将其升级为排他锁。

排他锁【exclusive locks】

又称为写锁，X 锁，独占锁，互斥锁。如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。

---

死锁

多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象。若无外力作用，事务都将无法推进下去。
InnoDB 会自动检测死锁，使一个undo较少的事务释放锁回退，另一个事务获取锁。
频繁触发对性能影响较大，应尽量避免。可通过show engine innodb status查看死锁日志

表死锁

一个事务访问表A（锁住了表A），然后企图访问表B；另一个事务访问了表B（锁住了表B），然后企图访问表A；这时两个事务互相等待对方释放，就产生了死锁。

• 尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；

行死锁

类似表死锁，两行数据交叉先后访问也会造成行死锁。
此外如果一条 SQL 语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。此时如果有另一个事务锁定了主键索引，则发生死锁。

• 在同一个事务中，尽可能一次锁定所有需要的资源，减少死锁产生概率
• 对于非常容易产生行死锁的业务部分，可以尝试使用表级锁代替。
• 不要直接用非主键索引作为检索条件进行更新。
  改为先通过该索引找到主键，然后用主键进行UPDATE。