第十一单元事务与锁

create database step2_unit13;
go
use step2_unit13;
go
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY identity,
NAME VARCHAR(10),
balance decimal(10,2)
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

1. 应用场景说明

什么是事务：在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转

账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的

SQL 语句都要回滚，整个业务执行失败。

模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：

张三账号-500

李四账号+500

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中

一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

案例演示 1：事务提交

模拟张三给李四转 500 元钱（成功）

begin transaction -- tran,开启事务
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
commit; -- 提交

案例演示 2：事务回滚

模拟张三给李四转 500 元钱（失败）

begin transaction -- tran,开启事务
begin try
    -- 张三账号-500
    update account set balance = balance - 500 where name='张三';
    -- 李四账号+500
    update account set balance = balance + 500 where name='李四';
    commit; -- 提交
end try
begin catch
    rollback;-- 回滚
end catch

总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

2. 事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表

中，其他任何情况都会清空事务日志(rollback，断开连接)

事务的执行步骤

客户端连接数据库服务器，创建连接时创建此用户临时日志文件
开启事务以后，所有的操作都会先写入到临时日志文件中
所有的查询操作从表中查询，但会经过日志文件加工后才返回
如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

3. 四大特性

原子性,一致性,隔离性,持久性（ACID）

Atomic（原子性）：事务中包含的操作被看做一个逻辑单元，这个逻辑单元中的操作要么全部成功，要么全部失败，保证数据的完整性。
Consistency（一致性）：事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。如：转账前2个人的总金额是2000，转账后2 个人总金额也是 2000。
Isolation（隔离性）：事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立。
Durability（持久性）：事务结束后，事务处理的结果必须能够得到固化(永久存储到数据库中了)。就算断电了，也是保存下来的。

1. 事务的隔离级别

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个

数据。可能引发并发访问的问题：

并发访问问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致

数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

级别	名称	隔离级别	脏读	不可重复读	幻读
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是
3	可重复读	repeatable read	否	否	是
4	串行化	serializable	否	否	否
2-3 之间	快照	SNAPSHOT	否	否	否

隔离级别越高，性能越差，安全性越高。

2. 事务快照

SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离（可以把事务已经提交的行的上一版本保存在TEMPDB数据库中） SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似 READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似不过在快照隔离级别下读操作不需要申请获得共享锁，所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性；如果目前版本与预期的版本不一致，读操作可以从TEMPDB中获取预期的版本。

如果启用任何一种基于快照的隔离级别，DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中，而INSERT语句不需要在TEMPDB中进行版本控制，因为此时还没有行的旧数据

undo.log

事务Id	数据Id	数据版本
1	1	1（快照）
2	1	2(如果版本不一致，则事务会获取版本1那条数据，也就是快照)

作用：

1 读操作时不会陷入block 和死锁的问题中，SNAPSHOT 本身提高了数据库系统的事务处理的性能。

2 避免了脏读，非一致性读，以及丢失更新，和不可重复读等多个问题

4. 隔离级别演示

查看当前的隔离级别

DBCC USEROPTIONS

1. 脏读的演示

设置隔离级别为 read uncommit;

-- set transaction isolation level <隔离级别>

先将两个账号的金额都恢复成1000元。

update account set balance=1000;

打开 A 窗口登录，设置隔离级别为最低
```
set transaction isolation level read uncommitted;
```

打开 B 窗口,AB 窗口都开启事务

set transaction isolation level read uncommitted;
begin transaction;

A 窗口更新 2 个人的账户数据，未提交

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

B 窗口查询账户

A 窗口回滚
```
rollback;
```
B 窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱

已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升

将数据进行恢复：

UPDATE account SET balance = 1000;

在 A 窗口设置全局的隔离级别为 read committed
```
set tran isolation level read committed;
```

A 更新 2 个人的账户，未提交

begin transaction;
update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

B 窗口查询账户 , 接着A 窗口再 commit 提交事务

select * from account; -- sqlserver 2017 会一直等待account释放，直到A窗口commit之后。

2. 不可重复读演示

设置隔离级别为read commited

set tran isolation level read committed;

将数据进行恢复：

UPDATE account SET balance = 1000;

B窗口开启事务,查询数据，先不提交
```
begin tran;
select * from account;
```

在 A 窗口开启事务，并更新数据

begin tran;
update account set balance=balance+500 where id=1;
commit;

B 窗口查询,发现与上一次读取的结果不同。
```
select * from account;
commit;
```

解决不可重复读的问题：

将隔离级别进行提升为：repeatable read

将数据进行恢复：

UPDATE account SET balance = 1000;

A 窗口设置隔离级别为：repeatable read
```
set tran isolation level repeatable read;
```
B窗口开启事务,查询数据，先不提交
```
begin tran;
select * from account;
```

在 A 窗口开启事务，并更新数据, 此时你会发现，事务会一直处于等待状态，无法提交，直到B窗口中的事务提交完毕。

begin tran;
-- 事务会一直处于等待状态，无法提交，直到B窗口中的事务提交完毕。
update account set balance=balance+500 where id=1; 
commit;

B 窗口提交事务

commit; -- 此时A窗口中的事务也会紧跟着提交

3. 幻读的演示

在SQL Server中，幻读无法演示（一个事务中两次读取到的数据数量不一致），但是随着事务隔离级别的提升，如果某个事务在update 数据之后未提交，则另外一个事务无法对当前资源进行操作，直到资源释放。

4. 事务快照演示

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别使用的不是共享锁,而是行版本控制（乐观锁）。使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项：

ALTER DATABASE step2_unit13 SET ALLOW_SNAPSHOT_ISOLATION ON;

将数据进行恢复：

update account set balance=1000;

A窗口打开事务，并更新数据，不提交(隔离级别为默认的read committed)

begin tran;
update account set balance-=500 where id=1;
update account set balance+=500 where id=2;
select * from account

B窗口打开事务，读取account,不提交

-- 设置隔离级别
set tran isolation level snapshot;
begin tran;
select * from account;

A 窗口 commit
```
commit;
select * from account
```
B窗口commit(提交之前再查询一次，发现数据是一致的)
```
select * from account
commit;
select * from account
```

5. 锁

锁是多用户访问同一数据库资源时，对访问的先后次序权限管理的机制。

锁的分类：

共享锁
排他锁
更新锁
架构锁
意向锁
大容量更新锁

悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

1. 行锁

关键字：rowlock

select * from account rowlock where id=1

2. 表锁

关键字：tableLockX

select * from account tableLockX where id=2

3. 共享锁

共享锁是用于读取数据操作，允许多个事务读取相同的数据，但不允许其他事务修改当前数据

-- 窗口1（添加共享锁）
begin tran
select * from account with(holdlock) where id=1;
waitfor delay '00:00:10';
commit;

-- 窗口2（可读）
begin tran
select * from account where id=1;
commit;

-- 窗口3（等待窗口1结束，才可执行）
begin tran
update account set balance=500 where id=1;
commit;

4. 排它锁

又称为独占锁，当事务对数据资源进行增删改操作时，不允许其它任何事务操作这块资源（可以读取）。防止对同一资源进行多重操作。

-- 窗口1
begin tran;
update account set balance=500 where id=1; -- 更新锁自动转换为排它锁
-- 等效于 update account with(updlock) set balance=500 where id=1
waitfor delay '00:00:10';
commit;

-- 窗口2(可读)
begin tran;
select * from account
commit;

-- 窗口3（等待窗口1结束才会执行）
begin tran
update account set balance=1500 where id=1;
commit;

5. 更新丢失

第一类丢失更新（通过设置隔离级别可以防止 Repeatable Read）

A事务撤销时，把已经提交的B事务的更新数据覆盖了。这种错误可能造成很严重的问题，通过下面的账户取款转账就可以看出来：

时间	取款事务A	转账事务B
`T1`	开始事务
`T2`		开始事务
`T3`	查询账户余额为1000元
`T4`		查询账户余额为1000元
`T5`		汇入100元把余额改为1100元
`T6`		提交事务
`T7`	取出100元把余额改为900元
`T8`	撤销事务
`T9`	余额恢复为1000 元（丢失更新）

A事务在撤销时，“不小心”将B事务已经转入账户的金额给抹去了。

第二类丢失更新（需要应用程序控制，乐观锁）

A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失：

时间	转账事务A	取款事务B
T1		开始事务
T2	开始事务
T3		查询账户余额为1000元
T4	查询账户余额为1000元
T5		取出100元把余额改为900元
T6		提交事务
T7	汇入100元
T8	提交事务
T9	把余额改为1100 元（丢失更新）

上面的例子里由于支票转账事务覆盖了取款事务对存款余额所做的更新，导致银行最后损失了100元，相反如果转账事务先提交，那么用户账户将损失100元。

更新锁

上述更新丢失问题目前无法重现，因为系统在执行update 语句时，会自带更新锁，在更新数据时，会将更新锁自动转换为排它锁（别人只能读）。

update account with(updlock) set balance=1000 where id=1

6. 死锁

在多个任务中，每个任务锁定了其他任务试图锁定的资源，会造成这些任务永久阻塞，从而出现死锁，此时系统处于死锁状态。

-- 窗口1(发生死锁)
begin tran;
    update account set balance=500 where id=1;
    waitfor delay '0:0:10'
    update account set balance=1500 where id=2;
commit;

-- 窗口2（执行成功）
begin tran;
    update account set balance=1500 where id=2;
    waitfor delay '0:0:8'
    update account set balance=500 where id=1;
commit;

如果先执行窗口2，窗口1会发生死锁, 窗口1中事务所要的资源正好被窗口2中的事务锁住了。

(1 行受影响) 消息 1205，级别 13，状态 51，第 4 行事务(进程 ID 51)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

如何减少死锁

在所有事务中以相同的次序使用资源
使事务尽可能简短并且在一个批处理中
避免在事务内和用户进行交互，减少资源的锁定时间
为死锁超时参数设置一个合理范围

6. 事务与锁的关系

事务与锁是不同的。

事务具有ACID（原子性、一致性、隔离性和持久性），锁是用于解决隔离性的一种机制。
事务的隔离级别通过锁的机制来实现。
另外锁有不同的粒度，同时事务也是有不同的隔离级别的（一般有四种：读未提交Read uncommitted，读已提交Read committed，可重复读Repeatable read，可串行化Serializable）。

在具体的程序设计中，开启事务其实是要数据库支持才行的，如果数据库本身不支持事务，那么仍然无法确保你在程序中使用的事务是有效的。锁可以分为乐观锁和悲观锁：悲观锁：认为在修改数据库数据的这段时间里存在着也想修改此数据的事务；乐观锁：认为在短暂的时间里不会有事务来修改此数据库的数据；我们一般意义上讲的锁其实是指悲观锁，在数据处理过程中，将数据置于锁定状态（由数据库实现）。回到你的问题，如果开启了事务，在事务没提交之前，别人是无法修改该数据的；如果rollback，你在本次事务中的修改将撤消（不是别人修改的会没有，因为别人此时无法修改）。当然，前提是你使用的数据库支持事务。还有一个要注意的是，部分数据库支持自定义SQL锁覆盖事务隔离级别默认的锁机制，如果使用了自定义的锁，那就另当别论。重点：一般事务使用的是悲观锁（具有排他性）。

配套视频链接：SQL Server高级编程 - 网易云课堂 (163.com)

posted @ 2023-12-19 09:20 誉尚学教育阅读(203) 评论(0) 编辑收藏举报

刷新页面返回顶部

誉尚学教育

海阔凭鱼跃，天高任我行

第十一单元 事务与锁

1. 应用场景说明

2. 事务原理

3. 四大特性

1. 事务的隔离级别

2. 事务快照

4. 隔离级别演示

1. 脏读的演示

2. 不可重复读演示

3. 幻读的演示

4. 事务快照演示

5. 锁

1. 行锁

2. 表锁

3. 共享锁

4. 排它锁

5. 更新丢失

更新锁

6. 死锁

如何减少死锁

6. 事务与锁的关系

第十一单元事务与锁