• 2 replies
  • Latest Post - ‏2012-05-03T17:10:28Z by krmilligan
22 Posts

Pinned topic Lock Table statement

‏2012-05-02T20:03:54Z |
Is there a companion statement to unlock a table in a stored procedure after a Lock Table statement has executed? I ask because I don't see an Unlock Table statement anywhere.

I have an SQL Stored Procedure in which we do a Lock Table, then a Select from that table, an update back into the same table, and then a COMMIT (or ROLLBACK in the event of an error).

According to the SQL Reference Manual, under the COMMIT statement, under the paragraph headed:
Effect of commit: Commit without HOLD causes the following to occur:

It says:

  • All locks acquired by the LOCK TABLE statement are released. All implicitly acquired locks are released, except for those required for the cursors that were not closed.

Apparently, the Lock Table statement sets an object lock and the Commit does not release that. There does not seem to be any kind of record lock left after the COMMIT, but I have not been able to get the object (or Member) lock released until the program that called the stored procedure ends. It is an RPG program compiled with CRTSQLRPGI using the CLOSQLCSR(*ENDMOD) parameter.

Question 1.) Is there a statement I can call while still in the stored procedure to release the table's object lock that I put on in that same stored procedure?

Question 2.) Is there any way to tell the underlying ILE C program created by the CREATE PROCEDURE to use the same kind of CLOSQLCSR(*ENDMOD) parameter that the RPG program is using or something that would be equivalent to that?
Updated on 2012-05-03T17:10:28Z at 2012-05-03T17:10:28Z by krmilligan
  • B.Hauser
    298 Posts

    Re: Lock Table statement

    What happens if:
    1. you close the activation group in which the RPG program runs?
    If it runs in the default activation group try to move the program in a named activation group.
    The default activation group may cause some problems
    2. you remove the library in which the locked table is located and add it after again?

  • krmilligan
    450 Posts

    Re: Lock Table statement

    I understand that you're seeing some locks after the stored procedure completes. However, I'm not convinced that it's the lock from the LOCK statement. Have you tried locking the same table in another connection and see if those "leftover" locks are causing lock conflicts?

    If you attend the DB2 for i SQL Performance Workshop (, you will learn that the SQL Engine caches some runtime structures to improve the performance of future executions of an SQL statement. This runtime structure caching results in some "soft" locks being acquired - these "soft" locks should not conflict with any application-level locks.

    Your answer to #2 is using the SET OPTION clause on your SQL Procedure definition.