深入理解MySQL中的事务和锁_事务隔离级别共享锁和排他锁-CSDN博客

本文链接：https://blog.csdn.net/rxbook/article/details/130397025

数据库中的事务是什么？

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

事务的四大特性 ACID（Atomicity、Consistency、Isolation、Durability），分别是原子性、一致性、隔离性和持久性。在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是目的。其中隔离性可以防止数据库在并发处理时出现数据不一致的情况。最严格的情况下，我们可以采用串行化的方式来执行每一个事务，这就意味着事务之间是相互独立的，不存在并发的情况。然而在实际生产环境下，考虑到随着用户量的增多，会存在大规模并发访问的情况，这就要求数据库有更高的吞吐能力，这个时候串行化的方式就无法满足数据库高并发访问的需求，我们还需要降低数据库的隔离标准，来换取事务之间的并发能力。

InnoDB 是支持事务的，而 MyISAM 存储引擎不支持事务。

使用SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些。

InnoDB事务的操作：

START TRANSACTION #或者 BEGIN，#显式开启一个事务。
COMMIT #提交事务。当提交事务后，对数据库的修改是永久性的。
ROLLBACK #或者 ROLLBACK TO [SAVEPOINT] #回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。
SAVEPOINT #在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
RELEASE SAVEPOINT #删除某个保存点。
SET TRANSACTION #设置事务的隔离级别。

使用事务有两种方式，分别为隐式事务和显式事务。隐式事务实际上就是自动提交，MySQL 默认就是自动提交，当然也可以手动配置 MySQL 的参数：

mysql> set autocommit =0; // 关闭自动提交
mysql> set autocommit =1; // 开启自动提交

事务的操作：

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB; #创建一个测试表

SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;

SELECT * FROM test;

MySQL事务的隔离级别

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。你隔离得越严实，就越安全，但是效率也会越低。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。这4种隔离级别，并行性能依次降低，安全性依次提高。

隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大。在实际使用过程中我们往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方案，只有适合与否。

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。

读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。可以避免脏读。

可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。可以避免不可重复读和脏读，但无法避免幻读（MySQL默认）。

串行化：我的事务尚未提交，别人就别想改数据。可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

MySQL中默认的事务隔离级别是可重复读(Repeatable Read)。

# 可以用 show variables 来查看当前的事务隔离级别值
show variables like 'transaction_isolation';

# 可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

# 修改默认隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #把隔离级别设置为 READ UNCOMMITTED（读未提交）

脏读、不可重复读、幻读

脏读：一个事务读取到了另外一个事务没有提交的数据。事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
不可重复读：一个事务读取到了另外一个事务中提交的update的数据；两次读取同一数据，得到内容不同。事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：一个事务读取到了另外一个事务中提交的更新的数据。

	脏读	不可重复读	幻读
读未提交	✔️	✔️	✔️
读已提交	❌	✔️	✔️
可重复读	❌	❌	✔️
串行化	❌	❌	❌

上面说到的四种隔离级别里面 Serializable是最高的事务隔离级别，同时代价也最高，性能很低，一般很少使用。在这个级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。MySQL的默认隔离级别就是Repeatable read（可重复读），因此有可能会出现幻读。InnoDB通过MVCC(多版本并发控制)来解决幻读问题的。

隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大。在实际使用过程中往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方案，只有适合与否。

MVCC（多版本并发控制）

MVCC英文原意是 Multiversion Concurrency Control，翻译过来就是多版本并发控制。它的核心思想就是保存数据的历史版本快照，其他事务增加与删除数据，对于当前事务来说是不可见的。读取数据的时候不需要加锁也可以保证事务的隔离效果。

通过 MVCC 可以解决以下几个问题：

读写之间阻塞的问题。通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
降低了死锁的概率。MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
解决一致性读的问题。一致性读也被称为快照读，当查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

InnoDB 中的 MVCC 是如何实现的？

InnoDB的每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本，版本号（trx_id）随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

每开启一个事务，生成一个自增长的版本号；
MVCC 是通过 Undo Log + Read View 进行数据读取，其中 Undo Log 保存了历史快照，而 Read View 规则判断当前版本的数据是否可见。
在可重复读的情况下，InnoDB 可以通过 Next-Key 锁 + MVCC 来解决幻读问题。

InnoDB的隐藏字段：

db_row_id：隐藏主键，用来生成默认聚簇索引；
db_trx_id：创建或最后修改记录的事务ID；
db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。

几个细节问题：

回滚日志什么时候删除？系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。
什么时候不需要了？当系统里么有比这个回滚日志更早的read-view的时候。
为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。

快照读和当前读

快照读读取的是快照数据，一般不加锁的 SELECT 都属于快照读，比如：

SELECT * FROM users WHERE id=1;

当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改以及DML操作都属于当前读，比如：

