How table locking works on the AS400

[ddiamond]

I have a table on the AS400 that is maintained by triggers. Lets call that table NameKeys. Now every time a trigger fires that causes a record to be update, deleted, or inserted into NameKeys, I would expect there to be a record lock on NameKeys. But I would also expect the record lock to disappear as soon as the update, delete, or insert completes. What I am finding is that these locks a persisting long after the update/insert/delete completes, and that is causing all sorts of problems. Does anyone know if this is normal behavior for DB2 on the AS400 (V5R4)? Any suggestions on how to make the locks not persist so long?

 

[fredericofonseca]

you need to commit your changes as soon as possible.

Not doing it means the record will be kept locked until your application ends the connection.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ddiamond]

Here is an example of one of my triggers:

CREATE TRIGGER ADMSEARCH.TR_POLICYINFO_DEL_ZUMA 
  AFTER DELETE ON XGGNDTA.ZUMADF00 
  REFERENCING OLD O 
  FOR EACH ROW  
  MODE DB2SQL 
  BEGIN ATOMIC
    DECLARE V_MAMANU CHAR ( 6 ) ;
    DECLARE V_MAMASE CHAR ( 3 ) ;
    
    SET V_MAMANU = O.MAMANU ;
    SET V_MAMASE = O.MAMASE ;
    
    DELETE FROM ADMSEARCH.POLICYINFO 
    WHERE MASTER_NUMBER = V_MAMANU 
      AND MASTER_SEQUENCE = V_MAMASE ;
  END;

Because I am using begin atomic, I cannot commit within the block, but shouldn't the delete commit automatically when the trigger completes?