DB2函数超时，查询中出现死锁或超时错误

cxfofazt 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(241)

我创建了下面的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

db2

来源：https://stackoverflow.com/questions/72109362/db2-function-timing-out-with-a-deadlock-or-timeout-error-in-query

1条答案

按热度按时间

t98cgbkg1#

如果您可以不断重复此操作，则可能会有一个打开的事务处理在您正在访问的某个行上获得了锁。您可以按以下方式检查长时间运行的事务处理：

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
;

赞(0）回复(0）举报 2022-11-07

我来回答

DB2函数超时，查询中出现死锁或超时错误

1条答案

相关问题

热门标签

最新问答