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

Wouldn't a single atomic fetch and lock function (as in a stored procedure) guarantee that this wouldn't happen? — Jason Sperske, Mar 04 '15 at 21:56
Run you SQL statements in a transaction *and* lock the picked row for update, so that other queries won't advance until they get the lock if they end up picking the same row - be sure that your logic run as fast as possible not to degrade the performance though. — watery, Mar 04 '15 at 21:57
@JasonSperske I have to do a lot of business process before I set it to another table. I'm not sure if that is a thing in the stored procedure to fetch and lock, could you please eloborate? — Zeus, Mar 04 '15 at 22:02
I need to look that up in the docs and do some tests, that's why I didn't write an answer. Check your database documentation, anyway IIRC a `SELECT ... WHERE ... FOR UPDATE` should get a row exclusive lock on the picked row. Do that in a whole transaction so you can pick the row, lock it, write anything you need in other tables and then release that row (even if you didn't modify it) with a commit. Tha's roughly what I'm suggesting. — watery, Mar 04 '15 at 22:07
@Zeus my (perhaps naive) approach would be a stored procedure that inside of it includes a SELECT followed by an UPDATE. The Database would insure that a SELECT wouldn't fire without the corresponding UPDATE as part of it's [ACID](http://en.wikipedia.org/wiki/ACID) guarantee. — Jason Sperske, Mar 04 '15 at 22:13
@JasonSperske - in Oracle readers don't block writers. A transaction won't start until the update, so simultaneous calls to the procedure can select the same row. The `for update` syntax handles that though. — Alex Poole, Mar 04 '15 at 23:34

score 2 · Answer 1 · answered Mar 04 '15 at 22:05

2

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.

answered Mar 04 '15 at 22:05

Aninda Bhattacharyya

1,241
8
17

Sure will take a look. – Zeus Mar 04 '15 at 22:11

score 1 · Answer 2 · answered Mar 04 '15 at 22:02

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.

Could you please elaborate a little on the row locks? – Zeus Mar 04 '15 at 22:07 — Zeus, Mar 04 '15 at 22:07

score 1 · Accepted Answer · answered Mar 05 '15 at 15:22

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

Concurrency issue on a database select statement

3 Answers3