• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

How to preserve the consistency in a concurrent database access.

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I'm creating an application composed by many clients that have a concurrent access to a single database (PostgreSQL).
I have a doubt about the manner to prevent that the concurrent access could cause problems with the database consistency, for example if a client A read some data and after some time updates it but in the time between the read and the update another client, B, makes an update on the same data.

I thought that to avoid this issues I could use for write operations (like an update) a transaction that read again the data that should be updated, compares it with the previous data read and if it is not changed proceed with the update, but I don't know if this is the best manner to obtain it or if there is a better strategy that is used in those cases.
Could you give me some suggestion?

Thanks in advance.
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Giuseppe Tino wrote:I thought that to avoid this issues I could use for write operations (like an update) a transaction that read again the data that should be updated, compares it with the previous data read and if it is not changed proceed with the update, but I don't know if this is the best manner to obtain it or if there is a better strategy that is used in those cases.
Could you give me some suggestion?


In JPA you have something like optimistic locking using a version number or timestamp. It's very similar to your thoughts: on each update the version number (or timestamp) is updated. If the version number (or timestamp) doesn't match anymore, the record was changed in the meantime by someone else
 
Goshfil Thomausen
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Giuseppe Tino wrote:I thought that to avoid this issues I could use for write operations (like an update) a transaction that read again the data that should be updated, compares it with the previous data read and if it is not changed proceed with the update, but I don't know if this is the best manner to obtain it or if there is a better strategy that is used in those cases.
Could you give me some suggestion?


In JPA you have something like optimistic locking using a version number or timestamp. It's very similar to your thoughts: on each update the version number (or timestamp) is updated. If the version number (or timestamp) doesn't match anymore, the record was changed in the meantime by someone else



Wow,this is a very fast answer! Thanks a lot!
And there is not any mechanism provided directly by Postgres to help with this "versioning"? Because I develop in Java but I'm not planning to use JPA.
(In any case thanks for the link it contains a lot of info, I can take the cue from it to implement my own mechanism. )
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Giuseppe Tino wrote:And there is not any mechanism provided directly by Postgres to help with this "versioning"? Because I develop in Java but I'm not planning to use JPA.


I am not a Postgres expert (in fact, I have never used it). But databases often support a pessimistic locking mechanism (e.g. FOR UPDATE clause). This article seems to provide a nice comparison of different alternatives to prevent lost updates.
 
Goshfil Thomausen
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Giuseppe Tino wrote:And there is not any mechanism provided directly by Postgres to help with this "versioning"? Because I develop in Java but I'm not planning to use JPA.


I am not a Postgres expert (in fact, I have never used it). But databases often support a pessimistic locking mechanism (e.g. FOR UPDATE clause). This article seems to provide a nice comparison of different alternatives to prevent lost updates.



Perfect I will study both pessimistic and optimistic locking so and will see what best fit with my needs.
Thanks a lot for your help.
 
Die Fledermaus does not fear such a tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic