DB2 function timing out with a deadlock or timeout error in query

Question

I have created the following DB2 function below:

CREATE FUNCTION CLAIMS.get_gross_amount
(payment_option varchar(30),fy date, released date, grossamt int, taxamt int)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
BEGIN
  declare gross_amt int;
  declare tax_amt int;
  
   IF (payment_option = 'overpayment') 
   THEN
     if (fy = '2021' AND released <= '2020-06-30') then
           SET gross_amt = grossamt;
     elseif (fy = '2021' and (released >= '2020-07-01' and released <= '2021-06-30'))
     then
           SET gross_amt = grossamt - taxamt;
           SET tax_amt = taxamt - 100;
     end if;
   ELSEIF (payment_option = 'underpayment' OR payment_option = 'backpayment')
   THEN
      if (fy = '2021' and (released >= '2020-07-01' and released <= '2021-06-30')) 
      then
          SET gross_amt = grossamt - taxamt;
          SET tax_amt = taxamt - 100;
       end if;
    ELSE
         SET gross_amt = grossamt;
   END IF;   
         
   RETURN gross_amt;
END

When I invoke this as follows:

select claims.get_gross_amount('overpayment','2021','2020-06-30',1000,100) from SYSIBM.SYSDUMMY1;

I am getting the below error:

17:24:07  FAILED  [SELECT - 0 rows, 300.297 secs]  [Code: -911, SQL State: 40001]  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".. SQLCODE=-911, SQLSTATE=40001, DRIVER=4.22.29  
select claims.get_gross_amount('overpayment','2021','2020-06-30',1000,100) from SYSIBM.SYSDUMMY1;

Any reason why a deadlock should occur?.

Thanks R

Answer 1

If you can repeat this over and over there might be a transaction open which have acquired locks on one of the rows you are accessing. You can check for long running transactions as:

select
    x.agent_id,
    substr(z.corr_TOKEN,1,12) as ip,
    cast(y.stmt_text as varchar(800)) as stmt,
    x.locks_held,
    x.APPL_IDLE_TIME,
    x.UOW_START_TIME
from sysibmadm.SNAPAPPL x
join sysibmadm.snapstmt y
    on x.agent_id = y.agent_id
join sysibmadm.SNAPAPPL_INFO z
    on x.agent_id = z.agent_id
where UOW_STOP_TIME is null
  and TIMESTAMPDIFF(4,CHAR(current_timestamp - UOW_START_TIME)) > 1
order by UOW_START_TIME
;