Concurrency issue on a database select statement

Question

I have an issue and I'm thinking of using database isolationtype == Serializable for this situation, but after reading a bunch of articles I'm still not convinced that that is the solution for my problem below.

Setup:

Weblogic cluster > 2 servers
Simple Java JDBC
Servlets, EJB Session beans 2.0

I have a table LAN and we pick matching values based on the input given by the client.

LAN

lan_id   | name | some_values | is_available
-------------------------------------
13       |  ss  | 3234        | yes 
12       |  sssd| 3234        | yes
14       |  sssd| 3234        | yes
15       |  ssaa| 3234        | yes

Now in the business logic I need to pick a matching row out of the LAN and save another table LAN_Assignment

LAN_Assignment

lan_id   | lan_assg_id | some other columns
-------------------------------------------

When running a select statement i get a matching row from a LAN table and assign it to the lan_assignment table.

Now If there are 5 requests coming from the client(could be any server in the cluster), they all pick the first available LAN and save it to the other table.

How do I make sure that the first request which picked up the LAN is not selected by the second request from the client?

PS: the select statements and the business logic is not so straight forward like explained here. There are a lot of conditions to choose LAN and save it to Lan_assignment etc.,

thank you

Answer 1

Oracle 10g has undocumented SKIP LOCKED available for updating, I'm using it as a solution(see option 3 below).

How ever I have gone through other options for handling this scenario.

Option 1: The below option will just lock the rows until the transaction is complete. All the other transactions will keep waiting for the lock to be released by the first transaction. This is a little risky as the transactions might stay waiting for a very long time and may result in deadlock.

select .. where .. for update

Option 2:(Nowait) This will not wait if the rows are locked by some other transaction. It will return oracle error. I may catch the exception wait 10 seconds and try for another 4-5 attempts before showing error to the user.

select .. where... for update nowait

Option 3: (skip locked) this will skip the rows which are locked by other transactions, which serves the purpose for me as I don't want to use those which are locked by other transactions.

select...where ... for update skip locked

Answer 2

The Serializable isolation is not the solution to your problem (but please leave it there!)

You have a few alternatives to process those 5 concurrent requests (as per your scenario). One is to fail 4 of those transactions and only 1 will succeed. You can do this using unique constraints or using optimistic locking, and retry operations that fail due to this (but remember to fail after a few retries).

Alternatively, you can use row locks, if the volume is not huge, this approach should work fine.

Answer 3

You can use SKIP LOCKED for your purpose.With this when Session 1 locks the row, Session 2 can skip it and process the next. I believe it was there in 10g also, but never documented.