Oracle—oracle事务

Oracle事务

事务是啥？

事务在数据库中是工作的逻辑单元，单个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制，可以确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。

事务有啥特性？

SQL92标准对数据库事务的特点进行如下定义：

原子性(Atomicity)
一个事务里面所有包含的SQL语句都是一个整体，是不可分割的，要么不做，要么都做。
一致性(Consistency)
事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。
隔离性(Isolation)
数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务在并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。
**持久性 (Durability) **
当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。

一组SQL语句操作要成为事务，数据库管理系统必须保证这组操作的原子性（Atomicity）、一致性（consistency）、隔离性（Isolation）和持久性（Durability），这就是ACID特性。
以下是解释如何在Oracle 中使用事务的主题列表：
COMMIT语句
ROLLBACK语句
SET TRANSACTION语句
LOCK TABLE语句

Oracle Commit语句（事务提交）

在 Oracle 中，COMMIT 语句可以用来提交当前事务的所有更改。提交后，其他用户将能够看到您的更改。

COMMIT语句语法

COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];

WORK:可选的。它被 Oracle 添加为符合 SQL 标准。使用或不使用 WORK 参数来执行 COMMIT 将产生相同的结果。
COMMENT clause：可选的。 它用于指定与当前事务关联的注释。 该注释最多可以包含在单引号中的 255 个字节的文本中。 如果出现问题，它将与事务ID一起存储在名为 DBA_2PC_PENDING 的系统视图中。
WRITE clause：可选的。 它用于指定将已提交事务的重做信息写入重做日志的优先级。 用这个子句，有两个参数可以指定：
    WAIT 或 NOWAIT (如果省略，WAIT是默认值)
    IMMEDIATE 或 BATCH(IMMEDIATE是省略时的默认值)
FORCE clause：可选的。 它用于强制提交可能已损坏或有疑问的事务。 有了这个子句，可以用3种方式指定FORCE：
   FORCE'string'，[integer]或FORCE CORRUPT_XID'string' 或 FORCE CORRUPT_XID_ALL

注意：

• 必须拥有 DBA 权限才能访问系统视图 - DBA_2PC_PENDING和V$CORRUPT_XID_LIST。
• 必须拥有 DBA 权限才能指定COMMIT语句的某些功能。

COMMIT语句示例

COMMIT;
-- 此 COMMIT 示例将执行与以下相同的操作：
COMMIT WORK WRITE WAIT IMMEDIATE;
-- 在此示例中，隐含了 WORK 关键字，并且省略 WRITE 子句将缺省为 WRITE WAIT IMMEDIATE，因此它与前面的 COMMIT 语句是等效的。

Oracle ROLLBACK语句（回滚事务）

在 Oracle 中，ROLLBACK 语句可以用来撤销当前事务或有问题的事务。

ROLLBACK语法

ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name  | FORCE 'string' ];

--WORK​：可选的。 它被 Oracle 添加为符合 SQL 标准。 使用或不使用 WORK 参数来发出 ROLLBACK 会导致相同的结果。
​--TO SAVEPOINT savepoint_name​：可选的。 ROLLBACK语句撤消当前会话的所有更改，直到由 savepoint_name --指定的保存点。 如果省略该子句，则所有更改都将被撤消。
​--FORCE​ ​‘string’：可选的。它用于强制回滚可能已损坏或有问题的事务。 使用此子句，可以将单引号中的事务
--ID指定为字符串。 可以在系统视图中找到名为 DBA_2PC_PENDING 的事务标识。
--必须拥有 DBA 权限才能访问系统视图：DBA_2PC_PENDING 和 V$CORRUPT_XID_LIST。
--您无法将有问题的事务回滚到保存点。

ROLLBACK语语法示例

ROLLBACK;
ROLLBACK WORK;

Savepoint

可以通过两种方式将 ROLLBACK 写入保存点：

ROLLBACK TO SAVEPOINT savepoint1;

ROLLBACK WORK TO SAVEPOINT savepoint1;

Force

强制回滚一个有问题的事务
可以通过两种方式编写有问题事务的 ROLLBACK：

ROLLBACK FORCE '22.14.67';

ROLLBACK WORK FORCE '22.14.67';

Oracle SET TRANSACTION语句（设置事务）

在 Oracle 中，SET TRANSACTION 语句可以用来设置事务的各种状态，比如只读、读/写、隔离级别，为事务分配名称或将事务分配回滚段等等。

SET TRANSACTION语法

SET TRANSACTION [ READ ONLY | READ WRITE ]
                [ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ]
                [ USE ROLLBACK SEGMENT 'segment_name' ]
                [ NAME 'transaction_name' ];

● READ ONLY：可以将事务设置为只读事务。

   ● READ WRITE：可以将事务设置为读/写事务。

   ● ISOLATION LEVEL： 如果指定，它有两个选项：

        1.ISOLATION LEVEL SERIALIZE：如果事务尝试更新由另一个事务更新并未提交的资源，则事务将失败。

        2.ISOLATION LEVEL READ COMMITTED：如果事务需要另一个事务持有的行锁，则事务将等待，直到行锁被释放。

   ● USE ROLLBACK SEGMENT：可选的。 如果指定，它将事务分配给由 'segment_name' 标识的回退段，该段是用引号括起来的段名称。

   ● NAME：为 'transaction_name' 标识的事务分配一个名称，该事务用引号括起来。

SET TRANSACTION 示例

只读

首先，下面来看看看如何使用SET TRANSACTION语句将事务设置为只读。
此示例会将事务设置为只读，并为其分配“RO_example”的名称。

SET TRANSACTION READ ONLY NAME 'RO_example';

读写

下面是如何使用 SET TRANSACTION 语句将事务设置为读/写的示例：

SET TRANSACTION READ WRITE NAME 'RW_example';

use rollback segment 实例

一般在做报表查询的时候，可以先设置该查询事务为一个只读事务，以避免出现写操作。

DECLARE
  l_name VARCHAR2(100);
  l_age  NUMBER;
  l_sex  VARCHAR2(2);
BEGIN
  -- 为保证set transaction是事务的第一条语句，先使用commit或rollback来结束掉前面可能存在的事务
  COMMIT;
  -- 使用name给事务命名
  SET TRANSACTION READ ONLY NAME '查询报表';
  SELECT NAME
    INTO l_name
    FROM student
   WHERE student_id = 1001;
  SELECT age
    INTO l_age
    FROM student
   WHERE student_id = 1001;
  SELECT sex
    INTO l_sex
    FROM student
   WHERE student_id = 1001;
  -- 终止只读事务
  COMMIT;
  dbms_output.put_line('输出：' || l_name || l_age || l_sex);
END;

Oracle LOCK TABLE语句（锁表）

在 Oracle 中，LOCK TABLE 语句可以用来锁定表、表分区或表子分区

LOCK TABLE语法

LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];
tables：用逗号分隔的表格列表。
lock_mode：它是以下值之一
WAIT：它指定数据库将等待(达到指定整数的特定秒数)以获取 DML 锁定。
NOWAIT：它指定数据库不应该等待释放锁。

lock_mode	描述
ROW SHARE	允许同时访问表，但阻止用户锁定整个表以进行独占访问。
ROW EXCLUSIVE	允许对表进行并发访问，但阻止用户以独占访问方式锁定整个表并以共享方式锁定表。
SHARE UPDATE	允许同时访问表，但阻止用户锁定整个表以进行独占访问。
SHARE	允许并发查询，但用户无法更新锁定的表。
SHARE ROW EXCLUSIVE	用户可以查看表中的记录，但是无法更新表或锁定SHARE表中的表。
EXCLUSIVE	允许查询锁定的表格，但不能进行其他活动。

LOCK TABLE示例

下面是一个如何在 Oracle 中使用 LOCK TABLE 语句的例子：
这个例子会锁定 suppliers 表在共享模式，而不是等待锁定被释放。

LOCK TABLE suppliers IN SHARE MODE NOWAIT;