Locking Contention w/RELEASE(DEALLOCATE)

Mark Madsen

Locking Contention w/RELEASE(DEALLOCATE)

Say my online package has RELEASE(DEALLOCATE) defined.

While my online is up and available I have a batch job that wants to do a LOCK TABLE statement in a statically bound program to do a large singleton delete SQL on one of the tables referenced in the package above.  Will the LOCK TABLE statement fail or be successful?

Michael Hannan

RE: Locking Contention w/RELEASE(DEALLOCATE)
(in response to Mark Madsen)

In Reply to Mark Madsen:

Say my online package has RELEASE(DEALLOCATE) defined.

While my online is up and available I have a batch job that wants to do a LOCK TABLE statement in a statically bound program to do a large singleton delete SQL on one of the tables referenced in the package above.  Will the LOCK TABLE statement fail or be successful?

I assume you mean to LOCK table in EXCLUSIVE MODE. LOCK table is a Lock rather than a Drain, so does not prevent other Claimers, e.g. the Onlines.

The LOCK will be released at COMMIT point assuming BIND RELEASE(COMMIT) for this process. It certainly can fail with a Timeout if cannot get the LOCK in the prescribed period. Utilities like Reorg, Drain objects and get a much longer time period to achieve that.

So your program might want to have logic to wait and retry in event of timeout. The advantage of LOCK table, is your process does not need to take a large number of row or page locks, during the DELETE, which might then escalate to a higher level lock anyway, if Escalation is enabled.

Even if the LOCK failed, you could try to execute the DELETE which would then have possibility to Timeout or Deadlock with Online processes. However once the Delete gets very far, it is more likely DB2 will choose to fail the online processes with any page or row deadlock problems.

We hope that Online processes normally complete quick enough to avoid Timing out the DELETE. Similarly we hope the Onlines commit quick enough to not timeout the LOCK Table, but it is possible. Ha ha! This optimism might be valid if the onlines were Release(commit). With Deallocate the online threads could hold high level table and tablespace locks past commit (until thread deallocate).

I think that if the LOCK Table fails with Timeout or Deadlock, after a some number of retries, it still maybe an option to try the DELETE anyway, if it will not cause Lock Escalation. If it will cause Lock Escalation, to exclusive Table Lock, then same problem probably exists (timeout due to online release deallocate high level locks).   I would try to design the DELETE process to delete a limited number of rows per COMMIT.

Use FETCH FIRST n ROWS ONLY (unfortunately only in Db2 12 NFM 500), to prevent lock escalation of the DELETE, execute in a loop with COMMIT, till no more rows found to delete. If the LOCK TABLE was successful, then this measure would not be needed.

There might be other ways to break up your DELETE into smaller non escalatable chunks. Design with intentional Lock exclusive on a table is usually problematic.

Consider changing all online packages that use you target table, other than WITH UR, to be Release(Commit). That is going  to help your LOCK and DELETE to succeed I think.

Online queries using Uncommitted Read (UR), can proceed despite your Table LOCK, as they don't take locks (except Mass Delete lock). So use of UR is highly recommended in online Cursors that will not be updated. Also for singleton SELECTs with no intention to update. With CS we get a Lock Avoidance process. With UR we get to avoid even that. There is not a big difference in safety between UR and CS. Many DB2 sites have been scared to use WITH UR quite unnecessarily, perhaps due to the very unfortunate term coined years ago "Dirty Read". Actually DB2 Latching prevents a page form being "dirty" (in the process of being updated).

CS can miss updates that about to happen in a different thread at the time query passes over. UR Likewise. In Data Sharing both of these can miss an Insert that has just commenced a very short time ago on another member, based on the logic that it is merely a coincidence the other member Insert already commenced. UR may retrieve rows Inserted or Updated by another process not yet committed. Chance of rollback is generally low. CS waits for a Lock first, unless using SKIP LOCKED DATA (sort of opposite to with UR action for rows locked by another process).

These were my personal opinions, of course. So will be interesting to see if others agree or not. It is worth reading redbooks on Locking and resource serialisation. e.g. "DB2 Concurrencency and Resource Serialisation for z V9".

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 03, 2018 - 06:08 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 03, 2018 - 06:17 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 03, 2018 - 06:18 AM (Europe/Berlin)

Walter Janißen

AW: Locking Contention w/RELEASE(DEALLOCATE)
(in response to Michael Hannan)
Hi

