SQL 事务与锁 详解数据库白水的博客-

本篇博客旨在记录数据库中事务与锁机制的必要性，记录了如何在数据库中使用事务与锁机制实现数据库的一致性以及并发性。

1. 事务机制

1.1. 事务是什么

事务通常包含一系列更新操作，这些更新操作是一个不可分割的逻辑工作单元。如果事务成功执行，那么该事务中所有的更新操作都会成功执行、并将执行结果提交到数据库文件中，成为数据库永久的组成部分。如果事务中某条更新操作执行失败，那么事务中的所有操作均被撤销。

这个性质叫做事务的原子性，即事务操作是打包执行的，一个不成功就全部不成功，要成功就全部成功。

1.2. 事务的必要性

举一个容易理解的例子，对于银行系统而言，转账业务是银行最基本的、且最常用的业务，转账通常是在两个账户之间进行的，如果一个人把钱转出去了，另一个人收款却失败了，就会导致钱白白丢失，这肯定是不能接收的，因此有必要将转账业务封装成存储过程，该存储过程作为一个整体要么转账和收款都成功，要么转账和收款都失败，调用该存储过程后即可真正实现两个银行账户间的转账而不会造成损失。

1.3. 在MySql中关闭自动提交 autocommit

关闭自动提交的方法有两种: 一种是显式地关闭自动提交，一种种是隐式地关闭自动提交。

显式关闭自动提交
使用MySQL命令set autocommit=0;，即可显示地关闭MySQL自动提交。
如图，

隐式关闭自动提交
使用MySQL命令start transaction;可以隐式地关闭自动提交。隐式地关闭自动提交，不会修改系统会话变量@@autocommit的值。
例如下例SQL语句：

-- 查看autocommit的值 select @@autocommit; -- 开启事务 start transaction; -- sql语句 insert into prot_user values ('111', 'AAA'); -- 提交 commit; -- 查看autocommit的值 select @@autocommit; 

执行的结果是，事务中的sql语句被成功执行，而且两次查询autocommit的值都是1。这就是隐式关闭自动查询，不会修改autocommit的值。

1.4. 回滚 rollback 与 保存点 savepoint

关闭MySQL自动提交后，就可以使用rollback关键字根据需要回滚(撤销)整个事务，或者回滚到保存点，也就是恢复数据库之前的状态。

事务的原子性，就是通过保存点(也称为检查点)实现的。使用MySQL命令savepoint [保存点名];可以在事务中设置一个保存点，使用MySQL命令rollback to savepoint [保存点名];可以将事务回滚到保存点状态，例如。

create procedure test() begin start transaction; set @temp = '0'; savepoint sp1; -- 定义保存点 -- sql语句 select prot_user.user_name into @temp from prot_user where prot_user.user_name="bais"; if @temp='0' then rollback to savepoint sp1; -- 回滚到保存点 else select @temp; end if; -- 提交 commit; end 

需要说明的是，假设有一个保存点B，rollback to savepoint B仅仅是让数据库回到事务中的某个”一致性状态B”，而”一致性状态B”仅仅是一个”临时状态”，该”临时状态”并没有将更新回滚，也没有将更新提交，如图所示。

事务回滚必须借助于rollback;(而不是rollback to savepoint B;)，事务的提交需借助于commit;。

使用MySQL命令release savepoint [保存点名];可以删除一个事务的保存点。如果该保存点不存在，该命令将出现错误信息: ERROR 1305 (42000): SAVEPOINT does not exist。如果当前的事务中先后定义了两个相同名字的保存点，旧保存点将被自动丢弃。

1.5. 提交 commit

MySQL自动提交一旦关闭，要是SQL语句正在能操作数据库就需要手动“提交”更新语句，才能将更新结果提交到数据库文件中，成为数据库永久的组成部分。

MySQL的手动提交方式也分为显式提交与隐式提交。

显式提交:
MySQL自动提交关闭后，使用MySQL命令commit;可以显示地提交更新语句。
例如：

