MySql 之 InnoDB 事务与锁

一、事务

什么是事务？

官网：https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_transaction

事务是工作的原子单元，可以提交或回滚。当事务对数据库进行多个更改时，要么在事务提交时所有更改都成功，要么在事务回滚时所有更改都被撤消。

工作：就是我可以是单条sql、也可以是一批sql。

由InnoDB实现的数据库事务，满足我们众所周知的 ACID，即原子性、一致性、隔离性和持久性。

开启、提交、回滚事务

官网地址：https://dev.mysql.com/doc/refman/8.0/en/commit.html

事务提交、回滚

START TRANSACTION 或者 BEGIN start a new transaction.

-- 默认是 READ WRITE 如果参数为READ ONLY,则事务中不允许对表进行更改
START TRANSACTION READ WRITE;
-- 业务操作 多个业务操作
SELECT * FROM teacher WHERE id=1;
UPDATE teacher SET teacher_age = teacher_age+1 WHERE id=1; -- 当事务用 READ ONLY 修饰时，改操作无效
COMMIT; -- 提交该事务
ROLLBACK; -- 回滚事务

自动提交

-- 查询是否开启自动提交（会话）
show SESSION VARIABLES like 'autocommit'; 

-- 查询自动开启提交（全局）
show GLOBAL VARIABLES like 'autocommit'; 

-- 关闭自动提交
set SESSION autocommit = 0;

关闭自动提交后，单条sql语句执行，必须要进行 commit 或者 ROLLBACK。

还有，当单条语句是在 START TRANSACTION 开启的事务中，也必须进行 commit 或者 ROLLBACK。

不能回滚的语句

官网：https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

有些语句不能回滚。通常，这些语句包括 数据定义语言(DDL)语句，例如那些创建或删除数据库的语句，那些 创建、删除或更改表或存储过程的语句。

隐式提交语句

官网：https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

比如 ddl语句、权限操作等，都是隐式提交，不需要自己提交的。

SAVEPOINT 回滚点

事务是保证单条或者多条sql要么同时执行成功、要么全部回滚。

InnoDB是支持回滚点的操作的，何为回滚点，就是我可以回滚部分操作、提交部分操作

START TRANSACTION;
UPDATE teacher SET teacher_age=teacher_age+1 WHERE id=2;

-- 设置回滚点，如果回滚回滚点，后续内容会被回滚
SAVEPOINT mypoint;
UPDATE teacher SET teacher_age=teacher_age+1 WHERE id=1;

-- 回滚到回滚点 id=1的不生效 但是不代表事务结束，
-- 事务结束还需要commit或者ROLLBACK
ROLLBACK TO mypoint; 
COMMIT; -- 提交事务

查看事务

官网：https://dev.mysql.com/doc/refman/8.0/en/information-schemainnodb-trx-table.html

SELECT * FROM information_schema.INNODB_TRX;

字段说明： 见官网 https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-trx-table.html

事务隔离级别

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

所谓隔离级别是在多个事务同时进行更改和执行查询时，对性能和结果的性能、一致性之间的平衡进行设置。

简单一句话，就是多个事务并发的时候，你是去保证性能还是优先保证数据一致性。

在 sql 1992标准中，提供了4种DB隔离级别：ru、rc、rr、SERIALIZABLE ，innodb 默认是rr。

但是用户可以更改会话或者全局的隔离级别：

-- 官网：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

-- 修改当前会话的隔离级别为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 读已提交级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- RR级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 串行化级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

-- 查询 全局/会话 隔离级别
SHOW GLOBAL VARIABLES LIKE '%isolation%'; 
SHOW SESSION VARIABLES LIKE '%isolation%';

-- 查询自动提交是否开启
show session VARIABLES like 'autocommit';

二、非锁定一致性读取 && MVCC

非锁定一致性读（快照读），普通的 SELECT，通过 多版本并发控制（MVCC） 实现。

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

思想：一致性读取意味着InnoDB使用多版本来向查询提供数据库在某个时间点的快照。该查询查看在该时间点之前提交的事务所做的更改，而不查看后来或未提交的事务所做的更改。

MVCC

MVCC 用来解决读—写冲突的无锁并发控制，就是为事务分配事务ID。为每个数据修改保存一个版本，版本与事务ID 相关联。

读操作只读取该事务开始前的数据库快照。

它的实现原理主要是 版本链，Read View, undolog 来实现的

版本链

一个 ReadView 还不能够判断可见与不可见，见与不可见跟行数据相关，所以还跟每行数据的一些隐藏字段有关：

源码：storage\innobase\dict\dict0dict.cc 中 dict_table_add_system_columns方法

DB_ROW_ID： 隐藏的字段ID，当没有主键或者非空唯一索引时，我们的主键所以基于这个递增字段建立。

DB_TRX_ID： 更新这行数据的事务ID

DB_ROLL_PTR（版本链关键） ： 回滚指针，被改动前的undolog日志指针。

每次对数据库记录进行改动，都会记录一条undo日志，每条undo日志也都有一个DB_ROLL_PTR属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表 形成一个版本链, 另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，在根据ReadView判断版本可见性的时候会用到。

ReadView

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

源码在 \storage\innobase\include\read0types.h

class ReadView {
    //省略
    ..................
    /** The read should not see any transaction with trx id >= this
    value. In other words, this is the "high water mark". */
    trx_id_t m_low_limit_id; //如果大于等于这个值的事务 不可见 也称高水位线
    
    /** The read should see all trx ids which are strictly
    smaller (<) than this value. In other words, this is thelow water mark". */
    trx_id_t m_up_limit_id; // 所有小于这个值的事务的值都可见 也称低水位线 其实是m_ids里面的最小值
    
    /** trx id of creating transaction, set to TRX_ID_MAX for free views. */
    trx_id_t m_creator_trx_id; // 当前的事务ID
    
    /** Set of RW transactions that was active when thissnapshotwas taken */
    ids_t m_ids; //存活的事务ID 就是在创建readView 没有提交的事务的ID集合
}

m_low_limit_id： 即将要分配的下一个事务ID

m_up_limit_id： 所有存活的（没有提交的）事务ID中最小值

m_creator_trx_id：创建这个readView的事务ID

m_ids： 创建readView时，所有存活的事务ID列表

判断是否可见逻辑

图解: https://www.processon.com/view/link/5fc8f9391e08534f6cd2ac31

判断规则:

    1. 如果数据的 DB_TRX_ID < m_up_limit_id db_trx_id == creator_trx_id

      如果数据事务ID都小于存活的事务ID了，那么肯定不存活了，说明在创建ReadView的时候已经提交了，可见。

      或者

      数据的事务ID 等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，可见。

    1. 如果数据的 DB_TRX_ID >= m_low_limit_id, 大于等于我即将分配的事务ID， 那么表明修改这条数据的事务是在创建了ReadView之后开启的，不可见，如果小于则进入下一个判断。
    1. 如果 m_up_limit_id<= DB_TRX_ID < m_low_limit_id, 表明修改这条数据的事务在第一次快照之前就创建好了，但是不确定提没提交，判断有没有提交，直接可以根据活跃的事务列表 m_ids判断
      1. DB_TRX_ID 如果在 m_ids 中，表面在创建ReadView之时还没提交，不可见
      1. DB_TRX_ID 如果不在 m_ids，表面在创建ReadView之时已经提交，可见

然后会根据不同的隔离级别在不同的时候来创建ReadView来达到能不能解决不可重复读跟幻读的问题。

UndoLog

所谓UndoLog，也就是回滚日志，简单点，当我需要回滚的时候，能找到之前相关的数据。比如，我们 rollback 或者异常中断的时候，能找到改动之前的数据进行恢复。当然，在 mvcc 中也需要用到 undolog 来找到以往的数据来解决不可重复读跟幻读问题。

那么undolog到底怎么记录的，我们通过一个例子来看具体的操作。

-- 假如，现在我在事务ID=100的事务中对表进行以下操作：

BEGIN;
INSERT INTO teacher(id,teacher_name,teacher_age,teacher_addr) VALUES(5,'zhangsan',18,'湖南');
INSERT INTO teacher(id,teacher_name,teacher_age,teacher_addr) VALUES(6,'dpl',30,'长沙');
DELETE FROM teacher WHERE id=6;
UPDATE teacher SET teacher_age=19 where id=5;

-- commit;

