Oracle 插入重复程序，尽管在插入之前测试了现有记录

嗨，

我有一个相当简单的pl/sql过程由java程序调用，当用户单击网站上的链接时，它本身会被调用。该过程包含2-3个参数，其主要目的是在表中插入1条记录 (基于参数)。(如果需要，则在LiveSQL link. Table中包含proc的一个版本，并更改了列名以保护无辜者。)

在插入之前，该过程测试即将插入的行是否已经存在。这是必要的，因为我们只是避免对这个程序传递的特定值进行欺骗，而涉及其他值的欺骗是可以的，所以没有唯一的约束。

如果行已经存在，则proc将此 “失败” 尝试插入日志记录表中 (这部分是通过错误处理完成的，我有点后悔并考虑在If的一个简单的其他部分重写，但这不太可能与我的问题有关)。

如果该行有not然而存在，然后它被插入，但我应该注意到，在测试和插入之间有一个快速的选择。

在大多数情况下，这都可以正常工作，但是一些重复的东西已经进入了表格!date_inserted字段上的时间戳表示重复插入的时间间隔为0到18秒。

我有一些想法，为什么这可能会发生 (多次点击和网络延迟导致多个会话和过程调用同时触发... 也许第一次会话提交之间的时间第二次会话测试和插入)，但在唯一的约束之外，在该过程中，我可以做些什么来阻止这些骗子潜入吗？

如果我测试插入中该行的存在，它会完全停止欺骗吗？类似:

insert into user_demo 
with new_rec as (
  select 
     1 as internal_id, 
     'ABC' as demo_code, 
     'blah' as user_demo_comment, 
     sysdate as date_inserted
  from dual
)
select *
from new_rec
where not exists
  (select 'x'
   from user_demo ud2 
   where ud2.internal_id = new_rec.internal_id
     and ud2.demo_code = demo_code);

谢谢!
菲尔

PS。如果您查看LiveSQL中的proc，请原谅gross varchar2类型可能是日期参数。我知道这不是最佳做法，但让你知道这是为了完全披露。

提前谢谢你，
菲尔

你在这里有一个并发问题。

默认情况下，Oracle数据库具有语句级一致性。这意味着DML只能看到语句开始时提交的更改。

所以如果两个人同时调用这个过程，重复值是不可避免的。流程可以是这样的:

T(ime)1 = 用户1运行选择，找不到任何行
T2 = 用户1插入一行
T3 = 用户2运行选择

此时用户1has not committed their changes。所以用户2看不到用户1插入的行。因此，T3处的查询不返回任何行，并且它们插入相同的值。

一种简单的方法是在提交之前将等待过程。这是为了给你足够的时间在第二个会话中运行进程，同时等待第一个完成:

insert into users 
  values ( 1, 1 );
  
insert into demos 
  values ( 'DEM', 'DEMO' );
commit;

create or replace procedure p_demo( 
  p_id           varchar2,  
  p_demo_code    varchar2, 
  clicked_on     varchar2 default to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') 
) as 

  l_internal_id        number;
  l_user_demo_comment  varchar2(100);

  cursor cur_demo is
    select 'x' 
    from user_demo 
    where internal_id = l_internal_id
    and demo_code = demo_code; 

  r_demo   cur_demo%rowtype;
  e_user_already_demoed  exception;

begin 

  select internal_id  
  into l_internal_id   
  from users  
  where user_facing_id = p_id; 
  
  open cur_demo;   
  fetch cur_demo into r_demo;     
    
  if cur_demo%found then
      close cur_demo;
      raise e_user_already_demoed;    
  else       
      select demo_desc||' '||clicked_on      
      into l_user_demo_comment    
      from demos    
      where demo_code = p_demo_code; 
    
       insert into user_demo (      
          internal_id, demo_code, user_demo_comment, date_inserted    
       )  values (
         l_internal_id, p_demo_code, l_user_demo_comment, sysdate    
       );        
       
       dbms_session.sleep ( 10 );
       commit; 
   end if; 
   
   close cur_demo; 
exception 
  when e_user_already_demoed then     
    dbms_output.put_line ( 'ALREADY DEMOED' );
  when no_data_found then     
    dbms_output.put_line ( 'NDF' );
  
END p_demo;
/

PRO session 1
exec p_demo ( 1, 'DEM' );
PRO in session 2
exec p_demo ( 1, 'DEM' );

PRO session 1 or 2
select *
from   user_demo;

INTERNAL_ID    DEMO_CODE    USER_DEMO_COMMENT           DATE_INSERTED          
             1 DEM          DEMO 09/29/2020 15:41:31    29-SEP-2020 15:41:31    
             1 DEM          DEMO 09/29/2020 15:41:32    29-SEP-2020 15:41:32  

Would it stop the dupes entirely if I test for the row's existence within the insert?

这里仍然有可能出现问题。第二个用户可以在第一次提交之前开始插入。所以查询不会看到新行，并插入一个重复的。

解决此问题的最简单方法是使用唯一约束。由于您只想防止值子集的重复，因此您需要基于函数的唯一索引。这将重复的允许值映射为null，将不允许的重复映射为列。

例如，对于代码UNQ，这仅允许您插入具有该值的一行:

create unique index ui 
  on user_demo ( case when demo_code = 'UNQ' then demo_code end );
  
insert into demos 
  values ( 'UNQ', 'Unique code' );
commit;

然后，您可以将整个过程重写为普通插入:

create or replace procedure p_demo( 
  p_id           varchar2,  
  p_demo_code    varchar2, 
  clicked_on     varchar2 default to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') 
) as 
begin 

  insert into user_demo (      
    internal_id, demo_code, user_demo_comment, date_inserted    
  )  values (
    ( select internal_id  
      from users  
      where user_facing_id = p_id
    ), 
    p_demo_code, (
      select demo_desc||' '||clicked_on      
      from demos    
      where demo_code = p_demo_code
    ), sysdate    
  );        
  
end p_demo;
/

此时，您还可以删除等待以查看效果。如果第一个会话尚未提交，则任何其他尝试插入代码UNQ的会话都将被阻止，直到完成:

PRO session 1
exec p_demo ( 1, 'UNQ' );
PRO session 2 - this will be blocked until session 1 commits/rollsback
exec p_demo ( 2, 'UNQ' );

PRO session 1
commit;

PRO session 2 
ORA-00001: unique constraint (CHRIS.UI) violated

select * from user_demo;

INTERNAL_ID    DEMO_CODE    USER_DEMO_COMMENT                  DATE_INSERTED          
             1 DEM          DEMO 09/29/2020 15:41:31           29-SEP-2020 15:41:31    
             1 DEM          DEMO 09/29/2020 15:41:32           29-SEP-2020 15:41:32    
             1 UNQ          Unique code 09/29/2020 15:46:40    29-SEP-2020 15:46:40