aspose file tools*
The moose likes JDBC and the fly likes Handling concurrent updates using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Handling concurrent updates using JDBC" Watch "Handling concurrent updates using JDBC" New topic
Author

Handling concurrent updates using JDBC

Shital Mahajan
Greenhorn

Joined: Apr 23, 2007
Posts: 2
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

Joined: Aug 13, 2004
Posts: 1081

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 ]

    Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
    Gaurav Chikara
    Ranch Hand

    Joined: Jun 09, 2000
    Posts: 410
    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


    SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    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

    Joined: Jun 09, 2000
    Posts: 410
    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

    Joined: Aug 13, 2004
    Posts: 1081

    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 ]
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: Handling concurrent updates using JDBC