在提交之前，这些操作都会进行undolog 记录。那么这些数据对应的undoLog是怎么样的？

其实很简单，如果是添加数据，回滚把这条记录删除；如果是删除数据，回滚的时候，把删除的重新插入；如果是修改数据，那么把旧值记录下来，然后回滚到以前的值。

undolog日志记录如下：https://www.processon.com/view/link/5fcb4e50f346fb3fc8768eaf

事务回滚

如果事务需要回滚，我就可以根据 undolog 的版本中的事务ID，回滚到之前的数据。

快照创建的时机：

如果隔离级别是RC，则 每次一致性读 都会创建一个新的快照。

如果隔离级别是RR，则在 第一次一致性读 的时候，创建快照。

三、锁定一致性读取 && 锁

锁定一致性读（当前读），SELECT ... FOR UPDATE/SELECT ... LOCK IN SHARE MODE / INSERT / UPDATE / DELETE，通过锁实现。

锁定读取其实就是我们经常讲的数据库的锁。就是在读取操作的时候，进行加锁，其他事务不能够进行操作。

在innoDB里面，加锁其实是加在索引记录上面的，这个索引数据如果加了锁，那么其他的事务就不能对这条索引数据进行更改，只有当当前事务更改完成后，其他事务才能更改。

这个锁也支持2种类型：

读锁

读锁，加了读锁，其他事务能够再次加读锁，又称为共享锁 或者 S锁

当我读取一个数据后，我不希望其他事务对数据进行更改，那么就可以采用读锁。

BEGIN;

-- 读锁 FOR SHARE 代替了LOCK IN SHARE MODE，但是LOCK IN SHARE MODE向后兼容,
-- 加锁后，其他事务在提交之前不能对数据加排他锁，但是能加读锁。
SELECT * FROM teacher where id=10 FOR SHARE;

COMMIT;

写锁

写锁，加上写锁，其他事务不能再去加其他的读锁或者写锁，又称为排它锁 或者 又叫X锁，Exclude锁。。我们一般修改会默认加上写锁。

BEGIN;

-- 索引扫描到id=10的数据，那么会锁id=10的数据，其他事务不能进行操作
SELECT * FROM teacher where id=10 FOR UPDATE; 

COMMIT;

以上是去加什么锁，分为 读锁 与 写锁，当然也有表级别的意向锁，这个也只是全表的一个标记，为了加锁提升性能。

刚才我们讲了，加锁是加在扫描的索引上的，那么扫描了哪些索引，

加锁哪些数据，又可以分为以下几个类型：

记录锁（Record Locks）

记录锁，顾名思义，是锁在索引记录上的锁，索引扫描某些数据的时候，在这些索引数据上加锁

例如：扫描到并且只扫描到相关的数据，比如数据库有id=10的数据

BEGIN;
-- 索引扫描到id=10的数据，那么会锁id=10的数据，其他事务不能进行操作
SELECT * FROM teacher where id=10 FOR UPDATE; 
COMMIT;

间隙锁（Gap Locks）

间隙锁是对索引记录之间的间隙的锁，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁。

所谓间隙，就是索引数据之间的间隙，那么间隙锁，就是锁住数据之间的间隙，不允许间隙之内添加数据。

但是间隙锁，只对添加生效，不能在间隙中添加新的数据，但是允许修改间隙内不存在的数据

例如

我现在 teacher 表中有ID为 1、4、10、23、34、50 的数据，那么间隙就是

（-∞，1），（1,4），（4,10），（10，23），（23，34），（34，50），（50，+∞），

如图

假如我索引扫描没有命中到记录，而是区间

BEGIN;

-- 事务查询 id>1 AND id<3，没有查询到索引记录，那么就会锁住间隙（1,4）的区间
SELECT * FROM teacher where id>1 AND id<3 FOR UPDATE;

COMMIT;

间隙锁保证了我在操作这个区间的时候，不会有新的数据插入，所以也解决了幻读的问题。

但是间隙锁，在RC级别下是禁用的，仅用于外键约束检查和重复键检查。因而RC是没有解决幻读问题的。

临键锁（Next-Key Locks）

是索引记录上的 记录锁 和索引记录之前的间隙上的间隙锁的组合。

