MySql-事务和锁

1. 事务概念

事务（ Transaction）由一次或者多次基本操作构成，或者说，事务由一条或者多条 SQL 语句构成。

事务相关命令：

1）begin transaction;/start transaction; 显式的开启一个事务
2）commit/commit work; 提交事务
3）rollback/rollback work; 回滚事务
4) savepoint identifier; SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
5) release savepoint identifier; 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
6) rollback to identifier; 把事务回滚到标记点；
7) SET TRANSACTION; 用来设置事务的隔离机制。（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE）
8) SET AUTOCOMMIT； 设置自动提交。

2. 事务的四大特性

• 1、原子性（Atomicity）：事务是一个原子操作，它要么全部成功，要么全部失败回滚。如果事务中的任何操作失败，则所有操作都将回滚到之前的状态，以确保数据库中的数据不会被部分更改。

• 2、一致性（Consistency）：事务的执行必须使数据库从一个一致状态转换到另一个一致状态。这意味着事务必须满足所有约束条件，以保持数据的完整性和一致性。

• 3、隔离性（Isolation）：并发事务的执行不能相互干扰。事务必须在独立的空间内执行，这意味着它们看起来像是在独占访问数据库。

• 4、持久性（Durability）：一旦事务完成提交，其结果就是永久性的，并且即使在系统故障的情况下，也必须能够恢复这些结果。

3. 事务并发引发的问题

1. 读-读冲突

不涉及到数据变化，没有任何问题。

2. 读-写冲突

• 脏读

一个事务读取了另一个事务未提交的数据。

• 不可重复读

事务多次读取一个数据，因为中间被其他事务 update，导致两次读取数据内容不一致，注重修改。

• 幻读

事务多次读取同一个数据，因为中间被其他事务 insert 或 delete， 导致两次数据总量不一致，注重增加删除。

主要通过 MVCC 来解决

3. 写-写冲突

todo 应该是用锁。。

4. 事务的四种隔离级别

查看隔离机制

show variables like 'transaction_isolation';
SELECT @@tx_isolation;  //navicat 里不好使

• Read uncommitted（读未提交）

  一个事务可以读取另一个未提交事务的数据。

  set session transaction isolation level read uncommitted;
  可以通过 global 替换 session，就是修改全局的隔离机制，而不是当前 session 的。

• Read committed（读已提交）

  一个事务中两次读取直接，有其他 update,insert,delete 等操作修改了数据，两次读取结果不一致。

  set session transaction isolation level read committed;

• Repeatable read（重复读）

  当前事务运行期间，隔离其他事务的变化。

  set session transaction isolation level repeatable read;

  实际上可重复读，可以解决大部分的幻读，只是不能完全解决，后面再叙。

• Serializable（串行化）

  事务期间直接加锁，不让其他事务修改。

  set session transaction isolation level serializable;

隔离级别	脏读	不可重复读	幻读
Read Uncommitted 读未提交	可能	可能	可能
Read Committed 读已提交	不可能	可能	可能
Repeatable Read 可重复读	不可能	不可能	可能
Serializable 可串行化	不可能	不可能	不可能

Mysql的InnoDB引擎使用的是Repeatable read（重复读），SQL Server和Oracle默认隔离级别为Read Committed（读已提交）

隔离机制主要是用来解决读写冲突的，对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。

RR 隔离机制下的幻读和不可重复读：https://blog.csdn.net/xingjingb/article/details/128360227

5. MVCC-解决读写冲突原理

1. MVCC 概念

MVCC，即 Multi-Version Concurrency Control （多版本并发控制）。它是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本 id，比对事务 id 并根据事物隔离级别去判断读取哪个版本的数据。

数据库隔离级别 读已提交、可重复读 都是基于 MVCC 实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。

2. 隐藏字段

数据表中的每条记录，除了我们自定义的字段外，还有数据库隐式定义的 trx_id、roll_pointer、row_id 等3个字段。

• trx_id：最近修改（更新、插入）事务ID：记录创建这条记录/最后一次修改该记录的事务ID（每次事务开启，会产生一个递增的事务id）
• roll_pointer：回滚指针，配合 undo 日志，指向这条记录的上一个版本
• row_id ：隐藏主键（自增id），如果数据表没有主键，则 InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引

3. undo log

undo log，mysql 众多日志的一种，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到 undo log 里，如果事务回滚，即可以通过 undo log 来还原数据。

可以这样认为，当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，当 update 一条记录时，它记录一条对应相反的 update 记录。

undo log 有什么用途呢？

• 事务回滚时，保证原子性和一致性。

• 用于 MVCC 快照读。

4. 版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。

