2 Replies Latest reply: Jan 19, 2013 3:37 AM by Billy~Verreynne RSS

    Explicit locking -- answered

      post removed. ...

      did not know about "Select ... FOR UPDATE *WAIT N*" to ask Oracle to try to lock for a few seconds before returning with an error.

      Thanks Etbin!

      Edited by: Steve Cosner on Jan 19, 2013 1:21 AM
      My only excuse is that I started out in Oracle 7. Pretty sure the feature was not available then.
        • 1. Re: Explicit locking
          I always wished there were an Oracle locking tool that would automatically attempt to lock a record, and keep trying for a given number of seconds, and then exit with failure or success depending on whether the lock succeeded. Such as: Select .... for update wait 5 seconds. ...
          no version specified but check [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2126016]for update clause 11g [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2126016]for update clause 10g [url http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_103a.htm#2065798]for update clause 9i


          • 2. Re: Explicit locking
            One designs an application for the database. The database is the core.

            Designing an application around how one believes locking should work, and then plonking that application code on a specific database product.. pretty poor idea.

            There is nothing wrong with how Oracle implements concurrency and locking. Fact: it is superior to SQL-Server, Sybase, mySQL and many other products. (interestingly, Borland's Interbase/OpenSource Firebase also use a MVCC model like Oracle - one of a few that has a sane and scalable locking model)

            If the issue is serialising access to available seats, then that can fairly easily be done by maintaining a single row that contains the number of seats available.

            Best implemented via a PL/SQL abstraction layer that is used by the application layer (such as Forms).

            Consider the use of actual SQL statements in the application layer a fundamental violation - as that nullifies the abstraction layer and requires each and every application that use the data directly via SQL to understand how locking in Oracle works, what MVCC is, and to play by the same rules in order to adhere to the business rules and implement consistent business transactions.

            And as you seen - applications are written by developers with little to no uderstanding of locking and what concurrency means in a multi-user and multi-process database environment. Even if one application does it right, the next one can royaly muck it up, rendering the good application useless as the shoddy one.

            Application developers need to use a PL/SQL abstraction interface as API - and not construct and write SQL code.