Database Locks in a web application


Performance and scalability: Database Locks in a web application

  1. Database Locks in a web application (2 messages)


    I have a web application (no ejbs) that is used by admins and web users. my question is, if a web user wants to update his info in the mean time an admin is updating the same users info there will be a lock in the database. I am not sure how to prevent this from happening from within my application.
  2. there could be a couple of ways add an application level varible that is enable when i user is updating their info and then check that before you go to update the info , add an extra field into the database that is checked etc etc
  3. Depending on what kind of transactions you do and your DB server, as well as transaction isolation levels, a deadlock or a concurrency conflict is possible. For example:

    The user SELECTs the current info in transaction T1
    The administrator UPDATEs the current info in transaction T2
    Transaction T2 commits
    User tries to UPDATE in transaction T1...but meanwhile the administrator has changed the data. This is a concurrency conflict.

    Different DB servers handle this situation differently. The exact behavior also depends on your transaction isolation level settings. Check the documentation for your database to check what exactly would happen.

    To prevent the conflicts from happening, you can use 2 methods:

    1) Locking the data
    2) Trying the transaction again

    Option 1) often suffers from the risk of deadlock. You should analyze the situation and see if such a risk exists. If you can live without transactions, though, everything becomes much easier.

    Pietari Laurila