在数据库管理中，正确设置主键对于保证数据的完整性和性能至关重要。然而，在生产库上直接对表添加主键约束时，我们可能会遇到锁定问题，特别是在高并发的环境中。本文将通过一个实际案例来探讨如何解决这一问题。

场景：

假设我们在对一个名为 test 的表设置主键。过程中涉及到三个会话（session）：

会话1 : 首先尝试通过创建索引，然后添加主键约束的方式来设置主键。


create 
index pk_test 
on test 
(ID
)
;



alter 
table test 
add 
constraint pk_test 
primary 
key 
(ID
) 
using 
index
;

会话2 : 同时，另一个会话尝试查询 test 表。
```
select * from test;
```
这时，会话2会被挂起（hang），等待锁释放。
会话3 : 对当前的共享池内容进行导出（dump），发现表的处理器（handler）上存在排他模式（X 模式）的锁（LOCK）和排他模式的引脚（PIN）。

分析显示，在添加主键约束的过程中，系统尝试在表处理器上获取排他模式的锁，即库缓存锁（library cache lock），而 select 操作则需要在表上获取共享模式（S 模式）的锁，因此发生冲突。

解决方案：

针对以上问题，推荐的解决办法是在进行添加主键操作之前，先创建一个 unique 索引。这样不仅可以保证字段的unique 性，还能显著缩短因添加主键约束而产生的锁表时间。此外，确保涉及的字段有非空约束，以满足主键的基本要求。


-- 步骤1: 创建unique索引



create 
unique 
index pk_test 
on test 
(ID
)
;







-- 步骤2: 添加主键约束



alter 
table test 
add 
constraint pk_test 
primary 
key 
(ID
) 
using 
index
;

结论：

通过预先创建 unique 索引的方法，我们可以有效避免在生产环境中直接添加主键约束时可能出现的锁定问题。这种做法不仅优化了数据库的性能，也保证了数据操作的流畅性。

ITPub博客

oracle数据库如何正确建主键

场景：

解决方案：

结论：

最新文章