数据库系统之Deadlock项目练习

关于Deadlock死锁的介绍可以看我的这篇文章：
https://blog.youkuaiyun.com/Jifu_M/article/details/112486019

项目介绍

使用PL/SQL在sample数据库上进行事务操作，让它们并发处理导致死锁情况发生。
要模拟数据库事务的并发处理，请使用标准PL/SQL包DBMS_LOCK中的PL/SQL procedure SLEEP。
使用SQL*Plus的命令来展示运行结果
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

项目实现

T1的操作如下

--T1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE DELAY_1
is
SYSTS  VARCHAR(60);
BEGIN
 SELECT TO_CHAR(SYSTIMESTAMP)
 INTO SYSTS
 FROM DUAL;
DBMS_OUTPUT.PUT_LINE(SYSTS);
UPDATE project SET budget = 66666 WHERE p# = 1001;
DBMS_LOCK.SLEEP(10);                   /* 睡10秒钟*/
UPDATE employee SET salary = 88888 WHERE e# = '00100';
SELECT TO_CHAR(SYSTIMESTAMP)
INTO SYSTS
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(SYSTS);
END DELAY_1;
/
EXECUTE DELAY_1;
/

T2的操作如下：

--T2
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE DELAY_2
is
SYSTS  VARCHAR(60);
BEGIN
 SELECT TO_CHAR(SYSTIMESTAMP)
 INTO SYSTS
 FROM DUAL;
DBMS_OUTPUT.PUT_LINE(SYSTS);
UPDATE employee SET salary = 23333 WHERE e# = '00100';
DBMS_LOCK.SLEEP(10);        /* 睡10秒钟*/
UPDATE project SET budget = 55555 WHERE p# = 1001;
SELECT TO_CHAR(SYSTIMESTAMP)
INTO SYSTS
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(SYSTS);
END DELAY_2;
/
EXECUTE DELAY_2;
/

运行结果如下：

Error starting at line : 27 in command -
BEGIN DELAY_1; END;
Error report -
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "JFM.DELAY_1", line 11
ORA-06512: at line 1
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

死锁成功触发。
可以看到在T1中，我们先锁住project这个表中的budget并进行操作，之后我们让T1程序休眠10s，在此期间，我们在T2中进行操作。在T2中我们锁住employee 表中的salary并进行操作。之后让T2程序也休眠10s，此时T1程序休眠结束要进行下一步操作，T1也想要对employee 表中的salary进行操作，然而此时salary正在被T2锁住了。T2休眠结束后想要也想对project表中的budget进行操作，然而budget也被T1锁住了。

因此成功引起死锁报错。