1.SQL存储过程和并发控制

【说明】

某企业内部信息系统部分简化后的关系模式如下：员工表：EMPLOYEES(Eid，Ename，Address，Phone，Jid):属性含义分别为：员工编码、员工姓名、家庭住址、联系电话、岗位级别编码。

岗位级别表：JOB_LEVELS(Jid，Jname，Jbase_salary):属性含义分别为：岗位级别编码、岗位名称、岗位基本工资。

员工工资表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，tax，year，month):属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。

该企业在每月25日计算员工的工资。首先是根据考勤系统以及绩效系统中的数据，计算出员工的考勤、绩效和加班工资，存入到员工工资表；其次结合员工的岗位基本工资，计算出最终工资，完成对员工工资表记录的更新。最后依据员工工资表完成工资的发放。

【问题1】(6分)

下面是月底25日计算某员工最终工资的存储过程程序，请补全空缺处的代码。

CREATE PROCEDURE SalaryCalculation((a) empId char(8),IN iYear number(4),IN iMonth number(2)) #括号里面的数字表示参数有几位

DECLARE

attendance number(14,2); #14表示整数部分 + 小数部分最多14位

merit number(14,2); #2表示小数部分最多2位

overtime number(14,2);

base number(14,2);

all_salary number(14,2);

BEGIN

SELECT attendance_wage,merit_pay,overtime_wage INTO (b)

FROM SALARY

WHERE Eid=empld FOR UPDATE;

SELECT Jbase_salary INTO :base FROM EMPLOYEES T1,(c)

WHERE T1.Jid=T2.Jid AND T1.Eid=empld;

all salary :=attendance+merit+overtime+base; #:= 是赋值运算符，和数据库中=号比较的用法区分开

UPDATE SALARY SET salary =:all_salary

WHERE (d) AND year=iYear AND month=iMonth;

（e）;

EXCEPTION WHEN OTHERS THEN

(f);

END;

【问题2】(5分)

为了防止对员工工资表的非法修改（包括内部犯罪），系统特意规定了员工工资表修改的业务规则：对员工工资表的修改只能在每月25日的上班时间进行。

下面是员工工资表修改业务规则对应的程序，请补全空缺处的代码。

CREATE TRIGGER CheckBusinessRule

(g) INSERT OR DELETE OR (h)on SALARY

FOR EACH (i)

BEGIN

IF (TO_CHAR(sysdate,'DD')<>(j))

