1 Reply Latest reply: Nov 6, 2007 9:17 AM by JamesSutherland RSS

    how to lock a row and release lock


      There are two thread, they read the same row, I want to lock this row when the first thread read the record, after this thread commit its data, release the lock, the second thread can lock this record, also can commit its data. I also want to know how to release the lock. And these operations are in different Classes, e.g. in Class A first thread lock the record, in Class B second lock the record, and in Class C release the lock.

      Note: these two thread will read this record at same time.

      Thanks in advance.
        • 1. Re: how to lock a row and release lock
          TopLink provides to forms of locking, optimistic and pessimistic.
          You are referring to pessimistic. You can use pessimistic locking in TopLink through the ReadObject/ReadAllQuery API acquireLocks(), or acquireLocksWithoutWaiting(), this will use a "Select ... for Update" in SQL. You must be in a transaction to lock a row in the database (UnitOfWork in TopLink), and you locks are released when you commit or rollback the transaction. If you are using JPA, TopLink supports a JPA query hint "toplink.pessimistic-lock"="Lock" to allow pessimistic locking.
          You can also use optimistic locking. An optimistic lock does not prevent another user from reading the same row, but ensures that only one user will be able to commit changes to the row. Optimistic locking typically has the advantage of better concurrency, and does not have issues with deadlocks.

          <br>James Sutherland
          <br>Oracle TopLink, EclipseLink
          <br>Wiki: Java Persistence, EclipseLink