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

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.