5. 快照读和当前读

• 快照读： 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的 select 语句都是快照读，快照读可能读到的并不一定是数据的最新版本，而是之前的历史版本

select * ...

• 当前读：读取的是记录数据的最新版本，显式加锁的都是当前读，它会读取记录的最新版本，并对读取的记录进行加锁，保证其他并发事务不能修改当前记录

select * ... for update;
select *  lock in share mode;
update ...;
insert ...;
delete ...;

6. readview-读视图

  Read View 主要用来做可见性判断，即当某个事务执行快照读的时候，会对该记录创建一个读视图，把它比作条件来判断当前事务能够看到该行记录哪个版本的数据，即可能是最新的数据，也有可能是 undo log 里某个版本的数据。

Read View 有几个全局属性：

• trx_list：一个数值列表，维护生成 Read View 时系统正活跃的事务 ID 列表
• min_limit_id：trx_list 列表中最小的事务 ID
• max_limit_id：ReadView 生成时系统尚未分配的下一个事务 ID，即已出现过事务 ID 的最大值 + 1
• creator_trx_id：生成 read——view 的事务 id

可见性算法流程：

根据版本链，对记录的每个版本做以下处理，直到查询到可见版本为止。

• 1. 如果数据事务 ID trx_id < min_limit_id，表明生成该版本的事务在生成 Read View 前，已经提交(因为事务 ID 是递增的)，所以该版本可以被当前事务访问。
• 2. 如果 trx_id>= max_limit_id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。
• 3. 如果 min_limit_id =<trx_id< max_limit_id,需腰分 3 种情况讨论

  （1）如果m_ids包含trx_id,则代表 Read View 生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。

  （2）如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则 Read View 生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；

  -（3）如果m_ids不包含trx_id，则说明你这个事务在 Read View 生成之前就已经提交了，修改的结果，当前事务是能看见的。

1. 在RC级别下，事务中，每次快照读都会新生成一个快照和Read View，这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。 2. 在RR级别下，某个事务的对某条记录的第一次快照读会创建一个快照（Read View），将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，之后的修改对其不可见。

6. 锁

1. 从对数据库操作的类型分

• 读锁 （共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，正常 select 操作不加锁。

select * from T where id=1 lock in share mode;

• 写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，正常的 update，delete，insert 都会加写锁。

select * from T where id=1 for update;

• 意向锁：为表级别的锁，其目的是为了协调表锁和行锁的关系。

  参考：https://cloud.tencent.com/developer/article/2001233

读读共享，读写互斥，写写互斥

2. 从对数据操作的粒度分

• 表锁：每次操作锁住整张表。开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低

# 锁表
lock tables sys_user write;
lock tables sys_user read;
 
# 查询mysql 哪些表正在被锁状态
show OPEN TABLES where In_use > 0;
 
# 解锁
UNLOCK TABLES;
 
# 查找持有表锁的会话，通过 id 终止会话也可以解锁
show processlist;
kill 'id';

• 行锁：每次操作锁住一行数据。开销大，加锁慢(加行锁，需要找到某一行所以开销会大一点，锁定粒度最小，发生锁冲突的概率最低，并发度最高。

  InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

  RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）；
  GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）；锁的是索引叶子节点的next指针，或者说间隙锁是一个在索引记录之间的间隙上的锁；解决了mysql重复读级别下的幻读问题；
  Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）。

  在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

  （RR隔离级别，优先加记录锁和间隙锁，当SQL有唯一索引时，才降级为记录锁）。

  一般 sql 加锁

  • select ... from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
  • select ... from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
  • select ... from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
  • update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
  • delete ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
  • insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

  死锁处理：https://blog.csdn.net/weixin_44797327/article/details/134611841

3. 按操作性能分类

• 悲观锁
  在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

• 乐观锁
  不加锁，而是在事务提交时，再去判断数据是否有冲突。实现关键点在于冲突的检测。一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

5. 相关问题汇总

1. 未提交事务

如果一个事务未提交，也未回滚，就会占用数据库资源，只要 session 不关一直在那等着，好像有超时回滚时间，不确定，以后研究。

查询未提交事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_STATE = 'RUNNING';

2. 设置自动提交

mysql 默认是自动提交的，每当我们执行一个 sql，其实是默认提交的事务。

设置自动提交

set autocommit = 0; //关闭自动提交
set autocommit = 1; //开启自动提交，默认的

关闭自动提交后，必须要手动 commit 或者 rollback;
开启自动提交，sql 执行完事务会自动提交。如果手动开启事务，就需要手动 commit 或者 rollback;

参考文件

https://blog.csdn.net/weixin_37672801/article/details/128088979