SELECT * FROM users LOCK IN SHARE MODE;
SELECT * FROM users FOR UPDATE;
INSERT INTO users values ...
DELETE FROM users WHERE ...
UPDATE users SET ...

接下来演示一下幻读，也就是同一个事务中不同的时间，两次相同的查询获取到的数据不同。如下分别打开两个客户端，执行下面的语句。

# client1
begin; --开启事务
select * from users; --查到1条数据

# client2
begin; --开启事务
select * from users; --查到1条数据
insert into users(id,name,age) values (2,'李四',21); --插入一条数据
commit; --提交事务

# client1
select * from users; --查到还是1条数据，因为当前是快照读
update users set age=12; --使用update语句触发了当前读，结果是2行受影响
select * from users; --再次查询得到2条数据

# client1 有没有办法不执行update就直接读到最新的数据？有，使用当前读：
select * from users for update;

【问题】既然有了redolog ,为什么还要有binlog呢?

【回答】redolog：用来保证事务的持久性的，保证数据不会丢失的。因为redolog只有innodb才有，而 binlog是MySQL所有引擎都有。

【继续问题】两个log怎么保证数据一致性？

【回答】两阶段提交，恢复数据的时候比较两个log。

【问题】为什么隔离级别为读未提交时，不适用于 MVCC 机制呢？
【回答】“读未提交”隔离级别不需要多版本数据。每个事务都读取最新数据，假设事务A把X从0改成1，无论事务A是否提交，事务B读到X为1，如果事务A回滚，事务B再次读X，自然就得到0，根本不需要MVCC帮衬。

【问题】读已提交和可重复读这两个隔离级别的 Read View 策略有何不同？
【回答】“读已提交”时，每次SELECT操作都创建Read View，无论SELECT是否相同，所以可能出现前后两次读到的结果不等，即不可重复读。
“可重复读”时，首次SELECT操作才创建Read View并复用给后续的相同SELECT操作，前后两次读到的结果一定相等，避免了不可重复读。

总结：MySQL中的ACID，其中原子性依靠 undolog 实现，隔离性依靠 MVCC 实现，持久性依靠 redolog 实现，一致性由上面三个共同决定。

MySQL中的锁

从锁定对象的粒度大小来对锁进行划分：行锁、页锁、表锁、全局锁。按照锁的功能对锁进行划分：共享锁和排它锁。按照锁的实现⽅式分为：悲观锁和乐观锁。

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。全局锁的典型使用场景是，做全库逻辑备份。在备份过程中整个库完全处于只读状态。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的，表锁的语法是 lock tables TABLE_NAME read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。但是需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的。MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。

如何安全地给小表加字段？在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

测试数据：

