How to make DB return different rows for subsequent transactions?

Question

I have the below flow in a multi-threaded environment

1. start transaction
2. read n number of top rows (based on a column) from db
3. check some criteria
4. update those set of rows
5. commit/rollback the transaction

I am using optimistic locking to handle multi-threaded scenario, but in above situation DB is always returning the same set of rows so if a second thread runs at the same time it will always fail.

Is there a better way to handle this? Could we force DB to return different set of rows for each transaction using some option?

Answer 1

The reason you are getting the same top n records for all your threads is because of the I in the ACID (atomicity, consistency, isolation, durability) principles of transactions. Isolation means other operations cannot access data that has been modified during a transaction that has not yet completed. So until your threads commit their transactions the other threads cannot see what they have done.

It is possible to change the Isolation level on most databases to one of the following:

• SERIALIZABLE
• REPEATABLE READ
• READ COMMITTED
• READ UNCOMMITTED

In your case you probably want READ UNCOMMITTED as it allows one transaction to see uncommitted changes made by some other transaction.

Note: This is almost certainly the wrong isolation level for most applications, and could lead to data corruption. If other application other than the one you described here are accessing the same database you probably don't want to change the isolation level as those application may start to see unexpected and incorrect behaviour.