-- 将自动提交关闭 set autocommit = 0; insert into prot_user values ('12141', 'AAA'); -- 显式提交 commit; 

执行sql语句后，数据成功插入数据库中。

隐式提交:
MySQL自动提交关闭后，使用下面的MySQL语句，搭配上数据定义语句，可以隐式地提交更新语句。

begin; set autocommit = 1; start transaction; rename table; truncate table; -- 数据定义语句 create alter drop create database ... create table ... create index ... create function ... create procedure ... alter table ... alter function ... alter procedure ... drop database ... drop table ... drop function ... drop index ... drop procedure ... 

1.6. 开启事务

使用MySQL命令start transaction;可以开启一个事务，该命令开启事务的同时，会隐式地关闭MySQL自动交。

2. 锁机制

2.1. 锁机制的必要性

同一时刻，多个并发用户同时访问同一个数据时，仅仅通过事务机制，无法保证多用户同时访问同一数据的数据一致性，有必要引入锁机制实现MySQL的并发访问，锁机制是实现多用户并发访问的基石。

也就是说，并发用户访问同一数据，锁机制可以避免数据不一致问题的发生。以场景描述为例。

2.2. 锁基础

1、锁的粒度
锁的粒度是指锁的作用范围。InnoDB存储引擎支持表锁以及行级锁。

2、隐式锁与显式锁
MySQL锁分为隐式锁以及显式锁。

MySQL自动加锁称为隐式锁；数据库开发人员手动加锁称为显式锁。

3、锁的类型
锁的类型包括读锁(read lock)和写锁(write lock)其中读锁也称为**（表级）共享锁**，写锁也称为**（表级）排他锁或者独占锁**。

-- 用共享锁锁表，会阻碍其他事务修改表数据 lock table [表名] read; -- 用排他锁锁表，会阻碍其他事务查询和修改 lock table [表名] write; 

加读锁和写锁的处理过程，如下两图所示：

4、锁的钥匙
多个MySQL客户机并发访问同一个数据时，如果MySQL客户机A对该数据成功地施加了锁，那么只有MySQL客户机A拥有这把锁的”钥匙”，也就是说:只有MySQL客户机A能够对该锁进行解锁操作。

解锁有两种方式：
第一种是kill锁的进程，可以用如下代码实现：

-- 查找锁进程 -- 如果有SUPER权限可以看到所有进程，否则只能看到自己的进程 show processlist; -- 解锁锁进程 kill [锁进程的id]; 

第二种是解锁表

-- 查询是否有锁表 show open tables where in_use > 0; -- 解锁表 unlock [表名]; 

5、锁的生命周期
锁的生命周期是指在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔。

2.2. 行级锁

2.2.1. 共享锁与排他锁

InnoDB提供了两种类型的行级锁，分别是 （行级）共享锁(S) 以及 （行级）排他锁(X)，其中共享锁也叫读锁，排他锁也叫写锁。在查询(select) 语句或者更新(insert、update以及delete)语句中，为受影响的记录施加行级锁的方法也非常简单。

由于共享锁和排他锁的自身特性相互矛盾，因此不能在同一数据上同时加上共享锁和排他锁。

使用下面的语句可以添加共享锁：

[sql语句] lock in share mode; 

例如：

-- 对test_table表中id=1的行进行共享查询 -- 在解锁前其他事务不能对数据进行修改 select * from test_table where id=1 lock in share mode; 

写锁(s)也称排它锁，同一时刻只能有一个事务拥有排它锁，其它事务不能拥有共享锁和排它锁。

需要注意的是：InnoDB引擎insert、update、 delete会自动给涉及的数据加排他锁(X)，这样的排他锁叫做隐式排他锁。而对于一般的select语句，不会加任何锁，因此一般情况下只对select语句加排他锁。

使用下面的语句可以添加排他锁：

[sql语句] for update; 

例如：

-- 对test_table表中username="a"的行进行排他查询 -- 在解锁前其他事务不能对数据进行查询和修改 select * from test_table where username="a" for update; 

