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
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