DB2功能在查询中使用僵局或超时错误计时

Question

我在下面创建了以下DB2函数：

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

当我调用以下内容时：

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

我收到以下错误：

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;

应该发生僵局的任何原因？

谢谢 r

Answer 1

如果您可以一遍又一遍地重复此操作，则可能会有一个交易打开，该交易已在您要访问的一行上获取了锁。您可以检查长期运行的交易：

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
;