Trying to understand oracle TRANSACTION_SERIALIZABLE level

Question

I'm trying to write method on SQL DB which work like ConcurrentMap.putIfAbsent where 'value' serves as key and 'id' as value. The main constraint of this method is keeping the value unique throughout the table.

Below is my example of this method. Invocation of sync.yield() passes control to other thread. It was added to achieve necessary parallel thread execution.

import java.sql.*;
import java.util.concurrent.atomic.AtomicInteger;


public class Main {

private static final String USER = "";
private static final String PASS = USER;
private static final String URL = "jdbc:oracle:thin:@192.168.100.160:1521:main";

private static final AtomicInteger id = new AtomicInteger();
private static final Sync sync = new Sync(1);

static Connection getConnection() throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection c = DriverManager.getConnection(URL, USER, PASS);
    c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    return c;
}

static long putIfAbsent(String value) throws Exception {
    Connection c = getConnection();

    PreparedStatement checkSt = c.prepareStatement("SELECT id from test WHERE value = ?");
    checkSt.setString(1, value);
    ResultSet rs = checkSt.executeQuery();

    if (rs.next())
        return rs.getLong(1);

    System.out.println(Thread.currentThread() + " did not find value");

    sync.yield();

    long id = getId();
    System.out.println(Thread.currentThread() + " prepare to insert value with id " + id);
    PreparedStatement updateSt = c.prepareStatement("INSERT INTO test VALUES (?, ?)");
    updateSt.setLong(1, id);
    updateSt.setString(2, value);

    updateSt.executeQuery();
    c.commit();
    c.close();

    return id;
}

public static void main(String[] args) {

    Runnable r = () -> {
        try {
            System.out.println(Thread.currentThread() + " commit success and return id = " + putIfAbsent("val"));
            sync.yield();
        } catch (Exception e) {
            e.printStackTrace();
        }
    };

    Thread t1 = new Thread(r);
    Thread t2 = new Thread(r);

    t1.start();
    t2.start();
}

static long getId() {
    return id.incrementAndGet();
}

}

When I run main method on empty table I get this console output:

Thread[Thread-0,5,main] did not find value
Thread[Thread-1,5,main] did not find value
Thread[Thread-0,5,main] prepare to insert value with id 1
Thread[Thread-0,5,main] commit success and return id = 1
Thread[Thread-1,5,main] prepare to insert value with id 2
Thread[Thread-1,5,main] commit success and return id = 2

I can explain first five lines. But cannot sixth. When thread-1 perform update it rely that SELECT id from test WHERE value = ? have empty result. This result is not consistent with current DB state. So, I expect ORA-08177: Cannot serialize access for this transaction.

I am using this impementations of Sync class(It keep link reference on thread's object):

import java.util.ArrayDeque;
import java.util.HashSet;
import java.util.Queue;
import java.util.Set;

public class Sync {
    private final Object lock = new Object();
    private final Queue<Thread> sleepingTh = new ArrayDeque<>();
    private final Set<Thread> activeTh = new HashSet<>();

    private final int threads;

    public Sync(int threads) {
        this.threads = threads;
    }

    public void yield() {
        final Thread ct = Thread.currentThread();

        synchronized (lock) {
            sleepingTh.add(ct);
            activeTh.remove(ct);

            if (sleepingTh.size() > threads) {
                Thread t = sleepingTh.poll();
                activeTh.add(t);
                lock.notifyAll();
            }

            while (!activeTh.contains(ct)) {
                try {
                    lock.wait();
                } catch (InterruptedException e) {
                }
            }
        }
    }

    public void wakeUpAll() {
        synchronized (lock) {
            activeTh.addAll(sleepingTh);
            sleepingTh.clear();
            lock.notifyAll();
        }
    }
}

Statement for creating table:

create table test(
id number(16),
value varchar(50)
);

I use jdk1.8.0_60, Oracle JDBC 10.2.0.4.0, and Oracle DB 11g2

Answer 1

The documentation ==> click says that:

Serializable Isolation Level
..............
..............
Oracle Database permits a serializable transaction to modify a row only if changes to the row made by other transactions were already committed when the serializable transaction began. The database generates an error when a serializable transaction tries to update or delete data changed by a different transaction that committed after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction

Your code executes only INSERT statements.
It doesn't try to update or delete data changed by a different transaction,
so ORA-08177 doesn't occur.

---

---- EDIT --------------

---

do you can give advice, how I can rewrite method?

Just create an unique contaraint on value column.
In the code just do straight INSERT statement.
If it succeeds - this means that the row has not existed yet
If if fails (duplicate key exception) - this means that the row already exists, and in this case simple ignore the error.

does SQL-92 allow this behavior?

Yes, of course.
SQL-92 define only three read phenomena, see this link for details: Isolation (database systems)

• dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.
• A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.
• A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

In the serializable isolation levels none of the above phenomena may occur. Thats all. The transaction cannot see any changes from other transactions.
And in you code this is true by all means. The session cannot see a row inserted by the other session, because neither the phantom phenomena nor the dirty read phenomena cannot occur in this isolation level.