I can add: If the package runs a select WITH UR, it can successfully do so, even if another program has executed a LOCK TABLE IN EXCLUSIVE MODE.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Michael Hannan [mailto:[login to unmask email]
Gesendet: Dienstag, 3. April 2018 06:04
An: [login to unmask email]
Betreff: [DB2-L] - RE: Locking Contention w/RELEASE(DEALLOCATE)


In Reply to Mark Madsen:

Say my online package has RELEASE(DEALLOCATE) defined.

While my online is up and available I have a batch job that wants to do a LOCK TABLE statement in a statically bound program to do a large singleton delete SQL on one of the tables referenced in the package above. Will the LOCK TABLE statement fail or be successful?

I assume you mean to LOCK table in EXCLUSIVE MODE. LOCK table is a Lock rather than a Drain, so does not prevent other Claimers, e.g. the Onlines.

The LOCK will be released at COMMIT point assuming BIND RELEASE(COMMIT) for this process. It certainly can fail with a Timeout if cannot get the LOCK in the prescribed period. Utilities like Reorg, Drain objects and get a much longer time period to achieve that.

So your program might want to have logic to wait and retry in even of timeout. The advantage of LOCK table, is your process does not need to take a large number of row or page locks, during the DELETE, which might then escalate to a higher level lock anyway, if Escalation is enabled.

Even if the LOCK failed, you could try to execute the DELETE which would then have possibility to Timeout or Deadlock with Online processes. However once the Delete gets very far, it is more likely DB2 will choose to fail the online processes with any deadlock problems.

We hope that Online processes normally complete quick enough to avoid Timing out the DELETE. Similarly we hope the Onlines commit quick enough to not timeout the LOCK Table, but it is possible. Ha ha. This would be the case if onlines were Release(commit). With Deallocate the online threads could hold high level table and tablespace locks past commit. So for this reason, the LOCK Table is more likely to Timeout than the DELETE Table with predicates, statement.

I therefore think that if the LOCK Table fails with Timeout or Deadlock, after a some number of retries, it still maybe an option to try the DELETE anyway, if it will not cause Lock Escalation. If it will cause Lock Escalation, to exclusive Table Lock, then same problem probably exists. I would try to design the DELETE process to delete a limited number of rows per COMMIT.

Use FETCH FIRST n ROWS ONLY (unfortunately only in Db2 12 NFM 500), to prevent lock escalation of the DELETE, execute in a loop with COMMIT, till no more rows found to delete. If the LOCK TABLE was successful, then this measure would not be needed.

There might be other ways to break up your DELETE into smaller non escalatable chunks.

Online queries using Uncommitted Read (UR), can proceed despite your Table LOCK, as they don't take locks (except Mass Delete lock). So use of UR is highly recommended in online Cursors that will not be updated. Also for singleton SELECTs with no intention to update. With CS we get a Lock Avoidance process. With UR we get to avoid even that. There is not a big difference in safety between UR and CS. Many DB2 sites have been scared to use WITH UR quite unnecessarily, perhaps due to the very unfortunate term coined years ago "Dirty Read". Actually DB2 Latching prevents a page form being "dirty" (in the process of being updated).

CS can miss updates that about to happen in a different thread at the time query passes over. UR Likewise. In Data Sharing both of these can miss an Insert that has just commenced a very short time ago on another member, based on the logic that it is merely a coincidence the other member Insert already commenced. UR may retrieve rows Inserted or Updated by another process not yet committed. Chance of rollback is generally low. CS waits for a Lock first, unless using SKIP LOCKED DATA (sort of opposite to with UR action for rows locked by another process).

These were my personal opinions, of course. So will be interesting to see if others agree or not.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Mark Madsen

RE: Locking Contention w/RELEASE(DEALLOCATE)
(in response to Mark Madsen)

We initially tried setting the online package to RELEASE(DEALLOCATE) to reduce the excessive high-level locking/unlocking that was happening; and that seemed to be working well.  But when the batch delete job started up it tried to escalate it's lock to a tablespace X-lock; and that escalation failed due to the high-level locks retained on the object due to the RELEASE(DEALLOCATE) for the online package.  We changed the online package back to RELEASE(COMMIT) and now the batch delete causes an escalation to a TS X-lock and completes successfully.  But, what I'm hoping to know for sure is if we ask the application to add the LOCK TABLE IN EXCLUSIVE MODE in their delete program to make the deletes more efficient/quicker and the online package is bound w/RELEASE(DEALLOCATE), will the LOCK TABLE SQL time out/fail or not?  Yes we can test and find out, but I'd rather be able to predict this rather than say "give it a try and then we'll all know"...  We are not at Db2 V12 yet, so repetitive DELETEs is not an option yet.

Walter Jani&#223;en

AW: Locking Contention w/RELEASE(DEALLOCATE)
(in response to Mark Madsen)
Hi Mark

In this case I am pretty sure that the lock-statement doesn’t work. In my opinion is not a good choice that an application can only succeed, when it gets an exclusive lock on a tablespace. I always try to convince the application developer to use a cursor and issue commits appropriately.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Mark Madsen [mailto:[login to unmask email]
Gesendet: Dienstag, 3. April 2018 14:38
An: [login to unmask email]
Betreff: [DB2-L] - RE: Locking Contention w/RELEASE(DEALLOCATE)


We initially tried setting the online package to RELEASE(DEALLOCATE) to reduce the excessive high-level locking/unlocking that was happening; and that seemed to be working well. But when the batch delete job started up it tried to escalate it's lock to a tablespace X-lock; and that escalation failed due to the high-level locks retained on the object due to the RELEASE(DEALLOCATE) for the online package. We changed the online package back to RELEASE(COMMIT) and now the batch delete causes an escalation to a TS X-lock and completes successfully. But, what I'm hoping to know for sure is if we ask the application to add the LOCK TABLE IN EXCLUSIVE MODE in their delete program to make the deletes more efficient/quicker and the online package is bound w/RELEASE(DEALLOCATE), will the LOCK TABLE SQL time out/fail or not? Yes we can test and find out, but I'd rather be able to predict this rather than say "give it a try and then we'll all know"... We are not at Db2 V12 yet, so repetitive DELETEs is not an option yet.

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Michael Hannan

RE: Locking Contention w/RELEASE(DEALLOCATE)
(in response to Mark Madsen)

In Reply to Mark Madsen:

We initially tried setting the online package to RELEASE(DEALLOCATE) to reduce the excessive high-level locking/unlocking that was happening; and that seemed to be working well.  But when the batch delete job started up it tried to escalate it's lock to a tablespace X-lock; and that escalation failed due to the high-level locks retained on the object due to the RELEASE(DEALLOCATE) for the online package.  We changed the online package back to RELEASE(COMMIT) and now the batch delete causes an escalation to a TS X-lock and completes successfully.  But, what I'm hoping to know for sure is if we ask the application to add the LOCK TABLE IN EXCLUSIVE MODE in their delete program to make the deletes more efficient/quicker and the online package is bound w/RELEASE(DEALLOCATE), will the LOCK TABLE SQL time out/fail or not?  Yes we can test and find out, but I'd rather be able to predict this rather than say "give it a try and then we'll all know"...  We are not at Db2 V12 yet, so repetitive DELETEs is not an option yet.

So you experienced what I predicted with Release(Deallocate). Similarly the LOCK TABLE in Exclusive won't work, unless all online access in those Deallocate packages was to be read only WITH UR, as already mentioned. Then the online would not take those high level  locks (just shared Mass Delete Lock only - which is not a concern here I think since there is no Mass Delete without predicates).

Can't have your cake and eat it too. I could maybe design you a Delete SQL perhaps that gets around not being on V12, but deletes limited sets of records. I need a bit more time to think about that, but think I have tackled that before. Would help if you quoted the DELETE statement and indicated what is the unique key columns. 

Essentially it could be something like this in a loop:

DELETE FROM Table
  WHERE (key1, key2, etc.) IN 
  (SELECT key1, key2, etc. covering full unique key 
      FROM Table
      WHERE conditions
      FETCH FIRST n ROWS ONLY)
;

Just need to consider if this is going to give a really good access path. Normally IN is not my favourite way to go, but can be good if DB2 can convert this to a join like process. The FETCH n ROWS ONLY is to limit the number of keys participating in each repeat of the DELETE.

In Db2 V7, the above DELETE subquery was convertible to a Join if the key was a single column. Not sure if a subsequent release relaxed this condition. That requires research or just try it out and see what access path you get. Maybe can find this in the manual somewhere. Reading Db2 11 Performance Management re converting non-correlated Subqueries into Joins, it looks like DELETE maybe supported for multi-column key guaranteeing uniqueness. Exact set of rules for transform to join is not stated.
 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Apr 04, 2018 - 01:20 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 04, 2018 - 01:25 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 04, 2018 - 01:26 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Apr 04, 2018 - 01:37 PM (Europe/Berlin)