12 事务处理和并发控制

1.背景简介

       事务是Oracle中进行数据库操作的基本单位。当需要一次性执行SQL语句块中的所有语句时，可以采用事务的方式，用以保证数据的一致性和完整性。

2.应用场景及代码

（1）对事务的操作

sqlserver中事务通过BEGIN TRANSACTION来标记开始，而oracle采用隐式事务。即从执行DML语句时事务开始，显式提交事务，使用COMMIT，或者在之后执行DCL,DDL语句也可提交事务(且无法回滚)。

若未提交前，数据库发生异常，则DML操作发生回滚。若正常断开或者退出(EXIT),则自动提交事务。

--在执行一系列DML语句之后，COMMIT显式提交
COMMIT;
--未提交之前回滚事务
ROLLBACK;
--设置事务保存点，可回滚至保存点（下例回滚后撤销更新操作）
INSERT ...;DELETE ...;
SAVEPOINT UPDATEPOINT;
UPDATE ...;
ROLLBACK TO UPDATEPOINT;

通过上述完整的控制，可以避免数据的脏读。

（2）行级锁，避免不可重复读

--A用户查询
SELECT * FROM STU WHERE NAME='111' FOR UPDATE；
-B用户更改(在A首先使用上述语句的情况下，该更新操作无法完成)
UPDATE STU SET AGE=12 WHERE NAME='111';

当第一个人执行SELECT语句时加上了“FOR UPDATE”参数，表示为该语句查询到的记录加上锁，其他事务不能再对该记录进行UPDATE操作。因此，当第二个用户试图对该记录进行UPDATE操作时被卡住了，呈现出“假死”的状态。

*ORACLE提供的锁机制分为乐观锁和悲观锁。而ORACLE的悲观锁有两种方式，从SQL语句看就是‘FOR UPDATE’和‘FOR UPDATE NOWAIT’两种形式。

（3）对数据表的加锁，解锁操作

--锁定表（独占模式） 若更新，会出现假死状态，可以查询
LOCK TABLE STU IN EXCLUSIVE MODE;
--查看被锁定的数据对象
SELECT ORACLE_USERNAME,OBJECT_ID,SESSION_ID FROM V$LOCKED_OBJECT; 
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=123;
--解锁表
--1.找出被锁定对象SESSION_ID对应的运行进程号
SELECT SID,SERIAL#,PROGRAM FROM SESSION WHERE SID=148;
--2.使用KILL命令结束对应的进程
ALTER SYSTEM KILL SESSION 'SID,进程号'；
--3.重复步骤1,2 直到视图V$LOCKED_OBJECT中被锁定STU表所对应的SESSION_ID全部被结束为止。

     锁定的数据对象会在正常重新连接数据库后自动解锁。锁是为了保证数据的一致性和完整性，大部分锁都由系统控制，无需干预。

（4）对程序包的操作

程序包定义分为程序包说明定义和程序包主体定义

--创建包
CREATE OR REPLACE PACKAGE STU_PAK IS
STU_REC STU%ROWTYPE;
--函数一
PROCEDURE DELETE_STU(SNO IN NUMBER);
--函数二
PROCEDURE QUERY(SNO IN NUMBER);
END PACKAGE;

--创建包主体
CREATE OR REPLACE PACKAGE BODY STU_PAK IS
PROCEDURE DELETE_STU(SNO IN NUMBER) IS BEGIN ...; END DELETE_STU;
PROCEDURE QUERY(SNO IN NUMBER) IS BEGIN ...; END QUERY;
BEGIN NULL;
END PACKAGE;

--使用程序包
BEGIN STU_PAK.QUERY('12457');
STU_PAK.DELETE_STU('34');
END;
--查看程序包
SELECT TEXT FROM ALL_SOURCE WHERE NAME = UPPER('STU_PAK');
SELECT * FROM ALL_SOURCE T WHERE T.TYPE='PACKAGE' AND T.NAME='STU_PAK';
--删除程序包
DROP PACKAGE BODY STU_PAK;
DROP PACKAGE STU_PAK;