Automatically unlock tables upon control C

Question

I have observed that if I hit control C on a running perl DBI script that the next run of the same script will hang.

I have confirmed with ps that the script is not running after I click control C.

I am using PerlDBI and DB2.

1. How can I run a query to see outstanding locks?
2. How can I change my code so it will do a rollback and unlock the tables upon control C.
3. How can I release the locks from a perl script?

Answer 1

Application processes, concurrency, and recovery:

The database manager releases all locks it has acquired and retained on behalf of an application process when that process ends.

If your application has really ended, then all its locks must be released (we don't take into account 2-phase commit transactions here - things are more complex in this case).
So, when you press Ctrl-C, then different things may happen - you ether don't terminate the client connection or DB2 still believes, that this connection is alive (but DB2 should detect unexpected client termination automatically and disconnect the application releasing its locks).

You may use the following methods to understand what happens with distinct database connection.
The SYSIBMADM.MON_LOCKWAITS view shows who is holder (HLD_* columns) and who is waiter (REQ_* columns) and what's the table (TABSCHEMA, TABNAME) is the object of this lock wait.

The MON_GET_LOCKS table function shows the locks, say, held by some application handle (session) or placed to some table. A couple examples.

If you know an application handle (XXX):

SELECT 
  F.TABSCHEMA, F.TABNAME, L.LOCK_OBJECT_TYPE, L.LOCK_MODE 
FROM 
  TABLE (MON_GET_LOCKS('<application_handle>XXX</application_handle>', -2)) L
, TABLE 
  (
    SELECT 
      MAX (DECODE (NAME, 'TABSCHEMA', VALUE::VARCHAR (128))) AS TABSCHEMA
    , MAX (DECODE (NAME, 'TABNAME', VALUE::VARCHAR (128)))   AS TABNAME
    FROM TABLE (MON_FORMAT_LOCK_NAME (L.LOCK_NAME))
  ) F

If you know the full table name: MY_SCHEMA.MY_TABLE:

SELECT APPLICATION_HANDLE, LOCK_OBJECT_TYPE, LOCK_MODE 
FROM TABLE (MON_GET_LOCKS('<table_schema>MY_SCHEMA</table_schema><table_name>MY_TABLE</table_name>', -2))

An application can be forced off the database by its application handle with the FORCE APPLICATION command using the ADMIN_CMD procedure. For example, if you see some lock wait, you may take the value (say, XXX) in SYSIBMADM.MON_LOCKWAITS.HLD_APPLICATION_HANDLE (the application handle holding the lock) and use it in the following call to force it off the database:

CALL SYSPROC.ADMIN_CMD('force application (XXX)')