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

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

背景

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

 

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