场景:
假设我们在对一个名为
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 索引的方法,我们可以有效避免在生产环境中直接添加主键约束时可能出现的锁定问题。这种做法不仅优化了数据库的性能,也保证了数据操作的流畅性。