Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2009
    Posts
    14

    Unanswered: When is Exclusive lock released

    Hi,

    I have a J2EE app which connects to DB2.
    In one method, I have a Stored procedure call which has INSERT SQL.

    I DON'T have any transaction mgmt in my application. In this case, when does the exclusive lock gets released?


    Thanks,
    Gokul

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Exclusive locks are usually released at the end of the transaction/unit-of-work. If you never COMMIT or ROLLBACK and if you also don't use auto-commit, the lock will be relased when your application disconnects from DB2.

    Java uses per default auto-commit, i.e. each SQL statement is automatically committed after its execution. IMHO that's a really, really stupid default, though. You should turn off auto-commit, don't use WITH HOLD cursors per default, and explicitly manage your transactions - unless you don't care much for performance and your application doesn't need any real consistency.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2009
    Posts
    14
    Thanks a lot for the Info.
    Does it mean that if AUTOCOMMIT is set to true, after the stored procedure call completes, commit happens and lock is released from the table?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by cse_gokul View Post
    Thanks a lot for the Info.
    Does it mean that if AUTOCOMMIT is set to true, after the stored procedure call completes, commit happens and lock is released from the table?
    Yes. If you are using DB2 LUW (instead of DB2 z/OS) I would consider putting the commit inside the SP if you don't have any other SQL within the same logical unit of work that needs to be executed. Doing that would increase concurrency because the locks taken by SQL in the SP will be released sooner if the commit is issued in the SP, rather than the program calling the SP.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can do that on z/OS as well, can't you?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze View Post
    You can do that on z/OS as well, can't you?
    I may be wrong (I am little rusty on z/OS and never created a SP on that platform) but I thought commits were not permitted in z/OS SP's?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2009
    Posts
    14
    It is possible in z/OS too. It is set in the SP defenition component of SP.


    In case my connection/datasource is managed through Application server, then if the auto-commit is ON, how do Application server manage transactions?
    My assumption is, when server/container manages connection, then when the transaction begins server sets to auto-commit to OFF and turns it to ON once the transaction completes.
    Please correct me if my wrong.

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    in z/OS "COMMIT ON RETURN YES" can only be used for a stored procedure if

    - it is not invoked by a user-defined function
    - it is not invoked by a trigger
    - it does not participate in a two-phase-commit

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by umayer View Post
    in z/OS "COMMIT ON RETURN YES" can only be used for a stored procedure if

    - it is not invoked by a user-defined function
    - it is not invoked by a trigger
    - it does not participate in a two-phase-commit
    I was referring to one or more explicit COMMIT statements within a SP, which is possible with LUW. Not sure about z/OS (and not sure if this has changed in the most recent release).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Dec 2005
    Posts
    273
    the manual states about the explicit COMMIT statement in V9 (z/OS):

    Restrictions on the use of COMMIT:

    The COMMIT statement cannot be used in a stored procedure if the procedure is in the calling chain of a user-defined function or a trigger or DB2 is not the commit coordinator.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by umayer View Post
    the manual states about the explicit COMMIT statement in V9 (z/OS):

    Restrictions on the use of COMMIT:

    The COMMIT statement cannot be used in a stored procedure if the procedure is in the calling chain of a user-defined function or a trigger or DB2 is not the commit coordinator.
    I beleive that it also states that a COMMIT cannot be used in a CICS or IMS environment, which I assume means when SP's are called from CICS or IMS programs. But cse_gokul is using J2EE so I assume it is OK in that case.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Dec 2009
    Posts
    14
    Yes, it's a J2EE applicaion calling Stored procedures on DB2 Linux.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •