阻止同一表中,两个并发会话插入相同记录的解决方案(原创)

阻止同一表中,两个并发会话插入相同记录的解决方案

背景

    两个并发会话,要并发访问一张表,并向表中插入记录,主键 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 的数据库,对其它的数据库,可能要从数据库隔离级别入手去解决了,比如:将隔离级别设为序列化的,在事务开始时,先去查询一下是否有同值记录存在,存在则退出,否则插入,序列化的隔离级别是性能大忌。

 

以上仅是本个的一些浅建,如有更好的解决方案可以说出来,大家探讨啊。

 

展开阅读全文

没有更多推荐了,返回首页