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?

The parameters for a procedure cannot be defined by VARCHAR2 with lengths like in your statement with VARCHAR2(40) or (30). `SELECT COUNT(*)>0 INTO` not allowed... — Barbaros Özhan, Mar 23 '18 at 17:50
If you read the full post to which you linked, instead of just the accepted answer, you will see that a `SELECT` statement does not start a transaction. — David Faber, Mar 23 '18 at 17:54
By the way, are you asking whether a `SELECT` starts a transaction or are you asking for help with your stored proc? It's not clear to me. — David Faber, Mar 23 '18 at 17:55
I would suggest using explicit transaction begin transaction and commit transaction https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT117 — srp, Mar 23 '18 at 17:58
@DavidFaber I've read the full post, but it's not clear to me that SELECT does not start a transaction. Can you please show me where does the documentation say that? And, what am I missing when I read the quotes in the other answer. — Evka'S, Mar 23 '18 at 18:09
@srp - that won't stop the table being updated by someone else between the select and insert? — Alex Poole, Mar 23 '18 at 18:09
@Evka'S - there is some debate in that post, but much is about the docs not being clear. A select only seems to start a transaction if a DB link is involved, which doesn't seem to be the case for you. But you having a transaction open before the select wouldn't stop someone else modifying the table anyway. Why aren't you just using a unique constraint? — Alex Poole, Mar 23 '18 at 18:12
@alex i am not sure in oracle but in sqlserver there is a provision to have isolation level serializable in transaction statement. which will lock for insertion and updation and select, Have a look at this article http://www.oracle.com/technetwork/testcontent/o65asktom-082389.html — srp, Mar 23 '18 at 18:24
A similar question https://stackoverflow.com/questions/31203189/why-does-not-select-query-start-the-transaction-oracle?rq=1 — srp, Mar 23 '18 at 18:35
In any case I've definitely seen tables get updated in between `SELECT` and `INSERT` regardless of what the documentation may or may not say. Writing a stored proc this way definitely is not good practice. — David Faber, Mar 23 '18 at 18:39
This is why Nature gave us UNIQUE constraints, so that we could enforce non-duplication of values in multi-user environments. — APC, Mar 24 '18 at 10:04

krokodilko · Answer 1 · 2018-04-25T18:53:00.053

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.

You can avoid the duplicity by executing statement `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` in the beginning. — Draex_, Apr 25 '18 at 18:20
@Draex_ I've updated my question with an example how this works with `ISOLATION LEVEL SERIALIZABLE`, please take a look at it. — krokodilko, Apr 25 '18 at 18:54
Aha, thanks. Do you know why is that so? I thought this was the issue of _Phantom read_, as in [On Transaction Isolation Levels](http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html) which is not supposed to happen in SERIALIZABLE mode. — Draex_, Apr 25 '18 at 19:38
Thanks. Do you know how I could achieve what I was trying to do? — Evka'S, Apr 25 '18 at 19:40
@Draex_ `Do you know why is that so?` - because the session doesn't see uncommited changes from other sessions. Isolation level Serializable does not magically change this behaviour in any way. it only **serializes locks**. — krokodilko, Apr 26 '18 at 04:03

Does SELECT start transaction in PL/SQL

1 Answers1