CODEWITHSUNDEEP
javasqlsql-serverjdbc
Conner
Conner

Reputation: 675

Basic Locking Based on Select result

I have a simple program that is modifying a database, which also may be run concurrently by different users.

In the program I have a few sequential operations reading/updating/inserting to the database that are dependent on each-other.

For example:

String selectQuery = "select order.[total] from order where id=?";
selectQuery.setString(1, "15679");

ResultSet queryResults = selectQuery.executeQuery();

if(queryResults.next() == false){

    //execute insert into the order table
    String insertQuery = "insert into order (id, total) values (?,?)";
    .......

}

Now in the above example, there is no guarantee that two programs won't try to insert the order at the same time since they both may execute the select, get an empty result set, and then try to insert.

I understand in this example it is a little unrealistic since we could enforce uniqueness on the id column which would stop duplicates from being inserted.

What I am trying to do is ensure that we can avoid this situation without locking the entire table? So we could guarantee that before the select query is executed that we can know that there will be no inserts/updates/reads on the row (which may or may not exist). After that we could release the lock on that row.

Upvotes: 0

Views: 1011

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89489

The right way to do this in SQL Server is to use a transaction, and add the (updlock,holdlock) hints to the select. updlock forces a SELECT query to use a restrictive Update (U) lock, instead of using versioned snapshots and no locks, or a permissive Shared (S) lock. And updlock enables range locking like in the SERIALIZABLE isolation level, so that the key ranges targeted by the query are locked, even if there are currently no rows in the range.

This will put a U range lock on the id, preventing another session from putting a U or X lock on the key value, and a range lock will lock empty key ranges, so it works whether or not any row with that id exists. EG:

con.setAutoCommit(false);
String selectQuery = "select order.[total] from order with (updlock,holdlock) where id=?";
selectQuery.setString(1, "15679");

ResultSet queryResults = selectQuery.executeQuery();

if(queryResults.next() == false){

    //execute insert into the order table
    String insertQuery = "insert into order (id, total) values (?,?)";
    .......

}
con.commit();

An inferior way to acomplish the same thing is to force the transaction into the SERIALIZABLE isolation level. But this won't force U locks on the initial read, so when two sessions both run the first query, one will succeed with the INSERT and the other will fail with a deadlock.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Well, you seem to understand race conditions and why you don't want to do checks in the application.

So, make sure the id is unique in orders:

alter table orders add constraint unq_orders_id unique (id);

You can attempt as many concurrent inserts as you like. All but one will fail because the database is ensuring relational integrity.

Upvotes: 1

Related Questions