数据库事务相关知识解析

### 数据库事务相关知识解析 #### 1. 同步和异步提交 在数据库操作中，同步提交和异步提交是两种不同的提交方式，它们适用于不同类型的应用程序。 - **适用场景**： - 同步提交：适用于与用户直接交互的应用程序，这类应用程序会向用户报告“提交完成”，因为需要确保数据的即时一致性和持久性。 - 异步提交：仅适用于批处理应用程序，这些应用程序在失败时能够自动重启。例如一些后台运行、不与用户直接交互的应用。 - **使用建议**：对于非批处理应用程序，应使用同步提交。如果使用异步提交的`COMMIT WRITE NOWAIT`功能，需要考虑应用程序被告知提交已处理，但后续提交被撤销的情况。如果无法应对这种情况或提交的更改丢失会产生严重后果，则不应使用异步提交。 #### 2. 非分布式 PL/SQL 块中的提交 自 Oracle 6 版本引入 PL/SQL 以来，它一直透明地使用异步提交。这是因为 PL/SQL 在某种程度上类似于批处理程序，最终用户在程序完全完成之前不知道过程的结果。 - **异步提交的使用范围**：异步提交仅用于非分布式 PL/SQL 代码块。如果涉及多个数据库，为了确保提交的持久性，必须使用同步协议，否则可能会出现一个数据库提交更改而另一个数据库未提交的情况。 - **PL/SQL 异步提交示例**： ```sql -- 创建表 T EODA@ORA12CR1> create table t 2 as 3 select * 4 from all_objects 5 where 1=0 6 / Table created. -- 创建 PL/SQL 过程 p EODA@ORA12CR1> create or replace procedure p 2 as 3 begin 4 for x in ( select * from all_objects ) 5 loop 6 insert into t values X; 7 commit; 8 end loop; 9 end; 10 / Procedure created. ``` 上述代码逻辑上等同于以下代码： ```sql EODA@ORA12CR1> create or replace procedure p 2 as 3 begin 4 for x in ( select * from all_objects ) 5 loop 6 insert into t values X; 7 commit write NOWAIT; 8 end loop; 9 10 -- 内部调用以确保 LGWR 写入重做日志 11 -- make internal call here to ensure 12 -- redo was written by LGWR 13 end; 14 / Procedure created. ``` PL/SQL 在将控制权返回给客户端应用程序之前，会确保生成的最后一点重做日志安全地记录到磁盘，从而保证 PL/SQL 代码块及其更改的持久性。 #### 3. 完整性约束与事务 完整性约束的检查时机是数据库操作中的一个重要方面。 - **默认检查时机**：默认情况下，完整性约束在整个 SQL 语句处理完成后进行检查。此外，还有可延迟约束，允许将完整性约束的验证推迟到应用程序发出`SET CONSTRAINTS ALL IMMEDIATE`命令或提交事务时。 - **立即约束（IMMEDIATE Constraints）**：在通常的立即模式下，完整性约束在整个 SQL 语句处理完成后立即检查。例如，在 PL/SQL 存储过程中有多个 SQL 语句时，每个 SQL 语句执行后会立即验证其完整性约束，而不是在存储过程完成后。这是因为单个 SQL 语句可能会使表中的个别行暂时不一致，如果在语句执行过程中检查约束，可能会导致 Oracle 拒绝结果，即使最终结果是正确的。 - **示例**： ```sql -- 创建表 t EODA@ORA12CR1> create table t ( x int unique ); Table created. -- 插入数据 EODA@ORA12CR1> insert into t values ( 1 ); 1 row created. EODA@ORA12CR1> insert into t values ( 2 ); 1 row created. -- 提交事务 EODA@ORA12CR1> commit; Commit complete. -- 执行多行更新 EODA@ORA12CR1> update t set x=x-1; 2 rows updated. ``` 如果 Oracle 在每行更新后检查约束，更新操作有 50% 的概率失败。但由于 Oracle 会耐心等待到语句结束，所以该语句能够成功执行，因为最终结果没有重复值。 - **可延迟约束（DEFERRABLE Constraints）与级联更新**：从 Oracle 8.0 开始，支持延迟约束检查，这对于某些操作非常有利，例如将主键的更新级联到子键。不过，修改主键的级联更新通常被认为是一种糟糕的做法，除非是为了纠正错误信息。 - **示例**： ```sql -- 创建父表 parent EODA@ORA12CR1> create table parent 2 ( pk int primary key ) 3 / Table created. -- 创建子表 child EODA@ORA12CR1> create table child 2 ( fk constraint child_fk_parent 3 references parent(pk) 4 deferrable 5 initially immediate 6 ) 7 / Table created. -- 插入数据 EODA@ORA12CR1> insert into parent values ( 1 ); 1 row created. EODA@ORA12CR1> insert into child values ( 1 ); 1 row created. -- 尝试更新父表主键 EODA@ORA12CR1> update parent set pk = 2; update parent set pk = 2 * ERROR at line 1: ORA-02292: integrity constraint (EODA.CHILD_FK_PARENT) violated - child record found -- 更改约束模式为延迟 EODA@ORA12CR1> set constraint child_fk_parent deferred; Constraint set. -- 再次尝试更新父表主键 EODA@ORA12CR1> update parent set pk = 2; 1 row ```