Conference orarep::nomahs::rdb_60

~~                      <<< Note 5062.0 by BROKE::BASTINE >>>
~~                             -< Carry over locks >-

~~His question is, why would 1 transaction create carry over locks so early
~~in execution?  Are the subqueries responsible for the carry over locks?

Carry over locks are usually seen on the logical area (although larea locks 
aren't the only locks subject to the carry over behavior).  If the SET 
TRANSACTION has a RESERVING or has touched tables, then you might expect to 
see this behavior.

    It appears your customer has tripped over the dreaded "KODA long verb". 
    As soon as a long running verb (one that does not return to the user
    for a long time) starts then any blocking AST for the NOWAIT lock will
    be deferred until the current "thread" stops executing.  With
    complicated queries that can be a long time.

Thanks Paul, but how does this "long verb" relate to carry over locks?

As soon as the customer disabled carry over locks, the problem goes away.
Do long verbs use carry over locks?

Renee

Not sure if this helps... here is the query from the customer:

     
        Enclosed please find the 1st query that was running that caused the 
     2nd query (No wait transaction) to wait.
     
     ===================================================================
-- FILE:  41362c_TC_SUBS_NRTC.SQL
-- LOC:  [SONE]
-- PURPOSE:   TO COLLECT TOTAL COMMISSIONABLE REVENUE FOR CHAIN 13.
--            THIS PROGRAM WILL COLLECT SUBSCRIBERS TRANSACTIONS WITH 
P359,P932,P933,P934
-- DATE:  02/20/97
-- AUTHOR:  GAIL TAKAHASHI
$deassign sql$database
$setenv histrpt prod rouser
$sql
attach 'fi mrg_subrpt';
set transaction read only;
--set output dbssql_prod_root:[data.one_time_rpts]41362c_tc_subs_nrtc.lis;
set output dbssql_prod_root:[data.one_time_rpts]41362c_tc_subs_nrtc_sum.lis;
--select cast(an as char(11))||' '||
--       cast(ln as char(30))||' '||cast(fn as char(10))||' '||
--       cast(cast(timstmp as date ansi) as char(12))||' '||
--       cast(trnstype as char(4))||' '||cast(rsncd as char(5))||' '||
--       cast(amt as char(10))||' '||
--       cast(svpr as char(1))||' '||
--       cast(svcd as char(11))||' '||
--       cast(dan as char(11))
select sum(amt)
from
(
select 
an,ln,fn,timstmp,trnstype,rsncd,amt,svpr,svcd,seqnum,dan,max(dch.relation_end_dt
)
from 
(
select 
st.acct_num,sm.lst_name,sm.first_name,st.trans_dt_tim,st.trans_type,st.trans_rea
son_cd,st.trans_amt,
       st.svc_evt_prefix,st.svc_evt_cd,st.sequence_num,sm.dlr_acct_num
from subscr_transactions st
     ,subscr_master sm
where sm.acct_num=st.acct_num
      and st.trans_type in ('CHG','SAJ') and st.primary_owner in ('DTV','NRTC')
      and (st.secndry_owner='DTV' or st.secndry_owner > ' ')
      and st.svc_evt_prefix = 'P' and st.trans_reason_cd > 0
      and st.svc_evt_cd in (359,932,933,934)
      and st.trans_dt_tim >= '17-JUN-1995 00:00:00'
      and st.trans_dt_tim <= '01-JUN-1996 23:59:59'
) as t1(an,ln,fn,timstmp,trnstype,rsncd,amt,svpr,svcd,seqnum,dan),dlr_chain_sub 
dch
where dch.acct_num=an
      and dch.chain_num=13 
group by an,ln,fn,timstmp,trnstype,rsncd,amt,svpr,svcd,seqnum,dan
) as t2(an,ln,fn,timstmp,trnstype,rsncd,amt,svpr,svcd,seqnum,dan,e_date)
;
set nooutput;
exit;
$exit



---- End of Message ----

~~                      <<< Note 5062.3 by BROKE::BASTINE >>>
~~                            -< More questions...  >-

~~Thanks Paul, but how does this "long verb" relate to carry over locks?

Renee,

Paul kinda explained long verbs in his reply...

~~    As soon as a long running verb (one that does not return to the user
~~    for a long time) starts then any blocking AST for the NOWAIT lock will
~~    be deferred until the current "thread" stops executing.  With
~~    complicated queries that can be a long time.

The complicated query is the "long verb".  Blocking ASTs delivered to the 
blocker will not be handled until the current KODA verb is complete.  If 
the query is complicated, the KODA verb may be a lengthy operation and may 
cause the "blockee" to wait for a noticable length of time.

Carry over locks don't have anything to do with KODA verbs but they do work 
by notifying blockers, via blocking ASTs, that they need to possibly demote 
locks which were carried over.  It's the blocker with the long verb, not 
the blockee.

    Blocking ASTs (BLASTs) are often not handled until a current unit of
    work (like a "verb") completes.  Thus any process that needs a lock
    held by another process very often has to wait until the other process
    has completed its unit of work.
    
    When you have carry-over locks enabled then processes doing nowait
    locks have to obtain the "NOWAIT" lock to signal other processes to not
    carry over locks.  That means that the process doing the NOWAIT txn
    will have to wait until other processes doing normal WAIT txns notice
    that they are being signaled to not do carry over larea locks. See the
    Rdb Internals book for more details.
    
    If your customer is often going to use NOWAIT txns then they may as
    well disable carry-over locks since they effectively won't be carrying
    over locks anyway.

>    When you have carry-over locks enabled then processes doing nowait
>    locks have to obtain the "NOWAIT" lock to signal other processes to not
>    carry over locks.  That means that the process doing the NOWAIT txn
>    will have to wait until other processes doing normal WAIT txns notice
>    that they are being signaled to not do carry over larea locks. See the
>    Rdb Internals book for more details.
    
This is EXACTLY what I was looking for!!! :)  Thanks a million!

Renee