Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Handling concurrent updates using JDBC

 
Shital Mahajan
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Consider a scenario where two users are trying to update same data in the database.They first select the data from the database table and then update some of the records in the same table after checking the values.
Suppose user A has selected record 1, 2 and 3. Then he updates record 2 and 3.
Before user A updates the records, user B selects records 1, 2 & 3 and updates records 1 and 2 after some criteria is met. So now the updates done by user A will be overwritten by user B.
How do i avoid this situation? Is there any way to control the simulataneous updates without degrading the performance?

Thanks
Shital
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are various ways to handle it, the I like is most are
  • Add a column in table let say last updated time (Datatype Timestamp), always insert/update it with current timestamp. And whenever you are trying to update a row use this timestamp in you where clause.Therefore once one user update the row , other user's query will not satisfy where clause and will not have any effect on database.
  • Another similar approach is that use a running counter instead of timestamp.



  • hope this helps.

    Thanks,
    Shailesh Chandra
    [ April 24, 2007: Message edited by: Shailesh Chandra ]
     
    Gaurav Chikara
    Ranch Hand
    Posts: 412
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Another similar approach is that use a running counter instead of timestamp.


    I am not sure how counter will help in this scenario
    Best approach is to define row level locks on Table if your database supports it
    Or define isolation level of transaction
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Originally posted by Gaurav Chikara:


    I am not sure how counter will help in this scenario
    Best approach is to define row level locks on Table if your database supports it
    Or define isolation level of transaction



    Any lock on database may have concequences specially in big application.Still
    I would with column of last update,

    A update counter will work in same way as timestamp. Only difference is that it will have counter of update operation rather timestamp.

    Both approach has their own pros and cons

    Thanks,
    Shailesh
     
    Gaurav Chikara
    Ranch Hand
    Posts: 412
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Any lock on database may have concequences specially in big application


    Still defining isolation levels on transactions or locks on rows are better than doing two hits on database two maintain data consistency.
    If we have 1000 rows then we will be hitting table 2000 times to maintain data integrity and I believe we won't want our application to be slow

    Besides this ROW-LEVEL locks are only for ROW so they are not escalated to table level to impact performance
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    you might be correct at your level, but my personal experience with row level lock is too bad in my previous project. Where we were having 15 cluster server and 40,000 concurrent users.

    ALSO we were having clustered database. I guess a lock also has to be replicated in this case.

    So It totally depend on actual environment.

    Thanks,
    Shailesh
    [ April 25, 2007: Message edited by: Shailesh Chandra ]
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic