键盘敲烂,月薪过万作业不做,等于没学
当前系列: SQL 修改讲义

事务四大特性

以“转账”为例,实际上是两条UPDATE语句:

UPDATE User SET Balance = Balance + 150 WHERE Id = 1
UPDATE User SET Balance = Balance - 150 WHERE Id = 6
一旦中间出现故障(比如网络故障/系统断电……),就会造成 Id=1 的用户余额增加而 Id=3 的用户没有减少的情况。

解决的办法就是将上述两条语句放到一个事务中。

BEGIN TRANSACTION        -- 开始事务
UPDATE User SET Balance = Balance + 199 WHERE Id = 1
UPDATE User SET Balance = Balance - 199 WHERE Id = 6       -- 因违法CHECK(Balance>0)约束而报错
COMMIT TRANSACTION       -- 提交事务

说明事务的:(记忆小窍门:ACID

  1. 原子性(Atomicity):事务作为一个整体不可再分。事务中的所有内容,要完全部完成,要么全部不完成;不能一部分完成一部分没完成。
  2. 一致性(Consistency):事务执行前后,数据的状态都要保证一致(正确,符合逻辑和预期)。
  3. 隔离性(Isolation):正在执行的事务不能被其他事务干扰。
  4. 持久性(Durablity):一旦完成(无论COMMIT还是ROLLBACK),改变就是永久性的。COMMIT和ROLLBACK

实际上,各种关系数据库对上述1、2、4条要求都执行得比较严格,但对于3隔离性因为并发复习的原因,不得不做出妥协。

为了提高数据库性能,就要允许数据库被并发的访问,就不能严格遵守事务隔离性要求,所以数据库是允许在一个事务的进行过程中,有另一个事务同时进行的。

数据库一般也允许用户自行设置数据库事务的


隔离级别

#冲击12K,^_^

如下表所示:

隔离级别
解决的问题
读未提交
丢失的更新
读已提交
脏读
可重复读
不可重复读 
序列化/串行化
幻影读

从上到下:

  • 事务隔离级别:越来越高
  • 隔离性越来越好,执行效率越来越低
  • 下一个隔离级别就能解决上一个隔离级别的问题

演示准备

两个连接窗口,每个窗口都可以启动事务。

说明:一个窗口(session/query)中的多次操作会形成并发。

在当前连接中:

  • 设置隔离级别:
    SET TRANSACTION ISOLATION LEVEL isolation_level
  • 查看的事务隔离级别:
    T-SQL mysql
    DBCC USEROPTIONS
    SELECT @@transaction_isolation;

另外,任何单条SQL语句,都自动被包裹在事务中执行:

UPDATE Student SET Age = 66 WHERE Id = 1; 
-- 等于
BEGIN TRAN
UPDATE Student SET Age = 66 WHERE Id = 1; 
COMMIT

依次演示

先设置事务隔离级别为:

READ UNCOMMITTED

保证不会出现:丢失的更新(后更新会覆盖之前的更新)

  1. 事务1中更新一条数据,未提交
  2. 事务2试图更新该条数据:无法更新,需要等待,直到事务1完成(提交/回滚)

但是,会出现:脏读(Dirty Read)

  1. 重复1
  2. 将事务2设置为READ UNCOMMITTED,然后在事务2中查询该条数据:能读取被1修改过后的数据
  3. 事务1回滚,事务2读到的是“脏”(未提交的)数据

将事务2的隔离级别设置成:

READ COMMITTED

就可以保证不会出现:脏读。事务2需等待事务1完成之后才能获得查询结果。

但是会出现:不可重复读 (Unrepeatable Read)

  1. 在事务1中查询一条数据,未提交
  2. 事务2修改该条数据
  3. 在事务1中再次查询该数据,得到和1不一样的结果

#理解#:在同一次事务中,针对同一(行)数据是查询,(按照事务隔离性的要求)应该得到相同的结果。

将事务1的的隔离级别设置为:

REPEATABLE READ

就可以保证不会出现:不可重复读。上述第2步操作无法完成,需要等待事务1完成才能继续进行。

但是会出现:幻影读(Ghost Read)

  1. 事务1中更新全表数据,未提交
  2. 事务2插入一条数据
  3. 事务1提交:发现好像有一条数据未更改?(实际上是2插入的数据)

将事务1的隔离级别给成:

SERIALIZABLE

就可以保证不会出现幻影读。事务2要一直等待事务1完成之后才能执行。


锁实现

#冲击18K,^_^

锁,可以视为对数据的一种标记。大致来说,

  • 在事务执行前,先检查数据有无被加锁
  • 一旦开始读写数据,就加上相应的锁,防止其他事务执行

这样就能实现事务隔离

上述事务隔离级别可以通过对数据:

  • 加不同种类
  • 设定不同的加锁/解锁时间
  • 锁定不同范围的数据

来实现。

强烈建议:在学习之前尽可能的熟练事务隔离级别。

共享和排他锁

共享Shared锁:可用于只读的SELECT语句,所以又称之“读锁”。被加共享锁的数据,不影响其他事务读取。

排他(Exclusive)锁:用于INSERT/UPDATE/DELET语句(以下简称“写数据”),所以又称之“写锁”。

  1. 被加锁的数据,无法再被加锁,
  2. 要操作的数据,哪怕是只有一个共享锁,也无法再加排他锁
所以PPT演示
  1. 写数据前总是(“能+会”)加排他锁,就可以避免:丢失的更新
  2. 读数据不用加共享锁,就可以实现:读未提交
  3. 读数据要加共享锁,就可以实现:读已提交
  4. 一旦读到数据(SELECT执行完成)就解锁,会造成:不可重复读;但直到整个显式事务执行完成才解锁,就可以避免,实现:可重复读

粒度和范围

我们可以把锁加在行上,也可以加在表上,一个锁能“控制”的数据单位,就被称之锁的“粒度”。

一次事务要锁多少数据,3行5行,1张表3张表,这是锁的“范围”。

粒度是数据库锁机制可以决定的,范围是SQL语句(比如WHERE条件)决定的。

@想一想@:粒度是大好,还是小好?

粒度小:并发性更好。比如“锁3行”,肯定比“锁3张表”影响的数据更少,其他事务可以操作的数据更多。

但如果要锁的粒度太小,同样范围,用表锁只需要3个锁,用行锁可能就要几千几万个,加锁解锁性能消耗更多。

#体会#:没有银弹。凡是选择,必有代价……

把加锁的范围扩大到所有相关表,就可以消除幻影读

更新锁

带WHERE子句的UPDATE语句,也会涉及到查询。

@想一想@:这里的查询,加什么锁?

如果使用S锁,就很容易形成死锁

  1. 事务1查询到某数据,加S锁,未提交
  2. 事务2也查询到该数据,也加S锁,未提交
  3. 事务1试图更新该数据,加X锁,不行,因为上面已有事务2的S锁,只能等待事务2解锁
  4. 事务2遇到和步骤3一样的问题,也只能等事务1解锁
  5. 而事务1在等着事务2,事务2又等着事务1
  6. ……

当然加X锁可以避免死锁,但性能不高呀!

所以需要引入更新(Update)锁:

  • 和S锁兼容(能加上,不影响SELECT语句),提高和SELECT的并发性
  • 和U锁/X锁不兼容

在查找过程中使用的就是U锁;一旦查找到需要的数据,U锁就转变成X锁。

死锁

死锁出现条件:

  • 双方均需要对方释放锁为前提,才能继续进行;
  • 而要释放锁,又必须要事务继续进行。

死锁不是等待。而是彻底的锁死在那里了!是无论怎么等待,都没办法活过来的那种……^_^

示范:一个最简单的死锁(注意WHERE条件要使用主键,@想一想@:为什么?

  1. 事务1中,执行:
    BEGIN TRAN
    UPDATE Student SET Score = Score + 10 WHERE Id = 1;
  2. 事务2中,执行
    BEGIN TRAN
    UPDATE BangMoney SET Balance -= 10 WHERE [Name] = N'陈元';
  3. 事务1中,继续执行:
    UPDATE BangMoney SET Balance -= 10 WHERE [Name] = N'陈元';
    提示:执行中……,需要等着了
  4. 事务2中,继续执行:
    UPDATE BangMoney SET Balance += 10 WHERE [Name] = N'幸龙泰';


只能依靠SQL SERVER来自行裁判其中一个事务作为victim,进行回滚。


作业

  1. 给用户(User)添加一列帮帮币(BMoney INT),当用户发布求助(Problem)时会设置悬赏(Reward),于是作者的帮帮币也会相应减少,但作者的帮帮币有不能少于0的约束:请使用事务完成上述SQL语句,提交或回滚
  2. 利用PPT和SQL语句演示各种并发问题,解释/显示事务隔离级别和锁如何应对上述问题。
  3. 思考题:游戏买装备。比如只有100个游戏币,装备30个游戏币一套。恶意用户利用软件,瞬间向服务器发送无数条购买装备的请求。结果就真的到手了n(>3)多装备,这可能是怎么一回事呢?

学习笔记
源栈学历
大多数人,都低估了编程学习的难度,而高估了自己的学习能力和毅力。

作业

觉得很 ,不要忘记分享哟!

任何问题,都可以直接加 QQ群:273534701

在当前系列 SQL 中继续学习:

多快好省!前端后端,线上线下,名师精讲

  • 先学习,后付费;
  • 不满意,不要钱。
  • 编程培训班,我就选源栈

更多了解 加:

QQ群:273534701

答疑解惑,远程debug……

B站 源栈-小九 的直播间

写代码要保持微笑 (๑•̀ㅂ•́)و✧

公众号:源栈一起帮

二维码