oracle并发选择更新和插入

Question

我有一个包含两列的表：k（主键）和value。我想：

1. 选择k进行更新，如果找不到k，则插入默认值的新行。
2. 对返回的值（存在的或新插入的行值）进行一些处理。
3. 更新行并提交。

是否可以使此“选择更新并在未找到时插入默认值”？

如果将(1)实现为select/check if found/insert if not found，我们会遇到并发问题，因为两个会话可能会在不存在的键上并发地进行选择，两个会话都会尝试插入，其中一个实例会失败。

在这种情况下，所需的行为是以原子方式执行 select/insert，其中一个实例执行它，第二个实例保持锁定状态，直到第一个实例提交，然后使用第一个实例插入的值。

我们实现它总是做一个“插入...如果不存在.../commit“，则在”选择更新“之前，但这意味着在不太可能需要时始终尝试插入。

有没有办法在一个sql语句上实现？

谢谢！！

Answer 1

使用序列生成代理主键而不是使用自然键。如果您有一个真正的自然键，那么两个用户同时提交相同值的可能性极小。

有几种方法可以自动生成主键。我更喜欢使用序列默认值，如下所示：

create sequence test_seq;

create table test1
(
    k number default test_seq.nextval,
    value varchar2(4000),
    constraint test1_pk primary key(k)
);

如果无法切换到代理密钥或真正的自然密钥：

将“insert…if not exist…/commit”更改为更简单的“insert…（if not-exist）”，并在单个事务中执行所有操作。在不同的会话中插入相同的主键，即使未提交，在Oracle中也是不可能的。虽然＜code＞选择

如果两个会话尝试同时插入相同的主键，则第二个会话将挂起，并且当第一个会话最终提交时，第二个会话将失败，并出现异常“ORA-00001：违反唯一约束 （X.Y）”。让该异常成为您了解用户何时提交重复值的标志。您可以捕获应用程序中的异常，并让用户重试。

Answer 2

<代码>选择...for update是您应该做的第一步；没有它，您就不能“保留”该行以供进一步处理(除非您愿意以独占模式锁定整个表；如果“处理”不花时间，那也是一种选择，特别是如果没有很多用户会这么做的话)。

如果row存在，剩下的就很简单了——处理它，更新它，提交。

但是，如果它不存在，您将不得不插入一个新行(正如您所说的)，这里有一个两个(或更多)用户插入相同值的问题。

为了避免它，创建一个函数

< li >将为新行返回唯一的< code>ID值 < li >是一个自主事务 < ul > < li >为什么？因为您在其中执行DML(更新或插入)，并且您不能在函数中执行该操作，除非它是一个自治事务

用户必须使用该函数来获取下一个ID值。这是一个示例：您需要一个保存上次使用的ID的表（my_id）（每个通过该函数访问它的用户都将创建一个新值）。

表格:

SQL> create table my_id (id number);

Table created.

功能：

SQL> create or replace function f_id
  2    return number
  3  is
  4    pragma autonomous_transaction;
  5    l_nextval number;
  6  begin
  7    select id + 1
  8      into l_nextval
  9      from my_id
 10      for update of id;
 11
 12      update my_id set
 13        id = l_nextval;
 14
 15    commit;
 16    return (l_nextval);
 17
 18  exception
 19    when no_data_found then
 20      lock table my_id in exclusive mode;
 21
 22      insert into my_id (id)
 23      values (1);
 24
 25      commit;
 26      return(1);
 27  end;
 28  /

Function created.

用它作为

SQL> html" target="_blank">select f_id from dual;

      F_ID
----------
         1

SQL>

就这样…您将使用的代码如下：

SQL> create table test
  2    (id      number constraint pk_test primary key,
  3     name    varchar2(10),
  4     datum   date
  5    );

Table created.

SQL> create or replace procedure p_test (par_id in number)
  2  is
  3    l_id test.id%type;
  4  begin
  5    select id
  6      into l_id
  7      from test
  8      where id = par_id
  9      for update;
 10
 11    update test set datum = sysdate where id = par_id;
 12  exception
 13    when no_data_found then
 14      insert into test (id, name, datum)
 15        values (f_id, 'Little', sysdate);         --> function call is here
 16  end;
 17  /

Procedure created.

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:21

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:21   --> row was inserted

SQL> exec p_test (1);

PL/SQL procedure successfully completed.

SQL> select * from test;

        ID NAME       DATUM
---------- ---------- -------------------
         1 Little     04.09.2021 20:49:30   --> row was updated

SQL>

Answer 3

查看 k 是否可用

从表中选择 * 其中 k = 更新值

如果没有返回行，则它不存在。插入它：

INSERT INTO TABLE（k， col1， col2） VALUES （value， val1， default））