Geek Answers Handbook

Can we select and update the database together?

I have a logic that selects some rows from the database that have the status "Ready", as soon as I get a result set, I need to update the status to "processing" so that no other thread can access these rows. then I need to execute some logic in the records in the result set, and one processing is complete. I need to update the status of the record again to complete.

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

can anyone suggest if there is a good way to implement it in java.

+4
source share
3 answers

You can SELECT ... FOR UPDATE NOWAIT in the transaction of the row you want to process before updating it. SELECT ... FOR SHARE NOWAIT will fail if someone updates the selected rows or someone already made SELECT ... FOR SHARE NOWAIT on them.

Details here .

+2
source

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

  • As already mentioned, SELECT … FOR UPDATE is one of your options.

  • Another is to increase the level of serialization. Please see this related answer and follow the links there.

    It should be noted that switching to REPEATABLE READ or even SERIALIZABLE transactions will require changes on the part of the application, as these isolation levels suggest that a certain number of transactions will be canceled with serialization errors, which is normal and expected behavior.

    In addition, a more stringent isolation level (as well as more SELECT … FOR UPDATE queries) will lead to reduced performance and, possibly, can lead to race conditions, depending on the design.

  • Please see this article on depesz.com for a good overview of possible flaws in this area.

+2
source

You are probably looking for “locks” that allow you to lock a table / row (for yourself). A great example can be found on the PostgreSQL website:

http://www.postgresql.org/docs/current/static/sql-lock.html

Using locks ensures that only you have access to the table / row, and others will not be executed.

+1
source

More articles:


All Articles