SQL Isolation level

Question

We are using session isolation serializable in our application. The intended behavior is that when a user is going insert a new row, it should-should check for the presence of the row with the same key and update the same if row found. But I have found multiple rows created for the same key in SQL server. Is this issue with isolation or the way we are handling the case?

Following is the code I am using,

private int getNextNumber(String objectName, Connection sqlConnection) throws SQLException {
    // TODO Auto-generated method stub
    int number = 0;

    try{

        sqlConnection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        System.out.println("##### Transaction isolation set : " + sqlConnection.getTransactionIsolation());

        Statement stmt = sqlConnection.createStatement();  
        ResultSet rs   = stmt.executeQuery("select * from [dbo].[db] where DocumentNumber = '" + objectName.toString() + "' FOR UPDATE");  

        while(rs.next()) {
            printNumber = rs.getInt("PrintNumber");
        }


        System.out.println("#### Print number found from sql is : " + printNumber);

        if(printNumber == 0) {
            printNumber = printNumber + 1;
            stmt.execute("INSERT INTO [dbo].[db] (number, DocumentNumber) VALUES (1 ,'" + objectName.toString() + "')");
        } else {
            number = number + 1;
            stmt.execute("UPDATE [dbo].[db] SET Number =" + number + " WHERE DocumentNumber ='" + objectName.toString() + "'");
        }

        //sqlConnection.commit();
    }catch(Exception e) {
        sqlConnection.rollback();
        e.printStackTrace();
    } finally {
        sqlConnection.commit();
    }
    return number;
}

Thanks, Kishor Koli

Can you post the code handling the "case"? Are you using a merge statement? Are you using old fashion EXISTS check with properly wrapped explicit transaction around the whole block? Need more info. — Phil Schwartz, May 12 '17 at 05:17

score 1 · Answer 1 · answered May 12 '17 at 11:41

It's an issue with the way your database is set up. You need a unique constraint to enforce uniqueness. You can check at insert time all you like but a unique constraint is the only way it's going to work 100% so it's just a waste of time selecting before inserting in the hope you'll prevent a duplicate. Insert, catch the exception/error or proceed.

SQL Isolation level

1 Answers1