是否在PL/SQL中选择启动事务

Question

我被告知下面的代码不能帮助我检查重复，因为在SELECT和UPDATE语句之前，结果可能不同。

PROCEDURE AddNew(Pname VARCHAR2, Pcountry VARCHAR2)
AS
    already_exists BOOLEAN;
BEGIN
    SELECT COUNT(*)>0 INTO already_exists FROM Publishers WHERE name=Pname;
    IF already_exists THEN
        RAISE_APPLICATION_ERROR(-20014,'Publisher already exists!');
    END IF;
    INSERT INTO Publishers(id,name,country)
        VALUES (NewPublisherId(),Pname,Pcountry);
END;

这篇文章声称SELECT启动了一个事务：为什么我在从数据库视图中选择时得到了一个打开的事务？

所以呢？选择是否启动事务？

Answer 1

后者是真的:https://docs.oracle.com/cloud/latest/db112/sqlrf/statements_10005.htm#sqlrf01705

事务隐式地以任何获得TX锁的操作开始：

• 发出修改数据的语句时
• 发出SELECT...FOR UPDATE语句时
• 用SET transaction语句或DBMS_TRANSACTION包显式启动事务时

CREATE TABLE Publishers(
    id int,
    name varchar2(100)
);

SQL> Set transaction name 'session 1';

Transaction set.

SQL> select count(*) FROM Publishers where name = 'John';

  COUNT(*)
----------
         0

SQL> INSERT INTO Publishers(id,name) VALUES(1,'John');

1 row created.

SQL> Set transaction name 'session 2';

Transaction set.

SQL> select count(*) FROM Publishers where name = 'John';

  COUNT(*)
----------
         0

SQL>  INSERT INTO Publishers(id,name) VALUES(1,'John');

1 row created.

SQL> Commit;

Commit complete.

SQL> Commit;

Commit complete.

select * from publishers;
        ID NAME                                                                                                
---------- ----------------------------------------------------------------------------------------------------
         1 John                                                                                                
         1 John         

===========编辑==================

可以通过在开始时执行语句集事务隔离级别SERIALIZABLE来避免重复。-@draex_

许多人认为隔离级别可序列化将神奇地解决这个问题。不幸的是，它没有帮助。让我们看看它在一个简单的示例中是如何工作的:会话#1

SQL>  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction set.

SQL> select count(*) FROM Publishers where name = 'John';

  COUNT(*)
----------
         0

SQL> INSERT INTO Publishers(id,name) VALUES(1,'John');

1 row created.

SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction set.

SQL>  select count(*) FROM Publishers where name = 'John';

  COUNT(*)
----------
         0

SQL> INSERT INTO Publishers(id,name) VALUES(1,'John');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from publishers;

        ID  NAME
----------  --------
         1  John

SQL> commit;

Commit complete.

SQL> select * from publishers;
            ID  NAME
    ----------  --------
             1  John
             1  John

正如您所看到的，隔离级别可序列化的魔力没有发挥作用。