行级锁与索引之间的关系
InnoDB表的行级锁是通过对”索引”施加锁的方式实现的，这就意味着：只有通过索引字段检索数据的查询(select)语句或者更新(insert、update、 delete)语句，才可能施加行级锁；否则InnoDB将使用表级锁，使用表级锁势必会降低InnoDB表的并发访问性能。

2.2.2. 意向锁

意向锁主要是运用在如下场景：MySQL客户机A获得了某个InnoDB表中若干条记录的行级锁，此时MySQL客户机B出于某种原因需要向该表显式地施加表级锁(使用lock tables命令)，MySQL客户机B为了获得该表的表级锁，需要逐行检测表中的行级锁是否与表级锁兼容，而这种检测需要耗费大量的服务器资源。

如果MySQL客户机A获得该表若干条记录的行级锁之前，MySQL客户机A直接向该表施加一个”表级锁” (这个表级锁是隐式的，也叫意向锁)，MySQL客户机B仅仅需要检测自己的表级锁与该意向锁是否兼容，无需逐行检测该表是否存在行级锁，就会节省不少服务器资源。

为此，MySQL提供了两种意向锁：意向共享锁(IS) 和 意向排它锁(IX)

1. 意向共享锁（IS）
   向InnoDB表的某些记录施加行级共享锁时，InnoDB存储引擎会自动地向该表施加意向共享锁(IS)。也就是说，执行select * from [表名] where [条件] lock in share mode;后，InnoDB存储引擎在为表中符合[条件]的记录施加共享锁前，自动地为该表施加意向共享锁(IS)；
2. 意向排他锁（IX）
   向InnoDB表的某些记录施加行级排它锁时，InnoDB存储引擎会自动地向该表施加意向排它锁(IX)。也就是说：执行更新语句(例如insert、 update或者delete语句)或者select * from [表名] where [条件] for update;时，InnoDB存储引擎在为表中符合[条件]的记录施加排他锁前，自动地为该表施加意向排它锁(lX)。

2.2.3. 死锁与等待锁

默认情况下，InnoDB存储引擎一旦出现锁等待超时异常，便不会自动提交事务，也不会自动回滚事务，而这是十分危险的。为了避免锁等待超时异常，应该自定义错误处理程序，由程序开发人员选择进一步提交事务，还是回滚事务。

2.2.4. 悲观锁与乐观锁

悲观锁：就是单独使用排它锁锁住记录select * from [表名] where [条件] for update;，这样的事务就不能修改这条记录了。

加了悲观锁后，一定要等到一个数据操作结束，才可以进行下一个数据操作，不支持并发，会造成锁等待导致数据库效率降低。

乐观锁：乐观锁与悲观锁相反，它可以支持”并发”操作——乐观锁在这条记录上加一个version字段，更新的时候就+1，select的时候带出这个字段，当实际更新的时候判断当前version是不是等于记录中的version，是则表示执行成功，反之失败并回退。

begin; set @ver = 0; select version into @ver from test where id = 1 ; select @ver; update test set name='b',version=version+1 where id=1 and version=@ver; commit; 

3. 事务的ACID特性

事务的任务是保证一系列更新语句的原子性，锁的任务是解决并发访问可能导致的数据不一致问题。如果事务
与事务之间存在并发操作，此时可以通过隔离级别实现事务的隔离性，从而实现数据的并发访问。

3.1. ACID特性

所谓的ACID特性，就是

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；
2. 一致性（Consistency）
   事务必须便数据库从一个一致性状态变换到另外；
3. 隔离性（Isolation）
   事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离，通过锁机制实现；
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

3.2. 事务的隔离级别与并发问题

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。如果不考虑隔离性，可能会引发如下问题:

1. 更新丢失(Lost Update)
   当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题，类比github的提交冲突。
2. 脏读(Dirty Reads )
   脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，外一个事务也访问这个数据，然后使用了这个数据。
   举一个生动的例子：公司发工资了，领导把5000元打到小白的账号上，但是该事务并未提交，而小白正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给小白的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，再将事务提交，最后小白实际的工资只有2000元，小白空欢喜一场。