CREATE TABLE mylock (
    id int(11) NOT NULL AUTO_INCREMENT,
    NAME varchar(20) DEFAULT NULL,
    PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a');
INSERT INTO mylock (id,NAME) VALUES (2, 'b');
INSERT INTO mylock (id,NAME) VALUES (3, 'c');
INSERT INTO mylock (id,NAME) VALUES (4, 'd');

【表读锁】

1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问⾮锁定表
4、session2：select * from mylock; -- 可以查询 没有锁
5、session2：update mylock set name='x' where id=2; -- 修改阻塞,⾃动加⾏写锁
6、session1：unlock tables; -- 释放表锁
7、session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执⾏完成
8、session1：select * from tdep; --可以访问

【表写锁】

1、session1: lock table mylock write; -- 给mylock表加写锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问⾮锁定表
4、session1：update mylock set name='y' where id=2; --可以执⾏
5、session2：select * from mylock; -- 查询阻塞
6、session1：unlock tables; -- 释放表锁
7、session2：4 rows in set (22.57 sec) -- 查询执⾏完成
8、session1：select * from tdep; --可以访问

MyISAM引擎的锁：

MyISAM存储引擎只支持表锁，可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定争夺，如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给MyISAM表显式加锁。

如果需要显式加锁，使用下面的SQL语句：

Lock tables users read local, users_detail read local;
Select sum(total) from users;
Select sum(subtotal) from users_detail;
Unlock users;

当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错。通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

InnoDB引擎的锁：

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。可以通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况：

- Innodb_row_lock_current_waits：当前正在等待锁定的数量；
- Innodb_row_lock_time：从系统启动到现在锁定总时间⻓度；
- Innodb_row_lock_time_avg：每次等待所花平均时间；
- Innodb_row_lock_time_max：从系统启动到现在等待最常的⼀次所花的时间；
- Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

如果发现锁争用比较严重，比如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。所以，现在知道InnoDB中索引的重要性了吧？可以点击这里看看MySQL中关于索引的理解。MySQL关于索引的理解_浮.尘的博客-CSDN博客

锁的粒度：

表锁，系统开销最小，会锁定整张表，MyIsam使用表锁。

行锁，最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB使用行锁。

InnoDB的⾏级锁，按照锁定范围来说分为三种：

记录锁（Record Locks）：锁定索引中⼀条记录。
间隙锁（Gap Locks）：要么锁住索引记录中间的值，要么锁住第⼀个索引记录前⾯的值或者最后⼀个索引记录后⾯的值。
Next-Key Locks：是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

InnoDB⾏读锁演示：

1、session1: begin;--开启事务未提交
             select * from mylock where ID=1 lock in share mode; --⼿动加id=1的⾏读锁,使⽤索引
2、session2：update mylock set name='y' where id=2; -- 未锁定该⾏可以修改
3、session2：update mylock set name='y' where id=1; -- 锁定该⾏修改阻塞
            ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁
5、session2：update mylock set name='y' where id=1; --修改成功
            Query OK, 1 row affected (0.00 sec)
            Rows matched: 1 Changed: 1 Warnings: 0
注：使⽤索引加⾏锁 ，未锁定的⾏可以访问

在没有索引的列上执行行读锁，会被升级为表锁：

1、session1: begin;--开启事务未提交 
             select * from mylock where name='c' lock in share mode; --⼿动加name='c'的⾏读锁,未使⽤索引
2、session2：update mylock set name='y' where id=2; -- 修改阻塞，name字段未⽤索引，导致⾏锁升级为表锁
3、session1: commit; --提交事务 或者 rollback 释放读锁
4、session2：update mylock set name='y' where id=2; --修改成功
            Query OK, 1 row affected (0.00 sec)
            Rows matched: 1 Changed: 1 Warnings: 0

InnoDB⾏写锁演示：

1、session1: begin;--开启事务未提交
             select * from mylock where id=1 for update; --⼿动加id=1的⾏写锁
2、session2：select * from mylock where id=2; -- 可以访问
3、session2: select * from mylock where id=1; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读锁被阻塞
5、session1：commit; -- 提交事务 或者 rollback 释放写锁
5、session2：执⾏成功

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

乐观锁和悲观锁

乐观锁和悲观锁都是为了解决并发控制问题，乐观锁可以认为是一种在最后提交的时候检测冲突的手段，而悲观锁则是一种避免冲突的手段。

【乐观锁】应用系统层面和数据的业务逻辑层次上的（实际上并没有加锁，只不过大家一直这样叫而已），利用程序处理并发。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

【乐观锁的版本号机制】在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。（这种方式类似SVN、Git 版本管理系统）

【乐观锁的时间戳机制】时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。

【悲观锁】它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。共享锁和排它锁是悲观锁的不同的实现。

与乐观锁相对应的，悲观锁是由数据库自己实现，要用的时候直接调用数据库的相关语句就可以。

要使用悲观锁，必须关闭mysql数据库的自动提交属性。set autocommit=0;

悲观锁的流程如下：在对任意记录进行修改前，先尝试为该记录加上排他锁。如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。其间如果有其他对该记录做修改或加排他锁的操作，都会等待解锁或直接抛出异常。

共享锁和排他锁

也就是就是读锁和写锁。

共享锁（读锁）：不堵塞，多个用户可以同时读一个资源，互不干扰。允许其他线程上读锁，但是不允许上写锁;
排他锁（写锁）：不允许其他线程上任何锁。一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
死锁：指两个事务或者多个事务在同一资源上相互占用，并请求对方所占用的资源，从而造成恶性循环的现象。

InnoDB 默认的修改数据语句：update,delete,insert 都会自动给涉及到的数据加上排他锁，select语句默认不会加任何类型的锁。

如果手动加排他锁，可以使用 select ...for update语句；手动加共享锁，可以使用 select ... lock in share mode语句。

所有加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

关于共享锁和排他锁的演示：

# 共享锁锁定的资源可以被其他用户读取，但不能修改。在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

# 给表加上加共享锁;
LOCK TABLE users READ; 

# 当对数据表加上共享锁的时候，该数据表就变成了只读模式，此时更新数据就会报错：
UPDATE users SET name = 'hello' WHERE id = 1; # ERROR 1099 (HY000): Table 'users' was locked with a READ lock and cant be updated

# 对表上的共享锁进行解锁
UNLOCK TABLE; 

# 给某一行加上共享锁，排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; 

# 给表添加排它锁，只有获得排它锁的事务可以进行查询或修改，其他事务如果想要查询数据，则需要等待。
LOCK TABLE users WRITE; 

# 释放掉排它锁
UNLOCK TABLE;

# 在某个数据行上添加排它锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

关于死锁的演示：

1、session1: begin;
             update mylock set name='m' where id=1; —-开启事务，更新id为1的数据，未提交
2、session2: begin;
             update mylock set name=’n' where id=2; —-开启事务，更新id为2的数据，未提交
3、session1: update mylock set name=’abc' where id=2; —- 更新id为2的数据，加写锁，被阻塞…
4、session2: update mylock set name=’def' where id=1; —- 更新id为1的数据，加写锁，此时出现死锁，终止！然后session1可以继续commit完成事务。