can we select and update to database together?

Question

I have logic which selects some rows from the database that have a status of “Ready” once I get the result set i need to update the status to 'processing' so no other thread can access these rows. then i have to perform some logic on the records in the result set and one the processing is complete i need to again update the record status to completed.

Questions: is there a way in which when i execute the select statement - at that time only i can simultaneously change the status of the rows returned in result set.

can someone suggest if there a good way of implementing it in java.

Answer 1

You can SELECT ... FOR UPDATE NOWAIT in a transaction the rows you want to process before updating them. SELECT ... FOR SHARE NOWAIT will fail with error if someone is updating selected rows or someone already done SELECT ... FOR SHARE NOWAIT on them.

Details here.

Answer 2

Answering your main question: Yes, you can do that!

1. As others have mentioned, SELECT … FOR UPDATE is one of your options.

2. Another one is to increase serialization level. Please, have a look at this related answer and follow the links there.

   It is worth mentioning, that going to up to REPEATABLE READ or even SERIALIZABLE transactions will require changes on the Application side, as these isolation levels assume that some amount of transactions will be rolled back with serialization errors, which is a normal and expected behavior.

   Also, more strict isolation level (as well as more SELECT … FOR UPDATE queries) will cause performance drop and, possibly, may lead to the race conditions, depending on the design.

3. Please, have look at this article on depesz.com, it does a good overview of possible drawbacks in the related area.