OR(to_number(TO_CHAR(sysdate,'HH24))

(k) BETWEEN 8 AND 18)THEN

Raise_Error;//抛出异常

END IF;

END;

【问题3】(4分)

人事部门具有每月对员工进行额外奖罚的权限，该奖罚也反应到员工的最终工资上。假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励2000元的事务操作，对应事务的部分调度序列如表4-1所示。

（1）请说明该事务调度存在哪种并发问题？

（2）采用2PL是否可以解决该并发问题？是否会产生死锁？

存储过程基本语法结构

CREATE [OR REPLACE] PROCEDURE 过程名(
    参数模式 参数名 数据类型,
    ...
)
DECLARE / 或者AS / 或者IS #不同数据库声明部分关键词不一样

    -- 声明部分：变量、常量、游标等
BEGIN
    -- 执行部分：SQL语句、逻辑处理
    [COMMIT;]  -- 提交事务
EXCEPTION
    -- 异常处理部分
    WHEN 异常类型 THEN
        处理语句/或[ROLLBACK;]  -- -- 出现异常 → 全部撤销
END;

CREATE PROCEDURE SalaryCalculation((a) empId char(8),IN iYear number(4),IN iMonth number(2))

空缺 (a)：IN

创建存储过程时，参数可以是三种模式中的任意一种，具体取决于需求：

IN 只读输入传入值
OUT 只写输出把计算结果返回给调用者
IN OUT 可读可写传入初始值，过程处理后返回新值

判断为什么是IN，1️⃣从使用用途角度考虑

题目已经说明了，这个过程的目的是更新数据库中的数据，不需要把结果返回给调用者

这个存储过程是"月底计算某员工最终工资"，调用时应该是：

SalaryCalculation('EMP001', 2024, 12);

三个值都是调用者传入的输入，不需要过程返回任何结果给调用者。所以三个参数都应该是 IN。

2️⃣从题目内容判断：

WHERE Eid = empId FOR UPDATE;           -- 第1处
WHERE T1.Jid = T2.Jid AND T1.Eid = empId; -- 第2处
WHERE Eid = empId AND year=iYear AND month=iMonth; -- 第3处

empId 在这三处都是出现在等号右边，作为查询条件使用。它只是被读取，从未被赋新值。和 iYear、iMonth 完全一样，这不应该是要被返回的。

SELECT INTO

空缺(b)：:attendance，:merit，:overtime

SELECT attendance_wage,merit_pay,overtime_wage INTO (b)

FROM SALARY

这里是将查询到的结果赋予给变量，然后这些变量要参与后续的其他计算

SELECT 列1, 列2, 列3 INTO 变量1, 变量2, 变量3
FROM 表名
WHERE 条件;

注意题目已经给出了英文的对应说明
员工工资表：SALARY(Eid，attendance_wage，merit_pay，overtime_wage，salary，tax，year，month):属性含义分别为：员工编码、考勤工资、绩效工资、加班工资、最终工资、税、年份、月份。

从员工工资表选取这三项付给题目中意思对应的三个变量

冒号：表示这三个变量，对于这个SQL语句而言是外部的变量。SQL语句是BEGIN才开始作用的，它并不知道我们在declare声明的变量是什么东西。在一些数据库中是不需要这么做的，但是考试环境还是要注意如果题目用了答案也要跟着用。

多表关联查询

空缺（c）JOB_LEVELS T2

SELECT Jbase_salary INTO :base

FROM EMPLOYEES T1, (c)

WHERE T1.Jid = T2.Jid AND T1.Eid = empld;

Jbase_salary（岗位基本工资）在 JOB_LEVELS 表中，这个表只有岗位的级别、岗位的名称、岗位的工资。

要找到员工的工资，必须先通过这个员工找到它的岗位才会获取岗位的通知，观察两张表发现jid是共有的属性。

然后根据题目可以得知T1是 EMPLOYEES这个表，那么连接条件中的T2肯定是JOB_LEVELS了。题目中的EMPLOYEES T1实际上是别名AS省略了，同理得答案，JOB_LEVELS T2

all_salary := attendance + merit + overtime + base;

这个语句是独立的一句，总工资等于考勤+绩效+加班+底薪，

每次调用这个过程的时候，其中的变量在前面的语句中被赋值，然后这句话单独计算出总工资，后面再拿总工资取写入修改表。

空缺（d）eid= empld

UPDATE SALARY SET salary =:all_salary

WHERE (d) AND year=iYear AND month=iMonth;

（e）;

要把where判断条件相符的行，其中的薪水更新为计算后的结果。

这里看到where的都是传入的条件来作判断，所以是SALARY中的eid= empld。

FOR UPDATE和COMMIT/ROLLBACK

（e）COMMIT

（f）ROLLBACK

（e）;

EXCEPTION WHEN OTHERS THEN

(f);

END;

在前面的行中，UPDATE 已经执行了，但修改还在事务中，没有永久保存。且FOR UPDATE 会加行锁。

锁只有 COMMIT 或 ROLLBACK 才能释放

所以 (e) 和 (f) 必然是这两个事务控制命令之一。

SELECT attendance_wage,merit_pay,overtime_wage INTO (b)
FROM SALARY
WHERE Eid=empld FOR UPDATE;

FOR UPDATE 是 SELECT 语句的最后一个子句，必须放在 WHERE 条件之后。这是语法规定，不是随意放的。
字面意思就是我要查这些数据，是为了更新它们，数据库需要上锁保护，以免被其它人修改了。

OTHERS 是一个通配符，代表"所有没有在前面被显式捕获的异常"。

EXCEPTION WHEN OTHERS THEN

ROLLBACK;

触发器之UPDATE ON

CREATE [OR REPLACE] TRIGGER 触发器名
触发时机 触发操作 ON 表名
FOR EACH ROW | FOR EACH STATEMENT
BEGIN
    -- 触发执行的代码
END;

（g）before

（h）update

CREATE TRIGGER CheckBusinessRule
(g) INSERT OR DELETE OR (h)on SALARY
FOR EACH (i)
BEGIN

触发时机（本题考点）

BEFORE 在操作执行之前触发（可以阻止操作）
AFTER 在操作执行之后触发（用于日志、审计）
INSTEAD OF 替代操作执行（常用于视图）

触发操作

可以是 INSERT、DELETE、UPDATE 中的一种或多种组合

题目明确说了是为了防止非法修改，"只能在每月25日上班时间修改" = 不满足条件时，禁止修改，必须在修改发生之前检查条件，不满足就拦截。因此用before

BEFORE INSERT OR DELETE OR (h) ON SALARY

这里就是列出要在什么操作之前检查？列出的都是对表修改的操作，自然就是insert、delete、update

FOR EACH的用法

（i）row

FOR EACH ROW 每影响表中的一行触发一次 UPDATE 10行 → 触发10次
FOR EACH STATEMENT 每条SQL语句触发一次 UPDATE 10行 → 触发1次

这是对工资表增删改的业务规则检查，必须对每一行操作都进行拦截判断，所以用 ROW

FOR EACH STATEMENT 关注的是"执行了一条什么 SQL"，FOR EACH ROW 关注的是"每一行数据被改成了什么样"。如果你需要针对每一行的变化做不同的事情（比如给不同的人发不同的短信），就必须用 FOR EACH ROW。

（j）‘25’

（k）not

BEGIN

IF (TO_CHAR(sysdate,'DD')<>(j))

OR(to_number(TO_CHAR(sysdate,'HH24))

(k) BETWEEN 8 AND 18)THEN

Raise_Error;//抛出异常

END IF;

END;

题目中的业务规则

"对员工工资表的修改只能在每月25日的上班时间进行。"

仔细看IF语句，如果其中的条件成立，那么就是异常终止

也就是说条件是今天不是25日，或者今天不是上班时间，根据题目信息上班时间是8点-18点。

TO_CHAR 格式化日期

TO_CHAR(sysdate, 'DD')      -- 返回日期的"日"部分，如 '25'（字符串）
TO_CHAR(sysdate, 'HH24')    -- 返回24小时制的小时，如 '14'（字符串）

sysdate 是数据库的系统日期时间函数，返回当前日期和时间。

TO_NUMBER 字符内容转成数字

TO_NUMBER('14')  -- 括号里面的字符串14返回成数字 14

结果就是：

IF (TO_CHAR(sysdate,'DD')<> ‘25’) 返回的日期字符串不等于‘25’
OR(to_number(TO_CHAR(sysdate,'HH24))返回的时间不在8和18之间
not BETWEEN 8 AND 18) THEN 报错终止

并行调度和两段锁分析

假设当某月计算一位员工的最终工资时，同一时间人事部门对该员工执行了奖励2000元的事务操作，对应事务的部分调度序列如表4-1所示。

（1）请说明该事务调度存在哪种并发问题？

（2）采用2PL是否可以解决该并发问题？是否会产生死锁？

丢失更新

两个事务同时读写同一数据，后提交的事务覆盖了先提交事务的修改，导致先提交事务的更新丢失。

计算最终工资的这个事务是用奖励2000元之前的数据，它在最后提交这个员工的薪水的时候，会把人事的奖罚的工资给覆盖掉。

（1）该事务存在丢失更新的问题。因为人事部门奖罚事务更新的最终工资被计算最终工资事务的写入而覆盖了。

在 2PL 下：

工资事务在 T1 读取 SALARY 表相关行时，会对这些行加共享锁（S锁）
奖罚事务在 T2 要更新同一行时，需要排他锁（X锁）
但共享锁未释放，排他锁无法加上 → 奖罚事务必须等待
直到工资事务完成计算、写入并提交释放锁后，奖罚事务才能执行

这样就保证了串行化，工资事务要么看到奖罚之后的数据，要么先执行完奖罚事务再看到，不会出现"读到一半旧数据"的情况。

所以：2PL 可以解决这个丢失更新问题。

（2）可以。可能会产生死锁。（两段锁不保证不发生死锁）

2.SQL存储过程和并发控制

【说明】
某银行账务系统的部分简化后的关系模式如下：
账户表：accounts(a_no,a_name,a_status, a_bal, open_branch_no,open_branch_name,phone_no)；属性含义分别为：账户编码、账户名称、账户状态（1-正常、2-冻结、3-挂失）、账户余额、开户网点编码、开户网点名称、账户移动电话。
账户交易明细表：
trade_details(t_date,optr_no,serial_no,t_branch,a_no,t_type,t_amt,t_result)；属性含义分别为：交易日期、操作员编码、流水号、交易网点编码、账户编码、交易类型（1-存款、2-取款）、交易金额、交易结果（1-成功、2-失败、3-异常、4-已取消）。
网点当日余额汇总表：branch sum(bno,b_date,b_name,all_bal)；属性含义分别为：网点编码、汇总日期、网点名称、网点开户账户的总余额。
系统提供常规的账户存取款交易，并提供账户余额变更通知服务。该账务系统是7*24h不间断地提供服务；网点当日余额汇总操作一般在当日晚上12点左右，运维人员在执行日终处理操作中完成。

【问题1】（6分）
下面是系统日终时生成网点当日余额汇总数据的存储过程程序，请补全空缺处的代码。

CREATE PROCEDURE BranchBalanceSum(IN s_date char(8))
DECLARE
       all_balance number(142);
       v_bran_no varchar(10) ;
       v_bran_name varchar(30);
       (a) c_sum_bal IS
       SELECT open_branch_no,open_branch_name,sum(a_bal)
       FROM accounts GROUP BY open_branch_no,open_branch_name;
BEGIN
       OPEN c_sum_bal;
      LOOP
           (b) c_sum bal INTO v_bran_no, (c) ;
            IF c_sum_bal%%NOTFOUND THEN//未找到记录
                   (d);
            END IF;
            INSERT INTO branch_sum
                VALUES(v_bran_no,s_date,v_bran_name,all_balance);
       END LOOP;
       CLOSE (e) ; COMMIT;
EXCEPTION WHEN OTHERS THEN
        (f)
END;

【问题2】（5分）
当执行存取款交易导致用户账户余额发生变更时，账务系统需要给用户发送余额变更短信通知。通知内容为“某时间您的账户执行了某交易，交易金额为XX元，交易后账户余额为XXX元” 。默认系统先更新账户表，后更新账户交易明细表。
下面是余额变更通知功能对应的程序，请补全空缺处的代码。

CREATE TRIGGER BalanceNotice (g) INSERT on (h)
         (i)
       WHEN (j) =1
DECLARE
        vphone varchar(30);
        vtype varchar(30) ;
        vbal number(142);
        vmsg varchar(300);
BEGIN
        SELECT phoneno,a_bal INTO v_phone,v_bal FROM accounts
        WHERE a_no = (k) ;
        IF NEW.t_type=1 THEN
               vtype:=存款;
        END IF;
        IF NEW.t_type=2 THEN
               vtype:=取款;
        ENDIF;
        vmsg:=NEW.t date'，您的账户NEWa no'上执行了'vtype交易，交易金额为"to string(NEW t amt)元，交易后账户余额为'to_string(v bal)元';
        SendMsg(v_phone，v_msg);//发送短信
END;

【问题3】（4分）
假设日终某网点当日余额汇总操作和同一网点某账户取款交易同一时间发生，对应事务的部分调度序列如表4-1所示。

（1）在事务提交读隔离级别下，该网点的汇总和取款事务是否成功结束？

（2）如果该数据库提供了多版本并发控制协议，两个事务是否成功结束？

游标

在之前的题目中，我们根据需要，通过where子句select出所需的数据into变量赋值，再用这些变量处理我们的需求。

在这题中，首先观察到

SELECT open_branch_no, open_branch_name, sum(a_bal)
FROM accounts
GROUP BY open_branch_no, open_branch_name;

select并没有限定条件，且后续用group by处理，还要与题目中的LOOP搭配，这种选取多行、逐行处理的情况应该使用游标。

a_no	open_branch_no	a_bal
001	     B001	         50000
002	     B001	         30000
003	     B002	         80000
004	     B002	         20000
查询结果集：
┌─────────┬──────────┐
│ 网点A   │  100000  │  ← 游标最开始指向这里（第一行之前）
├─────────┼──────────┤
│ 网点B   │  200000  │
├─────────┼──────────┤
│ 网点C   │  150000  │
└─────────┴──────────┘

每次 FETCH，游标向下移动一行，把当前行的数据取出来。

sum(a_bal)是把同一网点的所有账户余额加起来

DECLARE
    v1 数据类型;
    v2 数据类型;
    CURSOR 游标名 IS SELECT 列1, 列2 FROM 表 WHERE 条件;  -- ① 声明
BEGIN
    OPEN 游标名;                    -- ② 打开
    LOOP
        FETCH 游标名 INTO v1, v2;   -- ③ 取数据
        IF 游标名%NOTFOUND THEN    -- 如果没数据了
            EXIT;                   -- 退出循环
        END IF;
        -- 处理当前行数据...
    END LOOP;
    CLOSE 游标名;                   -- ④ 关闭
END;

按照游标的声明格式，a处应该填写 CURSOR

DECLARE
       all_balance number(142);
       v_bran_no varchar(10) ;
       v_bran_name varchar(30);
       (a) c_sum_bal IS
       SELECT open_branch_no,open_branch_name,sum(a_bal)
       FROM accounts GROUP BY open_branch_no,open_branch_name;

游标的四个标准步骤

① 声明 CURSOR ... IS SELECT ... 定义游标，绑定查询语句

② 打开 OPEN 游标名上一步知识声明，这一步开始执行查询，游标指向第一行之前

③ 取数据 FETCH 游标名 INTO 变量逐行取出数据

④ 关闭 CLOSE 游标名释放游标资源

b应该填写FETCH

每次loop处理，fetch取出先前select给游标的三列，交给变量

SELECT open_branch_no, open_branch_name, sum(a_bal)

BEGIN
       OPEN c_sum_bal;
      LOOP
           (b) c_sum_bal INTO v_bran_no, _(c)_ ;
            IF c_sum_bal%%NOTFOUND THEN//未找到记录
                   (d);
【对应关系】
SELECT  open_branch_no,  open_branch_name,  sum(a_bal)
           ↓                ↓                ↓
INTO    v_bran_no,       v_bran_name,     all_balance

因此C处应该填写v_bran_name, all_balance

d处表示当为找到记录时退出loop循环，d应该填EXIT或BREAK

处理完毕后，关闭 CLOSE 游标名释放游标资源

END IF; #结束前面的IF判断
            INSERT INTO branch_sum
                VALUES(v_bran_no,s_date,v_bran_name,all_balance);
       END LOOP;
       CLOSE _(e) ; COMMIT;
EXCEPTION WHEN OTHERS THEN
        (f)

e处应该填写c_sum_bal

事物结束COMMIT，异常发生则ROLLBACK

f处应该填写rollback

此处的when others标识所有异常通配符

INSERT INTO branch_sum VALUES(v_bran_no,s_date,v_bran_name,all_balance);

把计算后的变量填入网点当日余额汇总表：branch sum(bno,b_date,b_name,all_bal)；

触发器之INSERT ON

CREATE [OR REPLACE] TRIGGER 触发器名
触发时机 触发操作 ON 表名
FOR EACH ROW | FOR EACH STATEMENT
[可选when]
BEGIN
    -- 触发执行的代码
END;

CREATE TRIGGER BalanceNotice (g) INSERT on (h)

(i)

需要确定触发时机和触发器ON在哪个表上面

题目2的题目提到：默认系统先更新账户表accounts，后更新账户交易明细表trade_details。

按照题目要求，当发送通知的短信的时候，两张表应该都已经完成操作。所有的数据在发送的那刻都是最新的。

因此g应该填写after，h应该填写trade_details

每次交易都要给该账户发短信，必须是行级触发器。所以 (i) 填：FOR EACH ROW

FOR EACH STATEMENT 关注的是"执行了一条什么 SQL"，FOR EACH ROW 关注的是"每一行数据被改成了什么样"。如果你需要针对每一行的变化做不同的事情（比如给不同的人发不同的短信），就必须用 FOR EACH ROW。

触发器中的when

CREATE TRIGGER BalanceNotice (填after) INSERT on (填trade_details)
         (填FOR EACH ROW)
       WHEN (j) =1

只有满足 WHEN 条件的行，才会执行触发器体内BEGIN...END的代码。不满足条件的行，触发器直接跳过，不执行。

根据题目，t_result表示交易结果，（1-成功、2-失败、3-异常、4-已取消）

只有交易成功（t_result = 1）才需要发短信通知用户。失败、异常、已取消的交易不需要通知。

不过需要注意的是答案是WHEN (NEW.t_result) = 1

触发器中的NEW

触发器中需要用到什么数据？
    ↓
    ├── 系统时间、系统变量、常量 → 不需要 NEW/OLD
    │
    ├── 被修改行的"新值"（INSERT 的值、UPDATE 改之后的值）
    │       → 需要 NEW
    │
    └── 被修改行的"旧值"（DELETE 的值、UPDATE 改之前的值）
            → 需要 OLD

NEW有什么作用？

如果没有 NEW.，单独t_result 指的是什么？数据库不知道你要引用哪一行的 t_result

NEW.t_result精确指代刚刚插入的那一行中的t_result

继续往下阅题目，NEW出现了多次。都是表示当前的交易的状态，否则对于整个表而言，无法用这个字段的多个不知道什么时候的值来做判断。

DECLARE
        vphone varchar(30);
        vtype varchar(30) ;
        vbal number(142);
        vmsg varchar(300);
BEGIN
        SELECT phone_no,a_bal INTO v_phone,v_bal FROM accounts
        WHERE a_no = (k) ;

根据题目后续内容，

v_msg用来存最终拼接好的短信内容

v_bal被a_bal赋值，是账户表accounts的余额

vtype的内容要么是存款要么是取款，总之是用来表示交易的类型

vphone存手机号，在结尾用来发送短信

SELECT phone_no,a_bal INTO v_phone,v_bal FROM accounts

从account这个表取查到该账户绑定的手机号和余额，（trade_details 只记录交易金额，不记录余额。余额数据在 accounts 表中。）

我们并不是把所有的行都取出来，而是 WHERE a_no = NEW.a_no，只查刚发生交易，刚插入的那个账户。

并行调度之 Read Committed（读已提交）隔离级别

Read Committed 读已提交 / 提交读指的是，其他事务没提交的修改，看不到（不能读"脏数据"），只要别的事务提交了，下次查就能看到新值。

T1-T2：读第1、第2个账户
         ↓ 此时取款事务还没提交，读到的是旧数据

T3-T4：取款事务修改第3个账户并提交
         ↓ 取款事务已保存（COMMIT）

T5：汇总事务读第3个账户
         ↓ 因为"提交读"允许看到已提交的新数据
         ↓ 读到的是取款后的新余额

汇总结果 = 新旧混合 → 不一致！

汇总事务读到混合的新旧数据，导致汇总结果不一致，不能成功结束；整个调度无法保证正确性

（1）在事务提交读隔离级别下，该网点的汇总和取款事务不能成功结束。

MVCC

MVCC 的核心机制是：每个事务在开始时，获得一个数据快照。整个事务过程中，它只看得见这个快照里的数据。也就是说，众所周知操作的都是取款前的数据。

相对于传统使用加锁的机制，

"读不阻塞写"：正在读旧版本的事务，不会阻止其他事务创建新版本。

"写不阻塞读"：正在创建新版本的事务，不会阻止其他事务读取旧版本。MVCC 通过保留数据多个版本，让读和写操作各取所需，互不干扰。

（2）在 MVCC 协议下，汇总事务在开始时获得数据快照，整个执行过程中只读取快照版本的数据。取款事务在 T3-T4 执行的修改产生的是新版本，不影响汇总事务的快照。因此汇总事务读到的是取款前的一致数据，汇总结果正确；取款事务也可以正常提交。读不阻塞写，写不阻塞读，两个事务都能成功结束。

3.SQL存储过程和并发控制

【说明】

某网上销售系统的部分关系模式如下：

订单表：orders（o_no, o_date, o_time, p_no, m no, p_price, nums, amt, status）。其中属性含义分别为：订单号、订单日期、订单时间、产品编码、供应商编码、产品价格、产品数量、订单金额、订单状态（0-未处理、1-已处理、 2-已取消）。

产品表：products（p_no, p_name, p_type, price, m_no, p_nums）。其中属性含义分别为：产品编码、产品名称、产品类型、产品价格、供应商编码、库存数量。

【问题1】(5分)

节假日时，由供应商提供商品打折后的新价格，数据存放在临时表中，该临时表的表名为tmp_prices（不同供应商有不同的临时表），其关系模式如下：

tmp_ prices(P_ no, t_ price,m_no) ;

#根据前文，内容是产品编码，新属性t_price，供应商编码。我们根据字面意思可以猜测，tprice就是tmp临时价格

后台维护人员需要根据供应商填写在tmp prices中的数据来更新产品表中某些产品的价格。下面是基于游标，用SQL实现的价格更新程序，请补全空缺处的代码。

CREATE PROCEDURE UpdatePrice()
DECLARE

Pno         VARCHAR(10);
Pprice      real(6,2);
Mno         VARCHAR(10);
      (a)     upPrice IS
          SELECT p_no, t_price, m_no FROM tmp_prices;
BEGIN
      (b)     upPrice ;
LOOP
         FETCH upPrice INTO    (c)   ;
         IF NOTFOUND DO BREAK    //FETCH 操作无数据
         UPDATE products SET price = Pprice WHERE p_no=Pno and m_no=Mno;
         if error  //error 是由DBMS提供的上一句SQL的执行状态
         BEGIN
                  ROLLBACK;
                  RETURN    -1;
         END
END LOOP
CLOSE upPrice;

    (d)   ;
END

【问题2】(6分)

假设用户1和用户2同时购买1份A商品，用户3查询和浏览A商品。三个用户对应事务的部分调度序列如表4-1所示（事务中未进行并发控制），其中T0时刻该A商品的库存数量p_nums为100。

请说明T4、T7时刻，用户3事务读取到的p_nums 数值分别是多少。请说明T8时刻事务调度结果是否正确？若不正确请说明属于哪一种数据不一致性。
【问题3】(4分)
为保证并发事务的正确性，系统要求所有事务需遵循两段锁协议。
（1）请用100字以内的文字简要解释两段锁协议，并说明“两段”的含义。
（2）请说明两段锁协议是否可以避免死锁?如不能避免，应采取什么措施解决死锁问题。

回顾游标知识

DECLARE
    v1 数据类型;
    v2 数据类型;
    CURSOR 游标名 IS SELECT 列1, 列2 FROM 表 WHERE 条件;  -- ① 声明
BEGIN
    OPEN 游标名;                    -- ② 打开
    LOOP
        FETCH 游标名 INTO v1, v2;   -- ③ 取数据
        IF 游标名%NOTFOUND THEN    -- 如果没数据了
            EXIT;                   -- 退出循环
        END IF;
        -- 处理当前行数据...
    END LOOP;
    CLOSE 游标名;                   -- ④ 关闭
END;

① 声明 CURSOR ... IS SELECT ... 定义游标，绑定查询语句

② 打开 OPEN 游标名上一步知识声明，这一步开始执行查询，游标指向第一行之前

③ 取数据 FETCH 游标名 INTO 变量逐行取出数据

④ 关闭 CLOSE 游标名释放游标资源

CREATE PROCEDURE UpdatePrice()

DECLARE
Pno         VARCHAR(10);
Pprice      real(6,2);
Mno         VARCHAR(10);
      (a)     upPrice IS
          SELECT p_no, t_price, m_no FROM tmp_prices;
BEGIN
      (b)     upPrice ;

显然(a)处应该填写漏了的声明游标CURSOR

在BEGIN开始之后，进行打开游标 OPEN 游标名

LOOP
         FETCH upPrice INTO    (c)   ;
         IF NOTFOUND DO BREAK    //FETCH 操作无数据
         UPDATE products SET price = Pprice WHERE p_no=Pno and m_no=Mno;
         if error  //error 是由DBMS提供的上一句SQL的执行状态
         BEGIN
                  ROLLBACK;
                  RETURN    -1;
         END
END LOOP
CLOSE upPrice;
    (d)   ;
END

FETCH将游标upPrice的内容给予变量以便进一步处理，由于题目没有任何限定条件，fetch只是单纯的按照存储顺序逐个在loop中取出。

根据游标声明的p_no, t_price, m_no可知，要赋予的临时变量依次是：
Pno，Pprice，Mno

根据题目要求，需要根据供应商填写在tmpprices中的数据来更新产品表中某些产品的价格。

UPDATE products SET price = Pprice WHERE p_no=Pno and m_no=Mno;

产品编码和供应商编码相等时，更新产品价格

空白（d）是该存储过程的最后一条语句，LOOP结束，游标也释放了，应该对修改后的数据进行确认提交，应该填写commit语句。

其中T0时刻该A商品的库存数量p_nums为100。

T4时候，由于操作均没有写入，事物3只能读到初值100

T7时，事物2的99写入，覆盖了事物1，因此事物3的用户读到的值为99

因此事务调度结果不正确，不属于串行化调度，属于丢失修改。导致不正确的原因是事务2的修改覆盖了事务1的修改，导致事务1的修改丢失了。

为保证并发事务的正确性，系统要求所有事务需遵循两段锁协议。

（1）请用100字以内的文字简要解释两段锁协议，并说明“两段”的含义。

（2）请说明两段锁协议是否可以避免死锁?如不能避免，应采取什么措施解决死锁问题。

两段锁协议是指对任何数据进行读写之前必须对数据加锁；在释放一个封锁之后，事务不再申请和获得任何其他锁。

该协议明确所有事务必须分两个阶段对数据项加锁和解锁：第一阶段是获得封锁，事务可以对数据加读/写锁，但不能释放，也称为扩展阶段；第二阶段是释放封锁，事务可以释放前面对数据加的读/写锁，但不能再申请加锁，也称为收缩阶段。

两段锁协议只能保证事务调度的可串行化，但无法预防死锁，因为2PL只规定了锁的加锁/释放阶段，没有规定事务获取锁的顺序。如果两个事务分别以不同顺序获取资源上的锁，就会形成循环等待导致死锁。

当两个满足两段锁协议的事务并发运行时，如果两个事务对两个资源进行了交叉锁定，死锁就必然发生。

1️⃣采用“一次封锁法”：要求事务在执行前一次性申请全部所需锁，若无法全部获取则不执行并释放已占资源，从而破坏“请求与保持”条件。（事务管理中死锁四个条件知识）

2️⃣DBMS要提供死锁检测机制，以实时检测死锁的发生，并根据某种策略解除死锁。

4.SQL存储过程和并发控制

【说明】

某企业网上书城系统的部分关系模式如下:

书籍信息表：books(book_no, book_name, press_no, ISBN, price, sale_type, all_nums)。其中属性含义分别为：书籍编码、书籍名称、出版商编码、ISBN、销售价格、销售分类、当前库存数量。

书籍销售订单表：orders(order_no, book_no, book_nums, book_price, order_date,amount)。其中属性含义分别为：订单编码、书籍编码、书籍数量、书籍价格、订单日期和总金额。

书籍再购额度表：booklimit(book_no, sale_type, limit_amount)。其中属性含义分别为：书籍编码、销售分类、再购额度；

书籍最低库存表：bookminlevel(book_no, level) ，其中属性含义分别为：书籍编码，书籍最低库存数量；

书籍采购表：bookorders(book_no, order_amount)，其中属性含义分别为：书籍编码和采购数量。

有关关系模式的说明如下：

（1）下划线标出的属性是表的主码。

（2）根据书籍销售情况来确定书籍的销售分类：销售数量小于1万的为普通类型，其值为0；1万及以上的为热销类型，其值为1。

（3）系统具备书籍自动补货功能，涉及到的关系模式有：书籍再购额度表、书籍最低库存表、书籍采购表。其业务逻辑是：当某书籍库存小于其最低库存数量时，根据书籍的销售分类以及书籍再购额度表中的再购额度，生成书籍采购表中的采购订单，完成自动补货操作。

【问题1】(5分)

系统定期扫描书籍销售订单表，根据书籍总的销售情况来确定书籍的销售类别。下面是系统中设置某书籍销售类别的存储过程，结束时需显式提交返回。请补全空缺处的代码。

CREATE PROCEDURE UpdateBookSaleType(IN bno varchar(20))
DECLARE
          all_nums number(6);
BEGIN
          SELECT (a) (book_nums) INTO all_nums FROM orders
                  WHERE book_no = (b) ;
          IF all_nums < (c) THEN
                  UPDATE books SET sale_type = 0 WHERE book_no = bno;
          ELSE
                  UPDATE books SET sale_type = (d) WHERE book_no = bno;
          END IF;
          (e) ;

【问题2】 (6分)

下面是系统中自动补货功能对应的触发器，请补全空缺处的代码。

CREATE TRIGGER BookOrdersTrigger  （f）    update
       of      (g)     on books
          (h)     
       WHEN     (i)      <(SELECT level FROM bookminlevel
                                    WHERE bookminlevel.book_no = OLD.book_no)
        AND      (j)       >=(SELECT level FROM bookminlevel
                                       WHERE bookminlevel.book_no = OLD.book_no)
BEGIN
        INSERT INTO (k)
                (SELECT book_no,limit_amount
                       FROM booklimit as TMP
                       WHERE TMP.book_no = OLD.book_no
                       AND TMP.sale_type = OLD.sale_type);
END;

【问题3】 (4分)

假设用户1和用户2同时购买同一书籍，对应事务的部分调度序列如表4-1所示(事务中未进行并发控制)，其中T0时刻该书籍的库存数量all_nums=500。

（a）sum

先把题目SQL语句中涉及的表和属性找到相对应的出处

CREATE PROCEDURE UpdateBookSaleType(IN bno varchar(20))
DECLARE
          all_nums number(6);
BEGIN
          SELECT (a) (book_nums) INTO all_nums FROM orders
                  WHERE book_no = (b) ;

book_nums是数据销售订单表orders中的数量，看到这个属性有括号，要联想到聚合函数。题目要求根据销量分类，这里需要获取总数并赋予给变量all_nums。因此是SUM

单独的book_nums 是一笔订单的销售数量。使用SUM之后同样的book_no的销量会累加。

orders 表：

order_no   book_no   book_nums
───────────────────────────────
ORD001     B001      3          ← 第一笔订单卖了3本
ORD002     B001      5          ← 第二笔订单卖了5本
ORD003     B001      2          ← 第三笔订单卖了2本

WHERE book_no = (b) ;

首先我们select的是orders表中book_nums列的所有值，where限定条件的含义应该是存储过程传递给我们什么参数，我们就用这个参数去找它的总销量。

假设我们看不懂传进来的参数是做什么用的，在题目后文中，WHERE book_no = bno;也出现了好几遍。

 IF all_nums < (c) THEN
                  UPDATE books SET sale_type = 0 WHERE book_no = bno;
          ELSE
                  UPDATE books SET sale_type = (d) WHERE book_no = bno;
          END IF;
          (e) ;
END;

题目已经提示了，销售数量小于1万的为普通类型，其值为0；1万及以上的为热销类型，其值为1。all_nums又是个数值类型，直接比较数字就好。C填10000

IF all_nums < 10000 then

UPDATE books SET sale_type = 1，也就是热销类型

最后END IF处理完了，使用commit提交

触发器之UPDATE FOR

【问题2】

题目条件：

系统具备书籍自动补货功能，涉及到的关系模式有：书籍再购额度表#booklimit、书籍最低库存表#bookminlevel、书籍采购表#bookorders。

其业务逻辑是：当某书籍库存小于其最低库存数量时，根据书籍的销售分类以及书籍再购额度表中的再购额度，生成书籍采购表中的采购订单，完成自动补货操作。

CREATE [OR REPLACE] TRIGGER 触发器名
触发时机 触发操作 ON 表名
FOR EACH ROW | FOR EACH STATEMENT
[可选when]
BEGIN
    -- 触发执行的代码
END;

这是一个做补货操作的触发器，应该在更新操作之后触发，这样系统才能在库存刚被消耗、低于临界值时进行补货。

CREATE TRIGGER BookOrdersTrigger  （f）    update
       of      (g)     on books
          (h)

所以（f）填 AFTER。

（g）这里是 UPDATE 操作的 of 子句，用来指定只在更新具体哪一列时才触发。在这个场景中，只有库存数量 all_nums 的变化才需要导致补货。

只有 FOR EACH ROW 才能使用 OLD 和 NEW

FOR EACH STATEMENT 的视角

它关注的是整条 SQL 语句，不关心具体改了哪些行。OLD 和 NEW 在这种级别下是没有意义的——"哪一行"都没概念，哪来的"旧值"和"新值"？

FOR EACH ROW 的视角

它关注的是被修改的每一行。上面的 UPDATE 影响了 50 行，行级触发器就触发 50 次，每次聚焦于一行。

因此，既然题目中出现了OLD，就可以认为必然是FOR EACH ROW，而且根据题目要求，我们需要对比库存是行级别的。

       WHEN     (i)      <(SELECT level FROM bookminlevel
                                    WHERE bookminlevel.book_no = OLD.book_no)
        AND      (j)       >=(SELECT level FROM bookminlevel
                                       WHERE bookminlevel.book_no = OLD.book_no)

分析填空先找到属性的出处

书籍最低库存表：bookminlevel(book_no, level) ，其中属性含义分别为：书籍编码，书籍最低库存数量；

库存量这个属性，在书籍信息表：books中的all_nums。

现在的库存量小于书籍最低库存数量

WHEN NEW.all_nums <(SELECT level FROM bookminlevel

WHERE bookminlevel.book_no = OLD.book_no)

题目要求当某书籍库存小于其最低库存数量时触发，并不需要库存量远低于要求时每次都一直触发，所以说要找到临界状态：新库存值小于要求，旧库存值满足要求。

AND OLD.all_nums >=(SELECT level FROM bookminlevel

WHERE bookminlevel.book_no = OLD.book_no)

因为这个触发器是针对all_nums，而且有when限定条件，当它触发时，books这个表里面相比较的是同一本书，那 OLD.book_no 和 NEW.book_no 就是同一个值，用哪个都行。

我们用 OLD.book_no 只是习惯性："就是当前正在被处理的这本书的原始编码"。

BEGIN
        INSERT INTO (k)
                (SELECT book_no,limit_amount
                       FROM booklimit as TMP
                       WHERE TMP.book_no = OLD.book_no
                       AND TMP.sale_type = OLD.sale_type);
END;

题目已经说了这道题涉及书籍采购表bookorders。很显然这里是要把书籍的编号和再购额度插入到采购表作为要采购的数量。

k就填bookorders

并行分析之读脏数据

T0时刻该书籍的库存数量all_nums=500

请说明T4时刻，用户2事务读取到的all_nums数值是多少？

由于T3已经写入，T4时刻用户2读到的是498

请说明T8时刻，all_nums数据是否出现不一致性问题？如出现，请说明属于哪一种数据不一致性。

看到rollback就要联想到脏读取

脏读（Dirty Read）。用户2读取了用户1未提交的修改（498），并基于此脏数据进行了后续操作，导致用户1回滚后数据库中留存了错误的值（495）。

需要注意的是，即使没有rollback，也不属于丢失更新。

丢失更新的定义：两个事务同时读取同一个数据，各自基于读取的值进行修改，后提交的事务覆盖了先提交事务的修改，导致先提交事务的更新丢失。

因为事物1和事物2并不是都读取的初值，然后在初值的基础上计算，这样后者覆盖前者才叫丢失更新。

然而实际上事物2是事物1写入后接着修改的。

5.SQL存储过程/隔离级别和锁

【说明】
某商业银行账务系统的部分关系模式如下：
账户表：Account (ano, aname, balance)，其中属性含义分别为：账户号码，账户名称和账户余额。
交易明细表：TranDetails (too, ano, ttime, toptr, amount, ttype)，其中属性分别为：交易编号，账户号码，交易时间，交易操作员，交易金额，交易类型(1-存款，2-取款，3-转账)。
余额汇总表：AcctSums (adate, atime, allamt)，其中属性分别为：汇总日期，汇总时间，总余额。
常见的交易规则如下：
存/取款交易：操作员核对用户相关信息，在系统上执行存/取款交易。账务系统增加/ 减少该账户余额/并在交易明细表中增加一条存/取款交易明细。
转账交易：操作员核对用户相关信息，核对转账交易账户信息，在系统上执行转账交易。账务系统对转出账户减少其账户余额，对转入账户增加其账户余额，并在交易明细表中增加一条转账交易明细。
余额汇总交易：将账户表中所有账户余额累计汇总。
假定当前账户表中的数据记录如表5-1所示。

表5-1

【问题1】（3分）

假设在正常交易时间，账户上在进行相应存取款或转账操作时，要执行余额汇总交易。下面是用SQL实现的余额汇总程序，请补全空缺处的代码。要求（不考虑并发性能）在保证余额汇总交易正确性的前提下，不能影响其他存取款或转账交易的正确性。

CREATE PROCEDURE AcctSum(OUT： Amts DOUBLE)

BEGIN

SET TRANSACTION ISOLATION LEVEL（  a  ）;

BEGIN TRANSACTION;

SELECT sum(balance) INTO : Amts FROM Accounts;

if error// error是由DBMS提供的上一句SQL的执行状态

BEGIN

ROLLBACK;

return -2;

END

INSERT INTO AcctSums

VALUES (getDATE(), getTIME(), （  b  ）);

if error // error是由DBMS提供的上一句SQL的执行状态

BEGIN

ROLLBACK;

return-3;

END

（   c   ）;

END

隔离级别

题目："要求（不考虑并发性能）在保证余额汇总交易正确性的前提下，不能影响其他存取款或转账交易的正确性。"

汇总结果正确指的是不读到脏数据、不出现不可重复读/幻读，汇总统计类的题目选 SERIALIZABLE 基本不会错。隔离级别越高能解决的问题就越多，并发能力就越来越差。

SQL 标准定义了四种隔离级别，从低到高：

级别名称脏读不可重复读幻读

0 READ UNCOMMITTED（读未提交） ✅ ✅ ✅

1 READ COMMITTED（读已提交） ❌ ✅ ✅

2 REPEATABLE READ（可重复读） ❌ ❌ ✅

3 SERIALIZABLE（可串行化） ❌ ❌ ❌

在SQL中写隔离级别的方式：

BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- ← 在 BEGIN TRANSACTION 之前
    BEGIN TRANSACTION;
        ...操作...
    COMMIT;
END;

CREATE PROCEDURE AcctSum(OUT： Amts DOUBLE)

Amts 在参数列表中作为 OUT 参数声明，类型是 DOUBLE。后续不需要再单独声明。: 表示这是外部变量。

BEGIN

SET TRANSACTION ISOLATION LEVEL（ a ）;

BEGIN TRANSACTION;

(a) 填：SERIALIZABLE

SELECT sum(balance) INTO : Amts FROM Accounts;

所有账户余额的总和，存入 OUT 参数 Amts，得到当前银行所有账户的总余额。后续语句是如果select失败则回滚。

INSERT INTO AcctSums

VALUES (getDATE(), getTIME(), （ b ）);

余额汇总表：AcctSums (adate, atime, allamt)，其中属性分别为：汇总日期，汇总时间，总余额。

因此b处应该是 :Amts 前面 SELECT 出的汇总总余额

if error // error是由DBMS提供的上一句SQL的执行状态

BEGIN

可以注意到每个if都对应有一个begin和end

ROLLBACK;

return-3;

END

（ c ）;

END

事物结束，除了有commit，还要观察到前面都有返回值，因此这空是

COMMIT; return 0

两段锁协议/提交读隔离级别

【问题2】（8分）
引入排他锁指令LX()和解锁指令UX()，要求满足两段锁协议和提交读隔离级别。假设在进行余额汇总交易的同时，发生了一笔转账交易。从101账户转给104账户400元。这两笔事务的调度如表5-2所示。

表5-2 转账汇总部分事务调度表

（1）请补全表中的空缺处（a）、（b）；

提交读意味着只能读到已提交的数据

2PL意味着加锁阶段不能解锁，解锁阶段不能加锁。

（a）空读取104账户余额时，该数据已被加写锁(排他锁)，因此需要等待转账事务释放锁；题目要填写空缺处，答案是：等待

T8汇总事务终于读到了104，且下一步是提交，意味着转账事务解锁，即 UX(104) 和 UX(101)

（2）上述调度结束后，汇总得到的总余额是多少？

正常情况来说，转账的前后，总余额肯定是不会变的。

但是题目中是先读101的余额500，然后才转账400。后面先更新104的余额，加400块，然后才读取。也就是说，对于汇总这个事物，101的钱并没有减少，凭空多了四百块。

500+350+550+（200+400）=2000

（3）该数据是否正确？请说明原因。

不正确。事务并发过程中，在加锁前，汇总事务已经读取了101账户余额，而转账事务在后面对其进行了更新，所以影响了最终结果。前后不一致。

6.锁分析和SQL存储过程

【说明】

某抢红包软件规定发红包人可以一次抛出多个红包，由多个人来抢。要求每个抢红包的人最多只能抢到同一批次中的一个红包，且存在多个人同时抢同一红包的情况。给定的红包关系模式如下：

Red(ID，BatchID，SenderID，Money，ReceiverID)

其中 ID 唯一标识每一个红包； BatchID 为发红包的批次，一个 BatchID 值可以对应多个 ID 值；SenderID 为发红包人的标识；Money 为红包中的钱数； ReceiverID 记录抢到红包的人的标识。

发红包人一次抛出多个红包，即向红包表中插入多条记录，每条记录表示一个红包，其 ReceiverID 值为空值。

抢某个红包时，需要判定该红包记录的 ReceiverID 值是否为空，不为空时表示该红包已被抢走，不能再抢，为空时抢红包人将自己的标识写入到 ReceiverID 字段中，即为抢到红包。

【问题 1】（9分）

引入两个伪指令 a = R(X) 和 W(b，X) 。其中a = R(X) 表示读取当前红包记录的 ReceiverID 字段(记为数据项 X) 到变量 a 中， W(b，X)表示将抢红包人的唯一标识 b 的值写入到当前红包记录的 ReceiverID 字段(数据项 X) 中，变量 a 为空值时才会执行 W(b，X) 操作。假设有多个人同时抢同一红包（即同时对同一记录进行操作），用ai=Ri(X)和Wi(bi，X)表示系统依次响应的第i个人的抢红包操作。假设当前数据项 X 为空值，同时有三个人抢同一红包，则

（1）如下的调度执行序列：

a1=R1(X)，a2=R2(X)，W1(b1，X)，W2(b2，X)，a3=R3(X)

抢到红包的是第几人？并说明理由。

题目：抢某个红包时，需要判定该红包记录的 ReceiverID 值是否为空

题目：变量 a 为空值时才会执行 W(b，X) 操作。

1 a1 = R1(X) 第1人读 X，a1 = 空

2 a2 = R2(X) 第2人读 X，a2 = 空

3 W1(b1, X) 第1人写入自己的标识，X = b1

4 W2(b2, X) 第2人写入自己的标识，X = b2（覆盖了 b1！）

5 a3 = R3(X) 第3人读 X，a3 = b2（不为空，无法抢）

第1人和第2人都在 X 为空时读到了空值，都认为可以抢。

第1人先写了 b1，但紧接着第2人用 b2 覆盖了 b1。第3人读的时候 X 已经是 b2。

最终 ReceiverID = b2，所以抢到红包的是第2人。

典型的丢失更新

（2）引入共享锁指令 SLocki(X)、独占锁指令XLocki(X)和解锁指令 ULocki(X)，其中下标 i表示第 i个抢红包人的指令。如下的调度执行序列：

SLock1(X)，a1=R1(X)，SLock2(X)，a2=R2(X)，XLock1(X)......

是否会产生死锁？并说明理由。

首先，虽然文字是一行，但是第1个人、第2个人、第3个人他们是三个独立的事务，不是同一个事务。（不然也不会有并发调度问题）

其次，S锁和X锁的兼容规则是针对不同事务之间的。序列末尾1想申请X锁，但是2也持有S锁。所有事务的 S 锁不释放，就无法加X锁，进入等待。

此时，XLock1(X)......，我们不知道后续T2会执行什么操作，但是我们要记得，S锁是只允许读不允许写的，在第二个人的事务中，它读到了空值，它的自动流程就会申请X锁去执行执行 W(b, X) 操作。并且事务获得的锁默认会持有到事务结束（COMMIT/ROLLBACK）。

因此，

会产生死锁，由于数据X同时被1和2加锁了S锁，在对方没有释放的时候，都无法成功加成X锁，导致1和2一直都处于等待的状态，从而产生死锁。

（3）为了保证系统第一个响应的抢红包人为最终抢到红包的人，请使用上述。中引入的锁指令，对上述（1）中的调度执行序列进行修改，在满足 2PL协议的前提下，给出一个不产生死锁的完整的调度执行序列。

经过上一问我们已经知道，不能先加 S 锁再去升级（那样会产生死锁）。应该一开始就直接申请 X 锁。

XLock（X） a=R（X） W（b，X）UNLock（X）

【问题 2】 (6分)

下面是用 SQL实现的抢红包程序的一部分，请补全空缺处的代码

CREATE PROCEDURE ScrambleRed (IN BatchNo  VARCHAR(20) ，                                                     (IN RecvrNo  VARCHAR(20))--红包批号和接收红包者
BEGIN
     --是否已抢过此批红包
      if exists( SELECT * FROM Red
                  WHERE BatchID = BatchNo AND ReceiverID = RecvrNo) then
                        return -1;
       end if;
       ---读取此批派发红包中未领取的红包记录ID
       DECLARE NonRecvedNo VARCHAR(30);
       DECLARE NonRecvedRed CURSOR FOR
                    SELECT ID
                    FROM Red
                    WHERE BatchID = BatchNo AND ReceiverID IS NULL;
        -打开游标
       OPEN NonRecvedRed;
       FETCH NonRecvedRed INTO NonRecvedNo;
       while not error
                      —抢红包事务
                      BEGIN TRANSACTION;
                      //写入红包记录
                      UPDATE RED  SET  ReceiverID =RecvrNo
                      WHERE ID  = NonRecvedNo AND   (a)
                     //执行状态判定
                     If＜修改的记录数＞= 1 THEN
                                    COMMIT;
                                    (b);
                                    Return 1;
                     Else
                                    ROLLBACK;
                     End if;
                             (c);
            End while
            --关闭游标
            CLOSE NonRecvedRed;
            Return 0;
END

RETURN

RETURN 语句无论在存储过程还是触发器中，都会立即终止当前整个程序的执行。而不只是退出当前SQL语句的判断。

题目：要求每个抢红包的人最多只能抢到同一批次中的一个红包

因此使用红包批次号BatchID，而非单个红包的ID作为变量参数和判断条件来处理。

DECLARE NonRecvedRed CURSOR FOR

SELECT ID FROM Red

WHERE BatchID = BatchNo AND ReceiverID IS NULL;

游标把一批红包中，没有被人收到（Receiver为空）的单独红包ID挑出来，并且后续存入变量 NonRecvedNo

OPEN NonRecvedRed;

FETCH NonRecvedRed INTO NonRecvedNo;

while not error 只要 FETCH 成功取到数据，就继续循环

BEGIN TRANSACTION

BEGIN TRANSACTION; 开始事务，用 BEGIN TRANSACTION 包裹，让每一次抢红包尝试成为一个原子操作：要么抢到并COMMIT，要么失败并ROLLBACK。

UPDATE RED SET ReceiverID =RecvrNo

把红包表中接收者的ID设置为此存储过程传入的ID

WHERE ID = NonRecvedNo AND (a)

a填：ReceiverID IS NULL

首先NonRecvedNo是前面取出的没人领过的红包的集合，当执行到此处UPDATE时，才是真正开始修改行，有必要确认该红包在接收者一栏还是空的。

关闭游标/判断UPDATE执行是否成功

If＜修改的记录数＞= 1 THEN

COMMIT;

(b);

B填写：CLOSE NonRecvedRed

这里具体指的意思就是update真的执行成功的话游标就可以关闭了，释放资源。还要返回外面一层，所以不是退出整个过程。

Return 1;

Else

ROLLBACK;

ROLLBACK和commit的作用范围都是一个事务，也就是如果没有成功抢到红包，我们就用ROLLBACK结束这个事务，事务要么 COMMIT 要么 ROLLBACK，必须有一个结束

End if;

(c);

C填写：FETCH NonRecvedRed INTO NonRecvedNo

为什么？这涉及到while和loop不同的判断方式

WHILE和FETCH

WHILE ... END WHILE 就是循环！它和 LOOP ... END LOOP 只是写法不同，本质一样。

这道题并没有用LOOP一直FETCH取行

OPEN 游标;
FETCH 游标 INTO 变量;       -- 手动取第一行

WHILE NOT error             -- 有数据就继续
    -- 处理当前行...
    
    FETCH 游标 INTO 变量;   -- 手动取下一行
END WHILE;
CLOSE 游标;

对比原来的LOOP循环，是在循环内部判断（IF %NOT FOUND THEN EXIT）

而while，我们仔细看，在OPEN之后马上就执行了第一次FETCH。第一次fetch的结果用来判断while是否成立。有数据，后面才是进入while循环来判断。

也就是说，真正一直在循环取下一行的，实际上是答案C里面的fetch

7.锁分析和SQL存储过程

【说明】
某图书馆的图书借还业务使用如下关系模式：
书目（ISBN，书名，出版社，在库数量）
图书（书号，ISBN，当前位置）
其中在库数量为当前书目可借出的图书的数量，每本图书入库后都会有当前位置，借出后当前位置字段改为空值。每一条书目信息对应多本相同的图书，每一本图书只能对应一条书目。
借还书业务的基本流程如下描述：
（1）读者根据书名查询书目，当前书目的在库数量大于0时可借阅。
（2）读者借出一本图书时，进行出库操作：根据该图书的书号将该图书的当前位置字段值改为空值，并根据其ISBN号将对应书目的在库数量减1。
（3）读者归还一本图书时，进行入库操作：系统根据当前书架的空余位置自动生成该本书的存放位置，并根据该图书的书号将其当前位置字段值改为生成的存放位置，然后将对应书目的在库数量加1。
（4）借还书时，逐一扫描每本图书的书号并进行出、入库操作。

【问题1】（7分）

引入两个伪指令：a= R(X)表示将在库数量X值读入到变量a中；W(a，X)表示将变量a的值写入到在库数量X中。入库操作用下标I表示，出库操作用下标O表示。
将出库和入库操作分别定义为两个事务，针对并发序列：a_O=R_O(X)，a_I=R_I(X)，a_O=a_O-1，W_O(a_O，X)，a_I=a_I+1 ，W_I(a_I，X)

其中变量 a _I和 a _o分别代表入库事务和出库事务中的局部变量。
（1）假设当前X的值为3，则执行完上述并发序列的伪指令后，X的值是多少？简述产生这一错误的原因（100字以内）。

ao = Ro(X)      -- 出库事务读取 X，ao = 3
aI = RI(X)      -- 入库事务读取 X，aI = 3
ao = ao - 1     -- 出库事务计算：3 - 1 = 2
Wo(ao, X)       -- 出库事务写入 X = 2
aI = aI + 1     -- 入库事务计算：3 + 1 = 4
WI(aI, X)       -- 入库事务写入 X = 4（覆盖了出库的结果！）

X的值为4，入库和出库事务读取的是同一值，入库事务的写入覆盖了出库事务的写入，导致出库的减 1 操作丢失。这是典型的"丢失更新"问题。
（2）为了解决上述问题，引入独占锁指令XLock(X)对数据X进行加锁，解锁指令Unlock(X)对数据X进行解锁。入库操作用下标I表示，如XLock _I（X）；出库操作用下标O表示，如Unlock _o（X）。请根据上述的并发序列，给出一种可能的执行序列，使其满足2PL协议。

增长阶段：只能获取锁，不能释放锁
收缩阶段：只能释放锁，不能获取锁
XLock_I(X) 只要在 Unlock_I(X) 之前获取到，并且获取之后不再申请新锁，就不违反 2PL。

XLOCK_O(X)          -- 出库加锁
ao = Ro(X)
XLOCK_I(X)          -- 入库加锁（等待）
ao = ao - 1
Wo(ao, X)
UNLOCK_O(X)         -- 出库解锁（入库等待结束，获得锁）
aI = RI(X)
aI = aI + 1
WI(aI, X)
UNLOCK_I(X)         -- 入库解锁

【问题2】（8分）
下面是用SQL实现的出入库业务程序的一部分，请补全空缺处的代码。

CREATE PROCEDURE IOstack(IN BookNo VARCHAR(20), IN Amount INT){
       //输入合法性验证
       if not(Amount=1 or Amount=-1) return -1;
       //修改图书表当前位置
       UPDATE 图书 SET 当前位置=GetPos(BookNo,Amount)//系统生成
       WHERE     （a）   ;
       if error then {ROLLBACK;return -2;}
       //修改在库数量
       UPDATE 书目 SET 在库数量=     （b）     
       WHERE EXISTS(
                    SELECT  *
                    FROM 图书
                    WHERE  书号=BookNo AND
                                （c）        );
       if error then {ROLLBACK;return -3;}
          （d）   ;
       return 0;
}

首先传入的参数中，题目要求Amount要么是1要么是-1，不然就终止整个存储过程。阅读完整个语句，发现题目要实现出入库，却没有相应的判断。因此Amount就是用来控制表示出入库时总量是加还是减的。这题也只是考虑了一次借和还一本的情况。

UPDATE 图书 SET 当前位置=GetPos(BookNo,Amount)//系统生成

WHERE （a） ;

GetPos 是题目中虚构的系统函数，不是真实的 SQL 函数。

题目："读者归还一本图书时，进行入库操作：系统根据当前书架的空余位置自动生成该本书的存放位置"

GetPos就是根据传入的函数生成位置，然后存到图书表的当前位置里面。或许是-1时它还能清空当前位置来满足题目要求，总之对做题没有影响。要注意的是Where子句限定条件，我们要UPDATE的是哪一行？

书号 = BookNo

前面我们已经分析了，Amount就是用加一减一来表示借和还。那么算在库数量就很明确了

UPDATE 书目 SET 在库数量=在库数量 + Amount

WHERE EXISTS

子查询没查询到任何行则为假，查询到则为真

UPDATE 书目 SET 在库数量 = 【填：在库数量 + Amount】
WHERE EXISTS(
    SELECT *
    FROM 图书
    WHERE 书号 = BookNo AND
              (c)
);

UPDATE在没有限定条件的情况下会处理所有行。这里UPDATE也确实不是找出某一行来更新，而是对所有行进行WHERE EXISTS的真假判断。这才是其中的子句发挥作用的时候。

题目说到业务流程要用ISBN，我们这里都还没用上，这ISBN一看就是用来跨表联合查询的。题目中我们传进来的参数是书号，要更新的在库数量在另一张表里面。

书目（ISBN，书名，出版社，在库数量）
图书（书号，ISBN，当前位置）

题目中有非常重要的信息：每一条书目信息对应多本相同的图书，每一本图书只能对应一条书目。

（c）图书.ISBN=书目.ISBN

这里很容易想不明白，为什么不直接

WHERE 图书.书号 = BookNo AND 图书.ISBN = 书目.ISBN 来确定唯一的一行，为什么还要多此一举套一个EXIST呢？

WHERE 图书.书号 = BookNo AND 图书.ISBN = 书目.ISBN 假如能找到，我们就直接去改它。

相当于是EXIST判断这个条件为真，我们就改这行。从最终效果上来说是等效的。

图书和书目是两个不同的表，有些数据库并不支持UPDATE SET FORM 书目, 图书 ，考试题目就通过间接的方式来考察了。

if error then {ROLLBACK; return -3;}
(d);
return 0;

所有操作成功完成，需要提交事务。注意前面有 ROLLBACK，说明这是一个事务块，即使题目省略了显式的 BEGIN TRANSACTION。正常结束时还是需要 COMMIT。

8.代码/SQL语句分析并发

【说明】
某航空售票系统负责所有本地起飞航班的机票销售，并设有多个机票销售网点。以下为E-SQL编写的部分售票代码：

……
EXEC SQL SELECT balance INTO :x FROM tickets WHERE flight=:flightno;
Printf（“航班%s当前剩余机票数为：%d\n请输入购票数：”，flightno，x）;
Scanf（“%d”，&a）;
EXEC SQL UPDATE tickets SET balance=:x-:a WHERE flight=:flightno;

请根据上述描述，完成下列问题：

【问题1】（5分）

上述售票程序，在并发状态下，可能发生什么错误？产生这种错误的原因是什么？

EXEC SQL SELECT balance INTO :x FROM tickets WHERE flight = :flightno;
-- 把剩余票数读到变量 x

printf("航班%s当前剩余机票数为：%d\n请输入购票数：", flightno, x);
scanf("%d", &a);
-- 用户输入购买数量 a

EXEC SQL UPDATE tickets SET balance = :x - :a WHERE flight = :flightno;
-- 用 x - a 更新剩余票数

注意题目关键词，在并发状态下，我们看到的这个执行SQL语句的脚本，它并不是同时只有一个人在操作。而是有多个事务。

在多个人同时购买的情况下，多个事务读取到相同的剩余票数这个初值，然而根据后购买的人计算得出的剩余票数会覆盖掉先购买的人计算得出的剩余票数。产生了丢失更新错误。

比如图中，两个人同时读取到剩余票数16，T1先购买一张，剩余15，T2也是基于初值16购买2张剩余14。最终剩余的数量是14，凭空丢了一张票。

【问题2】（6分）

代码封装事务/加锁分析

若将上述代码封装成一个完整的事务，则：

（1）在并发请求下的响应效率会存在什么问题？

（2）分析产生效率问题的原因。

并发响应效率低，大量事务阻塞等待。

原始代码有三条独立的 SQL，每条语句都是一个自动提交的独立事务。"封装成一个完整的事务"指的就是：

BEGIN TRANSACTION;
    SELECT balance INTO :x ...;
    // 用户输入
    UPDATE tickets SET ...;
COMMIT;

隐含的意思就是：为了解决问题1（丢失更新），我们将代码封装成事务，并加上适当的锁

丢失更新的问题是，多个事务读到的都是初始值，那么基于这个初始值做计算，肯定会出问题。我们只给UPDATE加写入的锁，是没用的。

所以我们必须要阻止其他人在你"计算期间"读到旧值。不可以加S锁，不可以让其他人读，必须加X锁。这个事物已经持有X锁了，后续就不用考虑什么加锁的问题了。

我们前面已经学习分析过，BEGIN TRANSACTION;之后，要么是commit释放，要么是rollback释放。锁一直不释放，事务持有锁时间包括了用户操作，过于长。其他售票网点的操作被阻塞。

（3）给出解决方案。

将事务适当划分，用户查询置放于外部事务，该事务只负责购票。

查询阶段 SELECT → 显示给用户 → 用户输入 ❌ 不在事务里

更新阶段 SELECT FOR UPDATE → 校验 → UPDATE ✅ 在事务里

第一次 SELECT 只是给用户看看查询时大概还剩多少，第二次 SELECT FOR UPDATE 才是真正锁住、确认、更新的依据。

【问题3】（4分）

下面是改写的存储过程，其中flightno为航班号；a为购票数；result为执行状态：1表示成功，0表示失败；表tickets中的剩余机票数balance具有大于等于零约束。请补充完整。

CREATE PROCEDURE buy_ticket(char[] flightno IN,    （a）  , int result OUT)
        AS
    BEGIN
        .........
        UPDATE tickets SET balance=   （b）  
            WHERE flight= flightno;
        if (SQLcode<>SUCCESS)  {  //SQLcode为SQL语句的执行状态
               （c）  ;
            result = 0;   return;
        ｝
        COMMIT;
           （d） 
END

CREATE PROCEDURE buy_ticket(char[] flightno IN, （a） , int result OUT)

题目：flightno为航班号；a为购票数；result为执行状态。从整个题目中都没有找到(a)这个缺失的传入参数，从业务需求分析应该是购票数。

注意：不要忘了存储过程的参数，有参数类型、参数名、参数模式。

还有的题顺序不一样，这里我们参考其他选项。

所以 (a) 填：int a IN

购票数没有传出、返回的需求

UPDATE tickets SET balance=（b）

不论里面的判断条件是什么，这里都是要更新剩余票数

所以 (b) 填：balance - a

1表示成功，0表示失败

if (SQLcode<>SUCCESS) { //SQLcode为SQL语句的执行状态

（c） ;

result = 0; return;

执行失败了，要能联想到回滚事务，所以 (c) 填：ROLLBACK

观察程序结构，已有commit，已有rollback，既然有执行失败的result = 0，还差执行完毕的result = 1。且其他语句有return的题这里也需要添加。

（d）result=1; return;

分享

数据库系统工程师⑱：案例题4-存储过程/并发分析

1.SQL存储过程和并发控制

存储过程基本语法结构

SELECT INTO

多表关联查询

FOR UPDATE和COMMIT/ROLLBACK

触发器之UPDATE ON

FOR EACH的用法

TO_CHAR 格式化日期

TO_NUMBER 字符内容转成数字

并行调度和两段锁分析

2.SQL存储过程和并发控制

游标

触发器之INSERT ON

触发器中的when

触发器中的NEW

并行调度之 Read Committed（读已提交） 隔离级别

MVCC

3.SQL存储过程和并发控制

回顾游标知识

4.SQL存储过程和并发控制

触发器之UPDATE FOR

只有 FOR EACH ROW 才能使用 OLD 和 NEW

并行分析之读脏数据

5.SQL存储过程/隔离级别和锁

隔离级别

两段锁协议/提交读隔离级别

6.锁分析和SQL存储过程

RETURN

BEGIN TRANSACTION

关闭游标/判断UPDATE执行是否成功

WHILE和FETCH

7.锁分析和SQL存储过程

WHERE EXISTS

8.代码/SQL语句分析并发

代码封装事务/加锁分析

评论

并行调度之 Read Committed（读已提交）隔离级别