3. 不可重复读(Non-Repeatable Reads)
   是指在一个事务内， 多次读词-数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间，由于第二个事务的修改, 那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不-样的，因此称为不可重复读。(即不能读到相同的数据内容)。
   举个例子：小白拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把小白工资卡的2000元转到另一账户，并在小白之前提交了事务，当小白扣款时， 系统检查到小白的工资卡已经没有钱，扣款失败。
4. 幻读( Phantom Reads )
   是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
   举个例子：小白的老婆工作在银行部门，她时常通过银行内部系统查看小白的信用卡消费记录。有一天，她正在查询到小白当月信用卡的总消费金额select sum(amount) from transaction where month="本月";为80元，而小白此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录insert transaction;，并提交了事务，随后小白的老婆将小白当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，小白的老婆很诧异，以为出现了幻觉，幻读就这样产生了。

为了区分避免上述问题，SQL标准定义了四种隔离级别:

1. 0 Read Uncommitted (读取未提交的数据)
   在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。该隔离级别很少用于实际应用，并且它的性能也不比其他隔离级别好多少，会导致脏读。
2. 1 Read Committed (读取提交的数据)
   这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。只有事物A提交了数据，事物B才能读取到。它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。当隔离级别设置为Readcommitted时，避免了脏读，但是可能会造成不可重复读。
3. 2 Repeatable Read (可重复读)
   这是MySQL默认的事务隔离级别，它确保同一事务内相同的查询语句，执行结果一致。当一个事务开始操作某个数据时，该数据就不可被其他事务修改，但这个级别可能会出现幻读现象。
4. 3 Serializable (串行化)
   该级别不允许读写并发操作，写执行时，读必须等待。这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突。换言之，它会在每条select语句后自动加上lock in share mode，为每个查询操作施加一个共享锁。在这个级别，可能导致大量的锁等待现象。该隔离级别主要用于InnoDB存储引擎的分布式事务。

四种隔离级别逐渐增强，其中Read Uncommitted的隔离级别最低，Serializable的隔离级别最高。读未提交、读已提交、可重复读和串行化也可以用数字0、1、2和3来表示。

用一张表来表示如下：

四个隔离级别可以通过下面的代码进行查看和设置

-- 查看隔离级别 show [global] variables like 'transaction_isolation'; -- 或 select @@transaction_isolation; -- 设置隔离级别 set [session|global] transaction_isolation=[0|1|2|3]; 

例如

-- 查看隔离级别 show global variables like 'transaction_isolation'; -- 或 select @@transaction_isolation; -- 设置隔离级别为0级 -- 此时事务还未提交，查询就能将数据读取出 set global transaction_isolation=0; 

3.3. 不可重复读与幻读的区别

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update，而幻读的重点在于insert和delete。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

3.4. 如何设置事务的隔离级别

合理地设置事务的隔离级别，可以有效避免脏读、不可重复读、幻读等并发问题。

出于性能考虑，都是使用了以乐观锁为理论基础的MVCC(多版本并发控制)来避免这两种问题。

在MySQL中不可重复读和幻读的解决办法是使用MVCC(多版本并发控制)保证了数据的可重复读，也保证了不会读到幻读数据(即使是有新的符合条件的数据产生，在同一个事务的下次查询中也查不到，蒙蔽自己的双眼假装看不到)。

在MySQL的InnoDB存储引擎中，MVCC中普通方式select * from table查询数据是不加任何锁的，数据的筛选除了通过查询条件之外，还要根据数据行的隐藏字段(两个版本号)来和事务的版本号来进行比较后过滤。这样做的好处是支持的并发量更高(因为不加锁)，根据版本号来过滤数据也解决了不可重复读的问题，也能保证不会读到幻读数据。

record lock(记录锁)和gap lock(间隙锁)保证了幻读数据不会产生(在读取数据的时候加锁,防止在读取时有其他事务对读取条件内的数据做增删改操作)也就是说如果要完全解决幻读问题，还要在查询语句中使用像lock in share mode和for update这样显式的加锁语句