Does SELECT start transaction in PL/SQL

Question

I was told that following code won't help me to check duplicity, because result might be different before SELECT and UPDATE statement.

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;

This post claims that SELECT starts a transaction: Why do I get an open transaction when just selecting from a database View?

This part of documentation suggests otherwise:

A transaction implicitly begins with any operation that obtains a TX lock:

• When a statement that modifies data is issued

• When a SELECT ... FOR UPDATE statement is issued

• When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package

So? Does SELECT start a transaction or not?

Answer 1

The latter is true: https://docs.oracle.com/cloud/latest/db112/SQLRF/statements_10005.htm#SQLRF01705

A transaction implicitly begins with any operation that obtains a TX lock:

• When a statement that modifies data is issued
• When a SELECT ... FOR UPDATE statement is issued
• When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package

But it really does not matter, from the point of view of the main problem - to see if the record already exists in the database. Even if the transaction is explicitely startet using SET TRANSACTION ..., your code simply does not detect duplicate transactions !

Just do a simple test manually simulating the procedure in two simultaneous sessions and you will see:

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

Let say that in session #1 the procedure begins at 8:00:00.0000:

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.

Let say that in session #2 the same procedure begins at 8:00:00.0020, just after the insert was made in session 1, but still before the session#1 commits:

SQL> Set transaction name 'session 2';

Transaction set.

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

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

The transaction #2 does not see uncommited changes done by the session 1, so the session 2 assumess that there is no record John, so it also inserts it to the table:

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

1 row created.

---

Now the session 1 commits:

SQL> Commit;

Commit complete.

and a few milliseconds later the session2 commits too:

SQL> Commit;

Commit complete.

---

And the final result is - a duplicated record even though the transaction has been explicitelly started:

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

========== EDIT =================

You can avoid the duplicity by executing statement SET TRANSACTION ISOLATION LEVEL SERIALIZABLE in the beginning. – @Draex_

Many think that ISOLATION LEVEL SERIALIZABLE will solve the problem magically. Unfortunately, it will not help.

Let's see how it works on a simple example:

Session #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.

Session #2

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.

Session #1 again:

SQL> commit;

Commit complete.

SQL> select * from publishers;

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

and back to session #2

SQL> commit;

Commit complete.

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

---

As you can see, the magic of ISOLATION LEVEL SERIALIZABLE did not work.