5 Replies Latest reply: May 30, 2013 10:30 AM by gimbal2 RSS

    how and when the database release a row lock?


      Dear experts,

      We are using the following statement to obtain a row lock in a table in the database(ORACLE of course),
      SELECT * FROM {TABLE_NAME} WHERE ID = 1 for update
      and if we succeed grabbing the row lock we will continue to issue a update statement every 30 seconds to preserve the lock as far as possible.
      here is the update statement to preserve the lock,
      UPDATE {TABLE_NAME} SET time = ? WHERE ID = 1.
      As you see more longer we keep holding the row lock , more update statements are submitted in the pending transaction.
      In normal case our application can grab the exclusive row lock and works for a long time,however sometimes a connection reset exception is thrown
      and our application will close the connection(I assume the pending transaction will be rolled back by the database) and exit the JVM.
      Since other applications will keep trying to grab the same row lock to become the master role,
      we expect one of them can succeed but they are all failed because the database has not released the row lock as expected.
      Can someone explain more details about how and when the row lock can get released in our use case?


      Edited by: SuoNayi on 2013-5-30 上午8:12

        • 1. Re: how and when the database release a row lock?
          Hm. Is this part of an XA transaction by any chance? I know that Oracle maintains separate bookkeeping for such transactions which can cause rows to stay 'locked' even when the regular DBA views will indicate there is no user currently locking the record at all. I have no idea about the actual details and what you need to do to clean up such a situation, that is something a DBA should know and do.

          If not... well this is more of a question for people who know the DBMS, which makes it a target for the Oracle DBMS forums. Java developers don't tend to have DBA-level knowledge of the database, you should ask the question where you have more chance of people with expertise answering stuff:

          General Database Discussions
          • 2. Re: how and when the database release a row lock?
            Thanks for your comment.
            It's a regular JDBC transaction and I will try my luck in the Oracle DBMS forum.
            • 3. Re: how and when the database release a row lock?
              Joe Weinstein-Oracle
              You are making a fundamental mistake, based on your not really understanding
              how Oracle, and/or other DBMSes may differ wildly in how/whether they lock
              data rows. So far as what you've said, your select query will lock nothing, nada,
              zero. Nothing about that read will stop some other used from updating or deleting
              that row. Depending on your connection state, neither will even the update.
              At commit time, as long as the DBMS can show a logical way that the final outcome
              could have happened if the competing transactions had been serialized (either one
              went first, then the other) then the DBMS will allow both to commit.
              For the complicated-but-necessary truth about locking, you need to read the
              DBMS-specific documentation on locking and transaction isolation. For Oracle,
              here's a hint: your read can lock a row if it uses the 'FOR UPDATE' syntax.
              • 4. Re: how and when the database release a row lock?
                I'm very sorry I did miss the "for update" words.
                Can you review the statement?
                In fact we use the row lock of the database to be the distributed lock service.
                In order to avoid the single point failure, our application is multiple-instances and only one instance can succeed in grabbing the row lock to do business operations.
                It will hold the row lock as long as possible, once it's failed to extend the lock the application will close the connection and exit the JVM at last.
                Since all other instances will keep trying to grab the same lock, one of them can also succeed in grabbing the row lock if the previous instance lose the lock.
                It works well for a long time while sometimes we see that the lock is not released and all the rest instances are all waiting for grab the lock and none can succeed.
                Sometimes it can succeed after 10+ minutes or so.

                Hope I clarify our use case this time.

                • 5. Re: how and when the database release a row lock?
                  new thread was created: how and when the database release a row lock?

                  (better post links to related threads so people have all the information and don't treat your double posts as forum abuse).