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

### 数据库并发控制与锁机制详解 在数据库操作中，并发控制是一个至关重要的话题，它直接影响着数据的一致性和系统的性能。本文将深入探讨数据库中的乐观锁和悲观锁、阻塞问题以及死锁问题，并提供相应的解决方案。 #### 1. 乐观锁与悲观锁 在数据库并发控制中，主要有乐观锁和悲观锁两种方式。 ##### 1.1 乐观锁 乐观锁通常使用哈希或校验和来实现。新增的列是虚拟列，不产生存储开销，其值在从数据库检索数据时计算，而非预先计算并存储在磁盘上。不过，计算哈希或校验和是 CPU 密集型操作，在 CPU 带宽稀缺的系统中需要谨慎使用。但这种方法对网络更友好，因为在网络上传输相对较小的哈希值，而不是逐列比较行的前后映像，可显著减少网络资源消耗。 ##### 1.2 悲观锁 悲观锁在 Oracle 数据库中表现出色，但需要与数据库保持有状态的连接，如客户端/服务器连接。由于锁不能跨连接保持，在当今许多场景下，悲观锁并不现实。 ##### 1.3 选择建议 对于大多数应用程序，建议使用乐观并发控制。作者倾向于使用带有时间戳列的版本列方法，它能提供长期的更新信息，计算成本低于哈希或校验和，并且在处理大列时不会出现潜在问题。如果需要在仍使用悲观锁方案的表中添加乐观并发控制，可选择 ORA_HASH 方法，它不会对现有应用程序造成太大干扰。 #### 2. 阻塞问题 阻塞发生在一个会话持有另一个会话请求的资源锁时，请求会话将被阻塞，直到持有会话释放锁。几乎所有情况下，阻塞都是可以避免的。数据库中常见的会导致阻塞的 DML 语句有 INSERT、UPDATE、DELETE、MERGE 和 SELECT FOR UPDATE。 ##### 2.1 阻塞的 INSERT INSERT 语句阻塞的情况较少，常见场景有两种： - 当表有主键或唯一约束，两个会话尝试插入相同值的行时，一个会话会阻塞，直到另一个会话提交（此时阻塞会话会收到重复值错误）或回滚（此时阻塞会话插入成功）。 - 当通过引用完整性约束关联的表，子表插入时依赖的父行正在创建或删除，插入操作可能会被阻塞。 为避免这种情况，可使用序列或 SYS_GUID() 内置函数生成主键或唯一列值。若无法使用这些方法，可通过内置的 DBMS_LOCK 包实现手动锁来避免问题。以下是一个示例： ```sql -- 创建表 SCOTT@ORA12CR1> create table demo ( x int primary key ); Table created. -- 创建触发器 SCOTT@ORA12CR1> create or replace trigger demo_bifer 2 before insert on demo 3 for each row 4 declare 5 l_lock_id number; 6 resource_busy exception; 7 pragma exception_init( resource_busy, -54 ); 8 begin 9 l_lock_id := 10 dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 ); 11 if ( dbms_lock.request 12 ( id => l_lock_id, 13 lockmode => dbms_lock.x_mode, 14 timeout => 0, 15 release_on_commit => TRUE ) not in (0,4) ) 16 then 17 raise resource_busy; 18 end if; 19 end; 20 / Trigger created. -- 插入数据 SCOTT@ORA12CR1> insert into demo(x) values (1); 1 row created. -- 模拟另一个会话插入相同数据 SCOTT@ORA12CR1> declare 2 pragma autonomous_transaction; 3 begin 4 insert into demo(x) values (1); 5 commit; 6 end; 7 / declare * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "SCOTT.DEMO_BIFER", line 14 ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER' ORA-06512: at line 4 ``` 该示例通过触发器和 DBMS_LOCK 包避免了因主键或唯一约束导致的插入阻塞。需要注意的是，此方法应作为短期解决方案，同时检查应用程序架构。 ##### 2.2 阻塞的 MERGE、UPDATE 和 DELETE 在交互式应用中，阻塞的 UPDATE 或 DELETE 通常意味着代码中存在更新丢失问题。可使用 SELEC