阻止同一表中,两个并发会话插入相同记录的解决方案
背景
两个并发会话,要并发访问一张表,并向表中插入记录,主键 ID 为自增长的序号,所以两个会话肯定会得到不同的主键 Id ,靠这个 ID 主键约束不能解决阻止插入重复记录的问题。
举例
系统存在如下一张表
create table test (
id integer ,
value varchar ( 20 ),
primary key ( id )
);
表中 ID 值来自业务需求上的自增长序列。
方案
方案一
在 test 表的 value 字段上建立一个唯一约束(如果表中的字段为多个,则表这些字段全部加到唯一约束上去即可)。
alter table test add constraints test_001 unique ( value );
优缺点
优点:此种方案最简单,几乎接触过数据库的人都可以做,而且省时省力。
缺点:影响性能,如果大批量的数据通过多个会话并发插入的话,数据库会成为性能瓶颈,因为每插入一条数据,数据库都要去检查唯一约束。
方案二
步骤 1 ,分配权限
为用数据库用户分配权限,因为下面的 trigger ,使用到了 dbms_lock 和 dbms_utility 包。
否则将报错误。(图片没法显示,错误码信息是:ORA-04098:触发器XXX无效且未通过重新验证)
以 sysdba 身份登陆数据库,为 imeg 用户(本人的数据库用户名为 imeg )分配权限,命令如下:
huawei:~ # su - oracle
oracle@huawei:~> sqlplus '/as sysdba';
SQL> grant execute on dbms_lock to imeg;
Grant succeeded.
SQL> grant execute on dbms_utility to imeg;
Grant succeeded.
-- 可能上面的 grant 是多余的(没测试),只要下面这句就可以了。在此有点脱裤子放屁的感觉。
SQL> grant execute any procedure to imeg;
Grant succeeded.
注意:权限分配了以后,可能要过一段时间才会生效,我操作上就是这样,分配完直接操作会报错。不知是 Oracle 的什么原因所至。作为一个疑问,希否定知道的可以告知。在此留下答题卡:
答: ____________________________________________________________________________
步骤 2 ,建立触发器
使用 trigger ,在 test 表上建立如下的 trigger
create or replace trigger test_trigger
before insert on test
for each row
declare
l_lock_id number ;
resource_busy exception ;
pragma exception_init ( resource_busy, - 54 );
begin
l_lock_id :=
dbms_utility.get_hash_value( :new.value, 0 , 1024 );
if ( dbms_lock.request
( id => l_lock_id,
lockmode => dbms_lock.x_mode,
--timeout => 0,
-- 此处的 timeout 是为了让第二个会话如果存在唯一值冲突时,是否立即返回,默认为 oracle 的最大时间,等第一个会话提交时返回。如果为 0 ,则表示如果有冲突,不等第一个会话提交,直接返回错误信息。
release_on_commit => TRUE ) <> 0 )
then
raise resource_busy;
end if ;
end ;
步骤 3 ,将应中的 SQL 语句改为 merge
会话 1 :
merge into test_meger t1 using ( select 'value' value from dual) t2 on (t1.value=t2.value)
--when matched then
-- dbms_output.put_line('null')
when not matched then
insert (t1.id,t1.value) values ( 2 , 'value' );
会话 2 :
merge into test_meger t1 using ( select 'value' value from dual) t2 on (t1.value=t2.value)
--when matched then
-- dbms_output.put_line('null')
when not matched then
insert (t1.id,t1.value) values ( 3 , 'value' );
会话执行描述
1、 会话 1 提交后,会话 2 执行:
由于会话 2 的 merge 的 on 条件, t1.value=t2.value 此时可以看到会话 1 提交的数据,所以会话 2 什么也不错。
可以消除 ID 不同的重复记录。
2、 会话 1 执行,但未提交,会话 2 执行:
由于会话 1 没有提交,会话 2 的 merge 的 on 条件, t1.value=t2.value 此时可以看不到会话 1 提交的数据,但是由于步骤 2 的触发器,使会话 2 处于阻塞状态,得不到执行。如果此时去掉触发器关于 timeout 的注释,会话 2 将直接报错返回,而不会阻塞。
3、
优缺点
优点:将 timeout=> 0 加上,可以让会话 2 不用等待会话 1 提交,如果存在相同的值,则直接返回。减少了会话 2 的等待时间。
缺点:如果会话 1 没提交,而会话 2 由于 timeout=> 0 而报错返回了,则会丢失本来应该插入的记录。另外,这个同样存在效率问题,如果电信级的应用,不推荐用这种做法,可能需要在应用层进行序列化控制,应用层同样有缺点,比如:怎么解决应用层集群并发写入的问题。有好的解决方案,还请不吝赐教啊。
总结
上述的解决方案二,仅适合于 oracle 的数据库,对其它的数据库,可能要从数据库隔离级别入手去解决了,比如:将隔离级别设为序列化的,在事务开始时,先去查询一下是否有同值记录存在,存在则退出,否则插入,序列化的隔离级别是性能大忌。
以上仅是本个的一些浅建,如有更好的解决方案可以说出来,大家探讨啊。