DB2 - L

 View Only
Expand all | Collapse all

Processors of Uncommitted Changes

  • 1.  Processors of Uncommitted Changes

    Posted Nov 12, 2021 06:50 AM

    Hi Mark,

     

    Subject: change to avoid the thread hijack.  ��

     

    Agree with all you've said.  There's a perfectly good Message Queueing product out there, which we have.  But as you say, folks go with what they know.

     

    In my case, the 2nd program knew what to process because it was being handed the row data via some CICS storage mechanism – a TSQ maybe?  Dunno.  Either way, it was being passed the row details so ran off to do what it needed to, then tried to SQL Delete the 'row' it had processed.  And occasionally it would do its work and try to delete the row before the original program had committed/terminated, resulting in a soft +100 failure.

     

    Can you guess their solution?  I bet you can.  It was a very small percentage of 'failures' where, unluckily, the triggered prog would finish before the triggering one had committed, so... they built a half-second delay into the called program.  Good, innit?  You can do it properly (use MQ, get the data to process from the table when it's good and ready, etc) or... bodge it and just wait half a second.

     

    Is it weißbier time yet?

     

     



  • 2.  RE: Processors of Uncommitted Changes

    Posted Nov 12, 2021 09:22 AM
    That got a laugh out of me. If I can ask...how many times did the developer give you the ol', "This code hasn't been changed in 500 years and it never had a problem before!" line? :)

    Yes, that's fine...until some unforeseen system condition causes a delay in Process 1 longer than 1/2 second. And the more I sit here and toss this around in my brain, the more it sounds like a recipe for all kinds of heuristically broken stuff. 

    I'm wondering why they wouldn't just COMMIT before kicking off the second async process...maybe they had other data changes on the same transaction/UOW?

    Or simply change the ISO on the package for process #2 to something like CS CURRENDATA(YES)...if all it does is pick up that row and process it. Process #1 holds a lock on the row inserted...If #2 runs ISO CS CD=Y, it'll wait until process #1 commits and releases the lock. No guesswork at the amount of delay...most of the time would probably take LESS than 500ms. And no need to change the code!

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 3.  RE: Processors of Uncommitted Changes

    Posted Nov 12, 2021 09:47 AM

    So the 'beauty' of this is that... it's new code.  So more like, 'The code hasn't changed since we tried to push through more than 5 tractions'.  Or, 'It hasn't changed since we thought it would be a jolly wheeze to write a program to try to delete a row that might not actually be there yet.'  But hey, it's Agile, let's fail fast and plan to fail.  So that's those boxes ticked nicely.

     

    As it is, the 1st prog does COMMIT before kicking off the 2nd process.  Actually I'm not 100% sure about that.  It might call the 2nd program just before COMMITTing/SYNCPOINTing.  And have no choice, actually, as the Db2 commit is done implicitly during the EXEC CICS SYNCPOINT.  Hmmm... does that terminate the program/transaction too?  I'd assumed so, so difficult to call the 2nd program after you've just ended.  I'd ask my CICS bod but he's done a runner today already.

     

    Either way, they're brutally-efficient (i.e. fast) programs, and occasionally the called program will process the data fed to it and try to delete the original row before the called program's commit, er, commits.  We traced it and, in about 1K paired executions, one callee ended before the caller, so to speak. And that was the one that got +100.  Not sure ISO levels will matter for a program trying to look for a row only the caller knows exists.  It's not like it's waiting for a lock; it's waiting (not in a Db2 sense) for the called program to commit and make the row appear.  And I think this a direct DELETE, not a CURSOR, so CURRENTDATA I think won't to the party either.

     

    Wot eva.  It's nearly beer o'clock.  Actually it's nearly G&T o'clock.  Have to get them down me in the right order.  I can't start drinking my Franziskaner until I've COMMITted a snifter of Tanqueray's finest to my bloodstream.

     

    Cheers,

     

     

    Raymond

     

     






  • 4.  RE: Processors of Uncommitted Changes

    Posted Nov 12, 2021 11:08 AM
    If Process 1 commits the row before calling Process 2, and Process 2 can't find the row...that shouldn't happen, for any reason.

    I share your opinion about "Agile"...but at least they're breaking it into a billion micro-services, so they get small failures, instead of one big one. ;)

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 5.  RE: Processors of Uncommitted Changes

    Posted Nov 12, 2021 06:23 PM
    Perhaps (shudder) they are firing the 2nd process as an asynchronous process:

    Gather the data required for process-2
    RUN TRANSID(process-2) passing data in a channel
    SYNCPOINT

    --process-2 starts
    --process-2 issues DELETE
    --process-2 issues SYNCPOINT

    process-1 continues executing
    INSERT data into table

    Being a not-CICS person I am not sure that the the process-1 SYNCPOINT is required. I
    couldn't find a clear statement.

    The ultimate question is "does process-1 INSERT the row before or after starting
    process-2?"

    Something stronger than weißbier is called for.


    James Campbell


    On 12 Nov 2021 at 16:07, Mark Wieczorkowski via Intern wrote:

    > If Process 1 commits the row before calling Process 2, and Process 2 can't find the row...that shouldn't happen, for any reason.
    >
    > I share your opinion about "Agile"...but at least they're breaking it into a billion micro-services, so they get small failures, instead of one big one. ;)
    >
    > ------------------------------
    > MarkWieczorkowski...
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 12, 2021 09:46 AM
    > From: Raymond Bell
    > Subject: Processors of Uncommitted Changes
    >
    > So the 'beauty' of this is that... it's new code. So more like, 'The code hasn't changed since we tried to push through more than 5 tractions'. Or, 'It hasn't changed since we thought it would be a jolly wheeze to write a program to try to delete a row that might not actually be there yet.' But hey, it's Agile, let's fail fast and plan to fail. So that's those boxes ticked nicely.
    >
    > As it is, the 1st prog does COMMIT before kicking off the 2nd process. Actually I'm not 100% sure about that. It might call the 2nd program just before COMMITTing/SYNCPOINTing. And have no choice, actually, as the Db2 commit is done implicitly during the EXEC CICS SYNCPOINT. Hmmm... does that terminate the program/transaction too? I'd assumed so, so difficult to call the 2nd program after you've just ended. I'd ask my CICS bod but he's done a runner today already.
    >
    > Either way, they're brutally-efficient (i.e. fast) programs, and occasionally the called program will process the data fed to it and try to delete the original row before the called program's commit, er, commits. We traced it and, in about 1K paired executions, one callee ended before the caller, so to speak. And that was the one that got +100. Not sure ISO levels will matter for a program trying to look for a row only the caller knows exists. It's not like it's waiting for a lock; it's waiting (not in a Db2 sense) for the called program to commit and make the row appear. And I think this a direct DELETE, not a CURSOR, so CURRENTDATA I think won't to the party either.
    >
    > Wot eva. It's nearly beer o'clock. Actually it's nearly G&T o'clock. Have to get them down me in the right order. I can't start drinking my Franziskaner until I've COMMITted a snifter of Tanqueray's finest to my bloodstream.
    >
    > Cheers,
    >
    >
    > Raymond
    >
    >
    >
    >
    >
    > Original Message:
    > Sent: 11/12/2021 9:22:00 AM
    > From: Mark Wieczorkowski
    > Subject: RE: Processors of Uncommitted Changes
    >
    > That got a laugh out of me. If I can ask...how many times did the developer give you the ol', "This code hasn't been changed in 500 years and it never had a problem before!" line? :)
    >
    > Yes, that's fine...until some unforeseen system condition causes a delay in Process 1 longer than 1/2 second. And the more I sit here and toss this around in my brain, the more it sounds like a recipe for all kinds of heuristically broken stuff.
    >
    > I'm wondering why they wouldn't just COMMIT before kicking off the second async process...maybe they had other data changes on the same transaction/UOW?
    >
    > Or simply change the ISO on the package for process #2 to something like CS CURRENDATA(YES)...if all it does is pick up that row and process it. Process #1 holds a lock on the row inserted...If #2 runs ISO CS CD=Y, it'll wait until process #1 commits and releases the lock. No guesswork at the amount of delay...most of the time would probably take LESS than 500ms. And no need to change the code!
    >
    > ------------------------------
    > MarkWieczorkowski...
    > ------------------------------
    >
    > Original Message:
    > Sent: Nov 12, 2021 06:49 AM
    > From: Raymond Bell
    > Subject: Processors of Uncommitted Changes
    >
    > Hi Mark,
    >
    > Subject: change to avoid the thread hijack.
    >
    > Agree with all you've said. There's a perfectly good Message Queueing product out there, which we have. But as you say, folks go with what they know.
    >
    > In my case, the 2nd program knew what to process because it was being handed the row data via some CICS storage mechanism - a TSQ maybe? Dunno. Either way, it was being passed the row details so ran off to do what it needed to, then tried to SQL Delete the 'row' it had processed. And occasionally it would do its work and try to delete the row before the original program had committed/terminated, resulting in a soft +100 failure.
    >
    > Can you guess their solution? I bet you can. It was a very small percentage of 'failures' where, unluckily, the triggered prog would finish before the triggering one had committed, so... they built a half-second delay into the called program. Good, innit? You can do it properly (use MQ, get the data to process from the table when it's good and ready, etc) or... bodge it and just wait half a second.
    >
    > Is it weißbier time yet?
    >


    --
    This email has been checked for viruses by AVG.
    https://www.avg.com




  • 6.  RE: Processors of Uncommitted Changes

    Posted Nov 13, 2021 05:57 AM
    I think it's clear that Process 2 is async...if not, it would be under the same UOW and would find the row every time.

    If Process 1 is kicking off Process 2 BEFORE inserting the row, we've gone straight into Jagermeister-shot territory.

    ------------------------------
    MarkWieczorkowski...
    ------------------------------



  • 7.  RE: Processors of Uncommitted Changes

    Posted Nov 13, 2021 06:18 AM

    Hmmm... shots...   one tequila, two tequila, three tequila, floor.

     

    The processes are async, in that the 1st isn't waiting for confirmation the 2nd worked – or even successfully started.  As I understand it, and I could be wrong, the 1st Inserts the row, fires (by the power of Greyskull, or possibly just EXEC CICS START) the 2nd, and SYNCPOINTS.  And occasionally the 2nd does what it needs to, or attempts to, before the 1st's SYNCPOINT is complete i.e. before the row is visible to other processes.

     

    So it's not kicking it off before inserting the row (I hope; that truly would be stupid – and therefore entirely possible) but, occasionally, the 2nd prog does look for the row before the 1st has committed it.  So now every 2nd tran will effectively be half a second slower than it should be, just because they can't be @rsed to do it properly.

     

    Can I retire yet?  I hear Brisbane is nice – apart from the sharks, spiders, crocs, heat, lack of water...

     

    Cheers,

     

     

    Raymond

     

     






  • 8.  RE: Processors of Uncommitted Changes

    Posted Nov 14, 2021 07:57 PM
    When process-1 inserts the row there should be an exclusive lock on the row (based on
    index entry / tablespace RID ??). If this happened before process-2 did the DELETE
    process-2 would be held up waiting for process-1 to SYNCPOINT and release the lock.

    Therefore process-1 must be firing process-2 before the INSERT. Or there is a bug
    somewhere.

    Have you been able to see the code that process-1 executes?


    James Campbell


    On 13 Nov 2021 at 11:18, Raymond Bell via Internationa wrote:

    > Hmmm... shots... one tequila, two tequila, three tequila, floor.
    >
    > The processes are async, in that the 1st isn't waiting for confirmation the 2nd worked - or even successfully started. As I understand it, and I could be wrong, the 1st Inserts the row, fires (by the power of Greyskull, or possibly just EXEC CICS START) the 2nd, and SYNCPOINTS. And occasionally the 2nd does what it needs to, or attempts to, before the 1st's SYNCPOINT is complete i.e. before the row is visible toother processes.
    >
    > So it's not kicking it off before inserting the row (I hope; that truly would be stupid - and therefore entirely possible) but, occasionally, the 2nd prog does look for the row before the 1st has committed it. So now every 2nd tran will effectively be half a second slower than it should be, just because they can't be @rsed to do it properly.
    >
    > Can I retire yet? I hear Brisbane is nice - apart from the sharks, spiders, crocs, heat, lack of water...
    >
    > Cheers,
    >
    >
    > Raymond
    >
    >
    >
    >
    > -------------------------------------------
    > Original Message:
    > Sent: 11/13/2021 5:57:00 AM
    > From: Mark Wieczorkowski
    > Subject: RE: Processors of Uncommitted Changes
    >
    > I think it's clear that Process 2 is async...if not, it would be under the same UOW and would find the row every time.
    >
    > If Process 1 is kicking off Process 2 BEFORE inserting the row, we've gone straight into Jagermeister-shot territory.
    >
    > ------------------------------
    > MarkWieczorkowski...
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 12, 2021 06:22 PM
    > From: James Campbell
    > Subject: Processors of Uncommitted Changes
    >
    > Perhaps (shudder) they are firing the 2nd process as an asynchronous process:
    >
    > Gather the data required for process-2
    > RUN TRANSID(process-2) passing data in a channel
    > SYNCPOINT
    >
    > --process-2 starts
    > --process-2 issues DELETE
    > --process-2 issues SYNCPOINT
    >
    > process-1 continues executing
    > INSERT data into table
    >
    > Being a not-CICS person I am not sure that the the process-1 SYNCPOINT is required. I
    > couldn't find a clear statement.
    >
    > The ultimate question is "does process-1 INSERT the row before or after starting
    > process-2?"
    >
    > Something stronger than weißbier is called for.
    >
    >
    > James Campbell
    >
    >
    > On 12 Nov 2021 at 16:07, Mark Wieczorkowski via Intern wrote:
    >
    > > If Process 1 commits the row before calling Process 2, and Process 2 can't find the row...that shouldn't happen, for any reason.
    > >
    > > I share your opinion about "Agile"...but at least they're breaking it into a billion micro-services, so they get small failures, instead of one big one. ;)
    > >
    > > ------------------------------
    > > MarkWieczorkowski...
    > > ------------------------------
    > > -------------------------------------------
    > > Original Message:
    > > Sent: Nov 12, 2021 09:46 AM
    > > From: Raymond Bell
    > > Subject: Processors of Uncommitted Changes
    > >
    > > So the 'beauty' of this is that... it's new code. So more like, 'The code hasn't changed since we tried to push through more than 5 tractions'. Or, 'It hasn't changed since we thought it would be a jolly wheeze to write a program to try to delete a row that might not actually be there yet.' But hey, it's Agile, let's fail fast and plan to fail. So that's those boxes ticked nicely.
    > >
    > > As it is, the 1st prog does COMMIT before kicking off the 2nd process. Actually I'm not 100% sure about that. It might call the 2nd program just before COMMITTing/SYNCPOINTing. And have no choice, actually, as the Db2 commit is done implicitly during the EXEC CICS SYNCPOINT. Hmmm... does that terminate the program/transaction too? I'd assumed so, so difficult to call the 2nd program after you've just ended. I'd ask my CICS bod but he's done a runner today already.
    > >
    > > Either way, they're brutally-efficient (i.e. fast) programs, and occasionally the called program will process the data fed to it and try to delete the original row before the called program's commit, er, commits. We traced it and, in about 1K paired executions, one callee ended before the caller, so to speak. And that was the one that got +100. Not sure ISO levels will matter for a program trying to look for a row only the caller knows exists. It's not like it's waiting for a lock; it's waiting (not in a Db2 sense) for the called program to commit and make the row appear. And I think this a direct DELETE, not a CURSOR, so CURRENTDATA I think won't to the party either.
    > >
    > > Wot eva. It's nearly beer o'clock. Actually it's nearly G&T o'clock. Have to get them down me in the right order. I can't start drinking my Franziskaner until I've COMMITted a snifter of Tanqueray's finest to my bloodstream.
    > >
    > > Cheers,
    > >
    > >
    > > Raymond
    > >
    > >
    > >
    > >
    > >
    > > Original Message:
    > > Sent: 11/12/2021 9:22:00 AM
    > > From: Mark Wieczorkowski
    > > Subject: RE: Processors of Uncommitted Changes
    > >
    > > That got a laugh out of me. If I can ask...how many times did the developer give you the ol', "This code hasn't been changed in 500 years and it never had a problem before!" line? :)
    > >
    > > Yes, that's fine...until some unforeseen system condition causes a delay in Process 1 longer than 1/2 second. And the more I sit here and toss this around in my brain, the more it sounds like a recipe for all kinds of heuristically broken stuff.
    > >
    > > I'm wondering why they wouldn't just COMMIT before kicking off the second async process...maybe they had other data changes on the same transaction/UOW?
    > >
    > > Or simply change the ISO on the package for process #2 to something like CS CURRENDATA(YES)...if all it does is pick up that row and process it. Process #1 holds a lock on the row inserted...If #2 runs ISO CS CD=Y, it'll wait until process #1 commits and releases the lock. No guesswork at the amount of delay...most of the time would probably take LESS than 500ms. And no need to change the code!
    > >
    > > ------------------------------
    > > MarkWieczorkowski...
    > > ------------------------------
    > >
    > > Original Message:
    > > Sent: Nov 12, 2021 06:49 AM
    > > From: Raymond Bell
    > > Subject: Processors of Uncommitted Changes
    > >
    > > Hi Mark,
    > >
    > > Subject: change to avoid the thread hijack.
    > >
    > > Agree with all you've said. There's a perfectly good Message Queueing product out there, which we have. But as you say, folks go with what they know.
    > >
    > > In my case, the 2nd program knew what to process because it was being handed the row data via some CICS storage mechanism - a TSQ maybe? Dunno. Either way, it was being passed the row details so ran off to do what it needed to, then tried to SQL Delete the 'row' it had processed. And occasionally it would do its work and try to delete the row before the original program had committed/terminated, resulting in a soft +100 failure.
    > >
    > > Can you guess their solution? I bet you can. It was a very small percentage of 'failures' where, unluckily, the triggered prog would finish before the triggering one had committed, so... they built a half-second delay into the called program. Good, innit? You can do it properly (use MQ, get the data to process from the table when it's good and ready, etc) or... bodge it and just wait half a second.
    > >
    > > Is it weißbier time yet?
    > >
    >
    >
    > --
    > This email has been checked for viruses by AVG.
    > https://www.avg.com


  • 9.  RE: Processors of Uncommitted Changes

    Posted Nov 16, 2021 08:17 AM