14 Replies Latest reply: May 31, 2013 1:24 PM by jgarry RSS

    how and when the database release a row lock?

    914264
      Dear experts,

      We are using the following statement to obtain a row lock in a table in the database(ORACLE 10G),
      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?

      Thanks,
      SuoNayi

      Edited by: SuoNayi on 2013-5-30 上午7:36
        • 1. Re: how and when the database release a row lock?
          sb92075
          SuoNayi wrote:
          Dear experts,

          We are using the following statement to obtain a row lock in a table in the database(ORACLE 10G),
          SELECT * FROM {TABLE_NAME} WHERE ID = 1
          no row lock occurs from posted SQL above.
          • 2. Re: how and when the database release a row lock?
            914264
            sorry, can you review the statement again? I missed the "for update" words.

            Edited by: SuoNayi on 2013-5-30 上午7:38
            • 3. Re: how and when the database release a row lock?
              sb92075
              SuoNayi wrote:
              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.
              Assumption is incorrect.
              The DB is unaware that the application connection has gone missing & just waits patiently for either COMMIT or ROLLBACK.
              • 4. Re: how and when the database release a row lock?
                914264
                When the connection reset exception is thrown we just close the connection(maybe we just swallow exceptions when closing jdbc connections).
                Can you tell me why the lock is not released as expected or there's other approaches to extend the row lock rather than executing the update statement again and again.

                Thanks,
                SuoNayi
                • 5. Re: how and when the database release a row lock?
                  sb92075
                  SuoNayi wrote:
                  When the connection reset exception is thrown we just close the connection(maybe we just swallow exceptions when closing jdbc connections).
                  Can you tell me why the lock is not released as expected or there's other approaches to extend the row lock rather than executing the update statement again and again.
                  Since you mention JDBC, I strongly suspect that application is 3-Tier like below


                  EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

                  If application is 3-Tier, how is the DB to know that the client won't be issuing more SQL for that user session?
                  • 6. Re: how and when the database release a row lock?
                    914264
                    In fact we use the row lock of the database to be the distributed lock service.
                    Our application is background application and not a web application.
                    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.
                    That instance is called master and it will hold the row lock as long as possible, once it's failed to extend the lock it 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 almost time while sometime even if the master is gone but the rest instances can not succeed in grabbing the lock.

                    Hope I clarify our use case this time.

                    Thanks,
                    SuoNayi
                    • 7. Re: how and when the database release a row lock?
                      rp0428
                      >
                      In fact we use the row lock of the database to be the distributed lock service.
                      Our application is background application and not a web application.
                      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.
                      That instance is called master and it will hold the row lock as long as possible, once it's failed to extend the lock it 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 almost time while sometime even if the master is gone but the rest instances can not succeed in grabbing the lock.
                      >
                      Well that sounds like a terrible way to do whatever it is you are trying to do.

                      Why don't you start over by telling us what PROBLEM all of this is supposed to be solving and why you think this extended locking is the way to solve it?

                      What do you mean by 'distributed lock service'?

                      What 'business operations' are you talking about?

                      What is the point of having 'multiple-instances' if only one of them can do 'business operations' over an extended period of time?

                      This sounds like the wrong solution for whatever problem you have.
                      • 8. Re: how and when the database release a row lock?
                        914264
                        Ok, for simplicity I will do the following steps:
                        1. get a jdbc connection from the datasource and hold it for future usage.
                        2. issue the "select .. for update" statement to grab the single row lock.
                        3. if succeed in grabbing the lock I will issue the update statement every 30 seconds to extend/preserve the lock because I need a long and exclusive lock.
                        Note that I use the same connection to execute the update statement.
                        4. if any exceptions is thrown I will close that connection and stop application and exit the JVM.
                        5. in the same time other applications(one application is deployed on the multiple machines) keep trying to grabbing the same row lock as well,
                        if one of them lose/release the lock other one of them should succeed grabbing the lock.

                        It works well for days and I can observe the row lock can change owner when the application who holds the lock is restarted/stopped.
                        While sometime if a exception is thrown when extending/preserving the lock and my application do close the connection and exit the JVM,
                        other application can not hold the lock since the database does release the row lock in time.

                        That's my problem and thanks for your attention.
                        • 9. Re: how and when the database release a row lock?
                          rp0428
                          Why don't you start over by telling us what PROBLEM all of this is supposed to be solving and why you think this extended locking is the way to solve it?
                          >
                          3. if succeed in grabbing the lock I will issue the update statement every 30 seconds to extend/preserve the lock because I need a long and exclusive lock.
                          >
                          If you 'succeed in grabbing the lock' then the row is locked until your session issues a COMMIT or ROLLBACK. There is need or point at to 'issue the update statement every 30 seconds to extend/preserve the lock'.

                          This is Oracle we are talking about, not SQL Server or some other database where that locking strategy may be necessary.

                          You haven't told us why you are using this approach, a method that I've never seen used in over 25 years.

                          If you are trying to serialize a procedure or process you can use a STATUS_TABLE and set a value that represents a 'lock'. Other processes can check that table to see if the lock is set.
                          • 10. Re: how and when the database release a row lock?
                            914264
                            This approach is derived from some open source software and I want to use it for my distributed lock mechanism.
                            Since the database is a infrastructure and you can use it very handy.
                            If I abuse the row lock I will consider other approaches, such as Apache ZooKeeper or something.
                            • 11. Re: how and when the database release a row lock?
                              rp0428
                              >
                              This approach is derived from some open source software and I want to use it for my distributed lock mechanism.
                              Since the database is a infrastructure and you can use it very handy.
                              If I abuse the row lock I will consider other approaches, such as Apache ZooKeeper or something.
                              >
                              That is nothing but jibberish.

                              Since you don't want to respond to any of my questions or explain the problem you are trying to solve I can't help you.

                              Good luck. Maybe someone else can help.
                              • 12. Re: how and when the database release a row lock?
                                sb92075
                                rp0428 wrote:
                                Good luck. Maybe someone else can help.
                                +1
                                • 13. Re: how and when the database release a row lock?
                                  939520
                                  I don't know why you need the locking mechanism you propose (I don't know your requirements). Perhaps you could spend some time looking at a solution that doesn't involve such a locking mechanism (don't be too fond on holding onto an approach and sticking to it no matter what).

                                  As a possible solution, how about adding a column to your records called 'version' which is of type numeric. Each time the record is updated, have a database trigger an increment of the column value. This way, anyone wanting to update the record can check to see if some other process altered the record. Example: if he fetches a record with version=4, then his update sql statement would be something like 'update myTable set name='Joe' where version=4'. If it fails to find the record, he knows someone updated the record while he was processing it and he can take an appropriate corrective action. Note alternatively, 'version' can be a timestamp instead of a number.
                                  • 14. Re: how and when the database release a row lock?
                                    jgarry
                                    SuoNayi wrote:
                                    This approach is derived from some open source software and I want to use it for my distributed lock mechanism.
                                    Since the database is a infrastructure and you can use it very handy.
                                    If I abuse the row lock I will consider other approaches, such as Apache ZooKeeper or something.
                                    Well, assuming that something open source is done properly or appropriate for what you do is pretty iffy.

                                    I suggest you get books by Tom Kyte and Jonathan Lewis after reading the concepts guide in the regular documentation for your version.

                                    You may also be using the word "distributed" in a way different than we take it. It kind of sounds like you are reinventing the carburetor in the age of fuel injection.