Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to preserve the consistency in a concurrent database access.

 
Giuseppe Tino
Greenhorn
Posts: 8
Firefox Browser Java Netbeans IDE
  • 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.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • 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
 
Giuseppe Tino
Greenhorn
Posts: 8
Firefox Browser Java Netbeans IDE
  • 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: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • 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.
 
Giuseppe Tino
Greenhorn
Posts: 8
Firefox Browser Java Netbeans IDE
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic