Oracle并发的选择性更新和插入

Question

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

我想要：

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

能否做到“选择以更新和插入默认值，如果没有找到”？

如果将（1）实现为选择/检查是否已找到/如果未找到则插入，我们会遇到并发问题，因为两个会话可以同时在不存在的键上进行选择，两个都会尝试插入，并且其中一个实例将失败。

在这种情况下，期望的行为是原子地执行选择/插入，其中一个实例执行它，并且第二个实例保持锁定，直到第一个实例提交，然后使用第一个实例插入的值。

我们始终通过“插入...如果不存在.../提交”来实现它，然后再进行“选择以进行更新”，但这意味着需要始终尝试插入，即使不太可能需要。

是否有任何方法在一个 SQL 语句中实现它？

谢谢！

- SNJ

---

听起来你想要使用merge，但是你在问题中没有提供足够的细节来描述你的确切问题。 - Gordon Linoff

听起来问题在于merge没有一个returning into选项。 - eaolson

merge解决了“插入或更新”的问题，而我需要解决的是“选择以进行更新或插入”。正如@eaolson所说，如果merge有一个“返回…”，我认为它可能是一个解决方案。 - SNJ

Answer 1

select ... for update 是你应该采取的第一步；如果没有它，你就不能为进一步处理“保留”该行（除非你愿意以独占模式锁定整个表；如果那个“处理”需要不花费时间，那也可以是一个选项，特别是如果将要执行此操作的用户不多）。

如果行存在，其余部分很简单-处理它，更新它，提交。

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

为了避免这种情况，创建一个函数，它

• 将为新行返回唯一的ID值
• 是一个自主事务
  • 为什么？因为你在其中执行DML（update或insert），除非它是自主事务，否则你不能在函数中执行DML

用户将不得不使用该函数来获取下一个ID值。以下是一个示例：你将需要一个表(my_id)，它保存了最后使用的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> 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>

- Littlefoot

---

在我的情况下，k不是自动生成的值，所以无法使用您的建议。 - SNJ

Answer 2

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

有几种自动生成主键的方法。我更喜欢使用序列默认值，像这样：

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中，即使未提交，也不可能在不同会话中插入相同的主键。虽然SELECT不会导致阻塞，但INSERT会。这种行为是Oracle隔离实现的例外，“ACID”中的“I”，在这种情况下，该行为可以为您带来好处。

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

- Jon Heller

---

k是一个自然键，而主键生成不是一个选项。是的，这很不可能，但即使如此也需要一个适当的解决方案。 - SNJ

Answer 3

查看 k 是否可用

SELECT * FROM 表名 WHERE k = 值 FOR UPDATE

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

INSERT INTO 表名(k, 列1, 列2) VALUES (值, 值1, default))

- Sandeep Narayan

---

如果在同一未存在的键上由两个线程同时进行，则其中一个线程将在插入时失败。我想知道是否有一种方法可以在不失败的情况下并发地执行此操作，其中每个连接都可以原子地选择和插入（如果不存在）。 - SNJ