事务（TRANSACTION）
事务是数据库操作的程序执行单元，保证成批的DML语句要么全执行，要么全不执行。如中间发现错误，会被回滚（Rollback）到事务开始前的状态。
MySQL常见的数据库引擎中支持事务的是InnoDB，不同数据库引擎原理和表现不同，以下内容皆基于InnoDB。
四大特性（ACID）
1）原子性 Atomicity：指整个数据库事务是不可分割的原子，要么都成功，要么都不成功。
2）一致性 Consistency：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3）隔离性 Isolation：也可以叫做并发控制、可串行化、锁等，要求并发的事务间互不干扰。通过事务的隔离级别进行设置。
4）持久性 Duration：事务一旦提交，结果就应该永久保存下来。
事务提交
- 通过 ROLLBACK / COMMIT 显式提交
- 通过 DDL 或 DCL 语句隐式提交：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。 - 自动提交（默认开启）
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的锁
- 事务A
start transaction;
select * from user_token;--产生快照
- 事务B
start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
--当前读，获取了共享锁
- 事务A
select * from user_token;--从快照读取，没有新数据
update user_token set user_id = 1 where id = 4;--当前读，暂无响应，等待B事务的锁释放
- 事务B
commit;--提交并释放锁
- 事务A
--上一步update操作获得锁，完成更新
select * from user_token;--此时快照也更新，获得了最新数据
commit;--提交并释放锁
案例二：手动加锁解决幻读
- 事务A
start transaction;
select * from user_token;--产生快照
- 事务B
start transaction;
insert into user_token VALUES(4,4,"4967e70c5d7e11ecb5c90242ac110003","pc","2022-02-02 22:22:22.222222");
commit;--提交并释放锁
- 事务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。
网友评论