Oracle数据库并发控制与锁机制详解

### Oracle数据库并发控制与锁机制详解 #### 1. 并发与一致性 在数据库操作中，并发与一致性是两个重要的概念。Oracle数据库的一个显著特点是能够同时实现并发和一致性，数据的读取操作不会被写入操作阻塞，反之亦然。这是Oracle与大多数其他关系型数据库的根本区别之一。 #### 2. 锁相关问题 在深入探讨Oracle使用的各种锁类型之前，有必要先了解一些常见的锁问题。这些问题很多是由于应用程序设计不当，未能正确使用数据库的锁机制而产生的。其中，“丢失更新”是一个经典的数据库问题，在多用户计算机环境中普遍存在。 ##### 2.1 丢失更新问题 丢失更新问题发生的过程如下： 1. 会话1（Session1）中的一个事务将一行数据检索到本地内存，并显示给终端用户User1。 2. 会话2（Session2）中的另一个事务检索同一行数据，并显示给另一个终端用户User2。 3. User1使用应用程序修改该行数据，应用程序更新数据库并提交，会话1的事务完成。 4. User2也修改该行数据，应用程序更新数据库并提交，会话2的事务完成。 在这个过程中，步骤3所做的所有更改都会丢失。例如，在一个员工信息更新界面，用户可以更改地址、工作号码等信息。如果应用程序没有使用锁机制，只是简单地使用SELECT和UPDATE命令，就可能出现丢失更新的情况。当User1更改地址并保存后，第二天查看记录时发现地址仍然是旧的，这是因为在User1读取数据后、修改之前，User2查询了同一记录，并且在User1更新后，User2又进行了更新，覆盖了User1的更改。 这种问题在一些缺乏数据库培训的GUI程序员编写数据库应用程序时经常出现。许多工具，如Oracle Forms和APEX，可以通过乐观锁机制透明地保护用户免受这种行为的影响，但也有很多其他工具（如手写的Visual Basic或Java程序）则没有提供这种保护。为了避免丢失更新，开发者可以采用悲观锁或乐观锁策略。 #### 3. 悲观锁 悲观锁在用户修改屏幕上的值之前立即生效。例如，当用户通过点击屏幕上的按钮表示要对特定行进行更新时，应用程序会立即对该行加锁，直到应用程序将用户的修改应用到数据库中的该行并提交。 悲观锁仅在有状态或连接环境中有用，即应用程序与数据库保持持续连接，并且在事务的生命周期内只有该应用程序使用该连接。这种方式在20世纪90年代初至中期的客户端/服务器应用程序中很常见，但随着应用服务器在90年代中后期的出现，这种连接方式变得不那么普遍了。 以下是使用悲观锁的具体操作步骤： 1. **查询数据**： ```sql $ sqlplus scott/tiger@PDB1 SQL> select empno, ename, sal from emp where deptno = 10; EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 ``` 2. **绑定变量**：假设用户选择更新MILLER行，应用程序需要绑定用户选择的值，以确保数据未被更改。 ```sql SQL> variable empno number SQL> variable ename varchar2(20) SQL> variable sal number SQL> exec :empno := 7934; :ename := 'MILLER'; :sal := 1300; PL/SQL procedure successfully completed. ``` 3. **加锁查询**：使用`FOR UPDATE NOWAIT`语句对该行加锁。 ```sql SQL> select empno, ename, sal from emp where empno = :empno and decode( ename, :ename, 1 ) = 1 and decode( sal, :sal, 1 ) = 1 for update nowait; EMPNO ENAME SAL ---------- ---------- ---------- 7934 MILLER 1300 ``` 这里使用`decode( column, :bind_variable, 1 ) = 1`是为了处理列值可能为NULL的情况，它是`where (column = :bind_variable OR (column is NULL and :bind_variable is NULL))`的简写。 执行上述查询可能会有以下三种结果： | 情况 | 结果 | | --- | --- | | 数据未更改 | 成功获取MILLER行，并对其加锁，其他会话无法更新该行，但可以读取。 | | 其他用户正在修改该行 | 会收到`ORA - 00054`资源繁忙错误，需要等待其他用户完成操作。 | | 数据已被更改 | 查询返回零行，说明屏幕上的数据已过时，应用程序需要重新查询并加锁数据。 | 4. **更新并提交**：成功加锁后，绑定新值，执行更新并提交。 ```sql SQL> update emp set ename = :ename, sal = :sal where empno = :empno; 1 row updated. SQL> commit; Commit complete. ``` 通过悲观锁，我们可以确保不会覆盖他人的更改，因为在更新之前验证了数据未被更改，并且加锁保证了在操作期间数据不会被其他会话修改。 #### 4. 乐观锁 乐观锁将所有锁操作推迟到更新操作执行之前。也就是说，在修改屏幕上的信息时不会获取锁，而是乐观地