就是我扫描的数据，既包含索引中存在的数据，又是扫描的一个区间。

BEGIN;

-- 事务查询id>1 AND id<5，既包含数据4，又包含区间（1,4），（4,10）
SELECT * FROM teacher where id>1 AND id<5 FOR UPDATE;

COMMIT;

事务查询id>1 AND id<5，既在索引中包含数据4，又包含区间（1,4），（4,10）

所以，会锁定数据4不允许修改，同时（1,4），（4,10）区间内不允许添加数据。

四、查看锁信息

查看已加锁信息

-- 查看数据锁信息
SELECT * from performance_schema.data_locks

在Mysql8.0.1之前 INFORMATION_SCHEMA.INNODB_LOCKS 但是后面已经被移除

官网说明：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html

查看事务锁等待

8.1之后查看锁的信息 与 之前的版本有所变更，在之前的版本主要在 INFORMATION_SCHEMA.INNODB_LOCKS 和 INNODB_LOCK_WAITS 2个表中，老版本的查看：https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-examples.html

例如：

-- session1：
BEGIN;
-- 给数据加上4到10的间隙锁
SELECT * FROM teacher where id>4 and id<6 FOR UPDATE;
COMMIT;

-- session2：
-- id=5在加锁区间，会进行阻塞
INSERT INTO teacher(id,teacher_name,teacher_age,teacher_addr) VALUES(5,'景天',30,'湖南');

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-examples.html?spm=a2c4g.11186623.0.0.1f013e3a7QfUoL

查看哪些事务在阻塞、哪些在等待：

SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

-- 或者简单的使用

SELECT
    waiting_trx_id,
    waiting_pid,
    waiting_query,
    blocking_trx_id,
    blocking_pid,
    blocking_query
FROM sys.innodb_lock_waits;

得到结果：

找不到blocking的语句？去官网：https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-examples.html#innodb-informationschema-examples-null-blocking-query

1.根据blocking_pid 查询threads

SELECT THREAD_ID FROM performance_schema.threads  
WHERE PROCESSLIST_ID =105;

2.根据THREAD_ID查询历史事件events_statements_history

SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_history 
WHERE THREAD_ID = 163 ORDER BY EVENT_ID;

通过事务状态来查看

1.查看事务信息

SELECT * FROM information_schema.INNODB_TRX;

2.可以得到在等待的 lock_id，然后去

SELECT * FROM performance_schema.data_lock_waits 
WHERE REQUESTING_ENGINE_LOCK_ID='xxxx'

3.会得到BLOCKING_THREAD_ID，比如 2879

4.查询 events_statements_history

SELECT THREAD_ID, SQL_TEXT  
FROM performance_schema.events_statements_history 
WHERE THREAD_ID = 2879 ORDER BY EVENT_ID DESC;

大概能找到导致锁的语句。

行锁等待时间

SELECT @@innodb_lock_wait_timeout;

官网地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

单位s，默认50s。最小1s

举例：

-- 会话1
BEGIN;
-- 开启事务不提交 占有行锁id=1
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=1; 


-- 会话2
-- 修改会话等待行锁的时间为10s
set innodb_lock_wait_timeout=10;
BEGIN;
-- 修改行锁数据，超过10s会报错
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=1;

死锁检测

Mysql默认是会有死锁检测：

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_deadlock_detect

什么是死锁？

两个或多个事务相互等待对方释放锁而陷入无限等待的状态，从而导致事务无法继续执行

我们在并发的时候，学过死锁的4个条件：

  1. 互斥 2个事务拿互斥的资源
  2. 请求和保持条件 个事务在等待其他事务持有的资源时，仍然保持自己所持有的资源不释放
  3. 不可剥夺 一个事务持有的资源不能被其他事务强制性地抢占
  4. 循环等待

例子：

-- 会话1：
BEGIN;
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=1; -- 第一步执行
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=4 -- 第三步执行 阻塞
COMMIT;

-- 会话2：
BEGIN;
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=4; -- 第二步执行
UPDATE gp_teacher SET teacher_age=teacher_age+1 where id=1 -- 第四步执行
COMMIT;

第四步执行的时候，发现死锁.

最后修改：2024 年 01 月 23 日
© 允许规范转载
如果觉得我的文章对你有用，请点个赞吧~