[db2 v11 Z/os] Locking, LKSIZE, Deadlocks

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Thanks,

Phil Sevetson
Somewhere in the Bomb Cyclone
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Joe Geller

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

Phil,

There is a lot of material on locking out there, but the place I would start is the IDUG Content Blog from this past October.  It was locking month, with several very good articles and and really excellent video of Steve Thomas' IDUG Presentation on locking, latches, claims and drains - http://www.idug.org/p/bl/et/blogaid=674.

As for your problem, if you have locksize row, I would look to see if the lock contention is on the indexes rather than the table.  An index entry has to go in the right spot of the index, so the previous or next entry (and possibly the non-leaf page) may get locks that could also be required by other concurrent inserts.

Joe

In Reply to Philip Sevetson:

Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Thanks,

Phil Sevetson
Somewhere in the Bomb Cyclone
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Joe Geller)
Thanks, Joe. I’ll have a look.


From: Joe Geller [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 2:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


Phil,

There is a lot of material on locking out there, but the place I would start is the IDUG Content Blog from this past October. It was locking month, with several very good articles and and really excellent video of Steve Thomas' IDUG Presentation on locking, latches, claims and drains - http://www.idug.org/p/bl/et/blogaid=674.

As for your problem, if you have locksize row, I would look to see if the lock contention is on the indexes rather than the table. An index entry has to go in the right spot of the index, so the previous or next entry (and possibly the non-leaf page) may get locks that could also be required by other concurrent inserts.

Joe

In Reply to Philip Sevetson:
Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Thanks,

Phil Sevetson
Somewhere in the Bomb Cyclone
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)



In Reply to Philip Sevetson:

Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Phil,

The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.

Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.

Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.

Row Level Locking can be a way to greatly decrease locking problems, not on the INSERTs themselves but other processing, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 05, 2018 - 02:07 AM (Europe/Berlin)

venkata rama rajesh mallina

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

As per my knowledge...

  • For LOCKSIZE ROW, Insert acquires X-lock on row level, and IX-lock on table/tablespace level.These locks will be there until commit point.
  • X-Lock on row does not allow any other SQL's to operate on same row. IX lock on tablespace allows other selects, Inserts, Updates on the same table, but on different rows.
  • Display on tablespace does not show any row or page locks. It will show locks on table/tablespace. To observe row and page locks we should go through omegamon tool or any another substitute tool.
  • If your unit of work containing only INSERTs means, I am not realizing how deadlock is happening. FYI, Timeout issue also shows same SQLCODE.
  • Actually with LOCKSIZE ROW means probability of deadlocks and timeouts will reduce. 
  • If possible increase commit frequency such that held locks are released soon.
  • Check another transaction which was causing deadlock/timeout with this transaction.(If it is timeout issue and query is SELECT, then ISOLATION UR may resolve the issue)
  • Make sure there no lock escalation is happening. If lock escalation happened, Then X-lock on row level is moved to table level. Then It will not allow any other transactions on same table which causes deadlock and timeout issues.
  • If lock escalation is happening, Avoid the escalation by increasing commit frequency or increasing LOCKMAX value.
  • If you not able to conclude what set of transactions are causing deadlock or timeout then with the help of DB2 system programmers, enable  Performance trace class 3,7 and statistics trace class 3 and study the reports

Venkata Rama Rajesh

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Michael Hannan)
Michael,

Well… crap. First, thank you very much for the redbook reference. I’ll drag that up and dig in.

Second, not so good.

1) Not a data sharing shop, anywhere, at all.

2) We’re watching our deadlocking issues with BMC Mainvew/DB2. It’s identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.

3) The “current statement” in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what’s “running”.

4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).

5) The tablespace LKSIZE is ROW.

I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I’m correct, then this is contradicting the “no locking conflicts on INSERTs” statement which you’ve made. Since LKSIZE is ROW, no lock escalation should be taking place.

I’m currently trying to identify what I’ve got wrong in my description of the situation, because I’m describing something which you (and a couple of others) are telling me can’t be happening. If I can figure this out, I’m sure it’ll cause progress in identifying the actual problem.

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 8:03 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks



In Reply to Philip Sevetson:
Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Phil,

The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.

Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.

Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.

Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Daniel Luksetich

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)


could there be possible lock escalation?

Dan

On 01.05.2018 07:29, Sevetson, Phil wrote:

> Michael,
>
> Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.
>
> 3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".
>
> 4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.
>
> I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.
>
> --Phil
>
> FROM: Michael Hannan [mailto:[login to unmask email]
> SENT: Thursday, January 04, 2018 8:03 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> In Reply to Philip Sevetson:
>
>> Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!
>>
>> I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.
>>
>> Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message----- **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/?post=184277&anc=p184277#p184277
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Daniel Luksetich)
Dan,

My understanding is that LKSIZE=ROW disallows that. I checked the space setting a few minutes ago, and that’s the setting. Also, the analyst says there’s no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would be the other way of causing that.

Had another brainstorm, though. Writing it up for a follow-on post.

--Phil

From: Daniel Luksetich [mailto:[login to unmask email]
Sent: Friday, January 05, 2018 10:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


could there be possible lock escalation?

Dan




On 01.05.2018 07:29, Sevetson, Phil wrote:
Michael,

Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.

Second, not so good.

1) Not a data sharing shop, anywhere, at all.

2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.

3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".

4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).

5) The tablespace LKSIZE is ROW.

I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.

I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 8:03 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks



In Reply to Philip Sevetson:
Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Phil,

The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.

Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.

Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.

Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Daniel Luksetich

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)


NUMLKTS and NUMLKUS sysparms control escalation.

Dan

On 01.05.2018 07:55, Sevetson, Phil wrote:

> Dan,
>
> My understanding is that LKSIZE=ROW disallows that. I checked the space setting a few minutes ago, and that's the setting. Also, the analyst says there's no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would be the other way of causing that.
>
> Had another brainstorm, though. Writing it up for a follow-on post.
>
> --Phil
>
> FROM: Daniel Luksetich [mailto:[login to unmask email]
> SENT: Friday, January 05, 2018 10:46 AM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> could there be possible lock escalation?
>
> Dan
>
> On 01.05.2018 07:29, Sevetson, Phil wrote:
>
> Michael,
>
> Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.
>
> 3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".
>
> 4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.
>
> I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.
>
> --Phil
>
> FROM: Michael Hannan [mailto:[login to unmask email]
> SENT: Thursday, January 04, 2018 8:03 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> In Reply to Philip Sevetson:
>
> Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!
>
> I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.
>
> Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
>
> -----End Original Message-----

-----End Original Message----- **This e-mail, including any attachments,
may be confidential, privileged, or otherwise legally protected. It is
intended only for the addressee. If you received this e-mail in error or
from someone who was not authorized to send it to you, do not
disseminate, copy, or otherwise use this e-mail or its attachments.
Please notify the sender immediately by reply e-mail and delete the
e-mail from your system.**

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


Links:
------
[1] http://www.idug.org/p/fo/st/?post=184279&amp;anc=p184279#p184279
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)



In Reply to Philip Sevetson:


2) We’re watching our deadlocking issues with BMC Mainvew/DB2. It’s identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.

3) The “current statement” in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what’s “running”.

4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).

5) The tablespace LKSIZE is ROW.

Phil,

"two tables, both doing Inserts to same table" did not quite make sense. Obviously Typos.

O.K. So Row Level Locking cannot lock a page in the table nor index nor spacemap pages (the later two are only latched briefly). INSERT does not need to wait for a lock to insert a row. Exception is Referential Integrity Check. Having successfully Inserted a row, lock is held on the row till commit. Another transaction could be waiting on that lock and potentially get deadlocked.

Deadlock example: Tran A holds lock on X but wants to read or update Y. Tran B holds lock on Y but wants to read or update X. So the held locks could be caused by the INSERTs. The locks waited on cannot be on an INSERT unless a Referential integrity check causes read of a parent. Indirectly triggered actions could also be involved in deadlocks.

If too many row locks are held, and escalation to Partition or table level is needed by two processes, that could cause a deadlock I guess, both processes holding a shared intent lock on the partition or table put wanting to take an exclusive lock on it. I would have to go read the fine detail.

Hope this all makes sense.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 05, 2018 - 05:15 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 05, 2018 - 05:27 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 05, 2018 - 05:35 PM (Europe/Berlin)

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Daniel Luksetich)
I have a slightly different question, first.

If I’m INSERTing VALUES(x,x,x) to a table T1, and T1 has a unique index defined on it, does this serialize (prevent) other inserts to T1 until my UOW commits? Because that’s what the UOWs are doing – INSERTing rows to a table with a unique index.

So after the insert, and before the commit, you have a row whose value(s) for the index column(s) is present but not committed. Does that force DB2 to hold off all further inserts until COMMIT/ROLLBACK??

--Phil

From: Daniel Luksetich [mailto:[login to unmask email]
Sent: Friday, January 05, 2018 11:01 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


NUMLKTS and NUMLKUS sysparms control escalation.

Dan




On 01.05.2018 07:55, Sevetson, Phil wrote:
Dan,

My understanding is that LKSIZE=ROW disallows that. I checked the space setting a few minutes ago, and that's the setting. Also, the analyst says there's no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would be the other way of causing that.

Had another brainstorm, though. Writing it up for a follow-on post.

--Phil

From: Daniel Luksetich [mailto:[login to unmask email]
Sent: Friday, January 05, 2018 10:46 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


could there be possible lock escalation?

Dan




On 01.05.2018 07:29, Sevetson, Phil wrote:
Michael,

Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.

Second, not so good.

1) Not a data sharing shop, anywhere, at all.

2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.

3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".

4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).

5) The tablespace LKSIZE is ROW.

I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.

I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, January 04, 2018 8:03 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks



In Reply to Philip Sevetson:
Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!

I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.

Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.

Phil,

The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.

Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.

Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.

Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Isaac Yassin

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)
Hi Phil,
On item 2 you wrote "two tables, both doing inserts to the same table. "
- I guess you meant "two* programs*, both doing inserts to the same table".
What do you see on the XXXXMSTR log? - The DSNT500I message for that
deadlock?
If it's insert only you shouldn't get locks on the same data page as DB2
will choose another page for the insert. On the index page you'll have
short latches (not locks). Can it be that an insert is followed by "read
for update"?



*Isaac Yassin IBM Gold Consultant*
*IBM Champion for Analytics*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9,10,11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair
IDUG GMC



On Fri, Jan 5, 2018 at 5:29 PM, Sevetson, Phil <[login to unmask email]> wrote:

> Michael,
>
>
>
> Well… crap. First, thank you very much for the redbook reference. I’ll
> drag that up and dig in.
>
>
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We’re watching our deadlocking issues with BMC Mainvew/DB2. It’s
> identifying one of our deadlocks as (among other things): two tables, both
> doing inserts to the same table.
>
> 3) The “current statement” in the THDACTV / S selection of the
> deadlocked/waiting threads, shows the INSERT statement as what’s “running”.
>
> 4) The deadlocked resource is identified as a page of the table
> (six-byte RBA, all six bytes identical between the two waiting threads; or
> first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
>
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the
> locking). If I’m correct, then this is contradicting the “no locking
> conflicts on INSERTs” statement which you’ve made. Since LKSIZE is ROW, no
> lock escalation should be taking place.
>
>
>
> I’m currently trying to identify what I’ve got wrong in my description of
> the situation, because I’m describing something which you (and a couple of
> others) are telling me can’t be happening. If I can figure this out, I’m
> sure it’ll cause progress in identifying the actual problem.
>
>
>
> --Phil
>
>
>
> *From:* Michael Hannan [mailto:[login to unmask email]
> *Sent:* Thursday, January 04, 2018 8:03 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
>
>
>
>
> In Reply to Philip Sevetson:
>
> Is there a discussion, somewhere, about what kinds of locks an INSERT
> statement takes on a table with LKSIZE=ROW? I thought I recalled that a
> successful INSERT set up some kind of lock on the target page. But I don't
> know whether that's current code - I saw it maybe ten years ago!
>
> I ask because we're having a serious deadlocking issue, in production, and
> the statements in the suspended threads are all INSERTs to a table, all
> pointing to a small range of pages (INSERT hotspot). I need to start
> studying what best practices are for dealing with this problem, because I
> know others have run into it.
>
> Any pointers to the manual, redbooks, whitepapers, or even suggested
> Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2
> z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out
> of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on
> whether you are in Data Sharing or not. Insert has relatively few problems
> in non Data Sharing since it conditionally locks a page (but not for row
> level locking). No page locks for row level locking at all. I don't think
> you can get a deadlock on the Insert itself but possibly on a subsequent
> SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page
> consistency P-locks. That is why we have Member Cluster to prevent
> different DS members from fighting each other for the same pages. In
> addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to
> further improve the local members by getting each Inserter thread to use
> different pages (thereby even reducing conditional lock overhead). I am not
> sure if Insert Algorithm 2 is used for Row Level Locking cases. I would
> have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems,
> depending on the cause. Many sites have been overly scared of it,
> especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Joe Geller

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Daniel Luksetich)

Phil,

Locksize Row can definitely have lock escalation - and in fact will get it sooner than locksize page.  What you shouldn't get is table row contention (on an insert, since each one should be a different row).  But if you had lock escalation, then when one of the transactions hits the limit (Dan gave you the zparms that define the limits), it would try to escalate to a tablespace lock, but would fail because the other transaction has exclusive locks.  But then your monitor should report it (I believe) as trying to get a tablespace lock, not a row lock, and not a page lock as you said it reported.

Joe

In Reply to Daniel Luksetich:



NUMLKTS and NUMLKUS sysparms control escalation.

Dan

On 01.05.2018 07:55, Sevetson, Phil wrote:

> Dan,
>
> My understanding is that LKSIZE=ROW disallows that. I checked the space setting a few minutes ago, and that's the setting. Also, the analyst says there's no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would be the other way of causing that.
>
> Had another brainstorm, though. Writing it up for a follow-on post.
>
> --Phil
>
> FROM: Daniel Luksetich [mailto:[login to unmask email]
> SENT: Friday, January 05, 2018 10:46 AM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> could there be possible lock escalation?
>
> Dan
>
> On 01.05.2018 07:29, Sevetson, Phil wrote:
>
> Michael,
>
> Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.
>
> 3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".
>
> 4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.
>
> I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.
>
> --Phil
>
> FROM: Michael Hannan [mailto:[login to unmask email]
> SENT: Thursday, January 04, 2018 8:03 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> In Reply to Philip Sevetson:
>
> Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!
>
> I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.
>
> Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
>
> -----End Original Message-----

-----End Original Message----- **This e-mail, including any attachments,
may be confidential, privileged, or otherwise legally protected. It is
intended only for the addressee. If you received this e-mail in error or
from someone who was not authorized to send it to you, do not
disseminate, copy, or otherwise use this e-mail or its attachments.
Please notify the sender immediately by reply e-mail and delete the
e-mail from your system.**

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


Links:
------
[1] http://www.idug.org/p/fo/st/?post=184279&anc=p184279#p184279
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Daniel Luksetich

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)


No, it does not. The only serialization that happens between concurrent
threads doing inserts is when there is an index non-leaf page split. In
that case there could be timeouts, but not deadlocks.

Dan

On 01.05.2018 08:11, Sevetson, Phil wrote:

> I have a slightly different question, first.
>
> If I'm INSERTing VALUES(x,x,x) to a table T1, and T1 has a unique index defined on it, does this serialize (prevent) other inserts to T1 until my UOW commits? Because that's what the UOWs are doing - INSERTing rows to a table with a unique index.
>
> So after the insert, and before the commit, you have a row whose value(s) for the index column(s) is present but not committed. Does that force DB2 to hold off all further inserts until COMMIT/ROLLBACK??
>
> --Phil
>
> FROM: Daniel Luksetich [mailto:[login to unmask email]
> SENT: Friday, January 05, 2018 11:01 AM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> NUMLKTS and NUMLKUS sysparms control escalation.
>
> Dan
>
> On 01.05.2018 07:55, Sevetson, Phil wrote:
>
> Dan,
>
> My understanding is that LKSIZE=ROW disallows that. I checked the space setting a few minutes ago, and that's the setting. Also, the analyst says there's no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would be the other way of causing that.
>
> Had another brainstorm, though. Writing it up for a follow-on post.
>
> --Phil
>
> FROM: Daniel Luksetich [mailto:[login to unmask email]
> SENT: Friday, January 05, 2018 10:46 AM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> could there be possible lock escalation?
>
> Dan
>
> On 01.05.2018 07:29, Sevetson, Phil wrote:
>
> Michael,
>
> Well... crap. First, thank you very much for the redbook reference. I'll drag that up and dig in.
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's identifying one of our deadlocks as (among other things): two tables, both doing inserts to the same table.
>
> 3) The "current statement" in the THDACTV / S selection of the deadlocked/waiting threads, shows the INSERT statement as what's "running".
>
> 4) The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the locking). If I'm correct, then this is contradicting the "no locking conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no lock escalation should be taking place.
>
> I'm currently trying to identify what I've got wrong in my description of the situation, because I'm describing something which you (and a couple of others) are telling me can't be happening. If I can figure this out, I'm sure it'll cause progress in identifying the actual problem.
>
> --Phil
>
> FROM: Michael Hannan [mailto:[login to unmask email]
> SENT: Thursday, January 04, 2018 8:03 PM
> TO: [login to unmask email]
> SUBJECT: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
> In Reply to Philip Sevetson:
>
> Is there a discussion, somewhere, about what kinds of locks an INSERT statement takes on a table with LKSIZE=ROW? I thought I recalled that a successful INSERT set up some kind of lock on the target page. But I don't know whether that's current code - I saw it maybe ten years ago!
>
> I ask because we're having a serious deadlocking issue, in production, and the statements in the suspended threads are all INSERTs to a table, all pointing to a small range of pages (INSERT hotspot). I need to start studying what best practices are for dealing with this problem, because I know others have run into it.
>
> Any pointers to the manual, redbooks, whitepapers, or even suggested Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2 z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on whether you are in Data Sharing or not. Insert has relatively few problems in non Data Sharing since it conditionally locks a page (but not for row level locking). No page locks for row level locking at all. I don't think you can get a deadlock on the Insert itself but possibly on a subsequent SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page consistency P-locks. That is why we have Member Cluster to prevent different DS members from fighting each other for the same pages. In addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to further improve the local members by getting each Inserter thread to use different pages (thereby even reducing conditional lock overhead). I am not sure if Insert Algorithm 2 is used for Row Level Locking cases. I would have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems, depending on the cause. Many sites have been overly scared of it, especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
>
> -----End Original Message-----

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

**This e-mail, including any attachments, may be confidential,
privileged, or otherwise legally protected. It is intended only for the
addressee. If you received this e-mail in error or from someone who was
not authorized to send it to you, do not disseminate, copy, or otherwise
use this e-mail or its attachments. Please notify the sender immediately
by reply e-mail and delete the e-mail from your system.**

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

-----End Original Message----- **This e-mail, including any attachments,
may be confidential, privileged, or otherwise legally protected. It is
intended only for the addressee. If you received this e-mail in error or
from someone who was not authorized to send it to you, do not
disseminate, copy, or otherwise use this e-mail or its attachments.
Please notify the sender immediately by reply e-mail and delete the
e-mail from your system.**

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


Links:
------
[1] http://www.idug.org/p/fo/st/?post=184284&amp;anc=p184284#p184284
[2] http://www.idug.org/p/fo/si/?topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.ESAIGroup.com/idug
[5] http://www.idug.org/p/cm/ld/fid=2

Isaac Yassin

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)
That should be wait not deadlock


*Isaac *


On Fri, Jan 5, 2018 at 6:11 PM, Sevetson, Phil <[login to unmask email]> wrote:

> I have a slightly different question, first.
>
>
>
> If I’m INSERTing VALUES(x,x,x) to a table T1, and T1 has a unique index
> defined on it, does this serialize (prevent) other inserts to T1 until my
> UOW commits? Because that’s what the UOWs are doing – INSERTing rows to a
> table with a unique index.
>
>
>
> So after the insert, and before the commit, you have a row whose value(s)
> for the index column(s) is present but not committed. Does that force DB2
> to hold off all further inserts until COMMIT/ROLLBACK??
>
>
>
> --Phil
>
>
>
> *From:* Daniel Luksetich [mailto:[login to unmask email]
> *Sent:* Friday, January 05, 2018 11:01 AM
>
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
>
>
> NUMLKTS and NUMLKUS sysparms control escalation.
>
> Dan
>
>
>
>
>
> On 01.05.2018 07:55, Sevetson, Phil wrote:
>
> Dan,
>
>
>
> My understanding is that LKSIZE=ROW disallows that. I checked the space
> setting a few minutes ago, and that's the setting. Also, the analyst says
> there's no LOCK TABLE / LOCK TABLESPACE SQL in the application, which would
> be the other way of causing that.
>
>
>
> Had another brainstorm, though. Writing it up for a follow-on post.
>
>
>
> --Phil
>
>
>
> *From:* Daniel Luksetich [mailto:[login to unmask email]
> <[login to unmask email]>]
> *Sent:* Friday, January 05, 2018 10:46 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
>
>
> could there be possible lock escalation?
>
> Dan
>
>
>
>
>
> On 01.05.2018 07:29, Sevetson, Phil wrote:
>
> Michael,
>
>
>
> Well... crap. First, thank you very much for the redbook reference. I'll
> drag that up and dig in.
>
>
>
> Second, not so good.
>
> 1) Not a data sharing shop, anywhere, at all.
>
> 2) We're watching our deadlocking issues with BMC Mainvew/DB2. It's
> identifying one of our deadlocks as (among other things): two tables, both
> doing inserts to the same table.
>
> 3) The "current statement" in the THDACTV / S selection of the
> deadlocked/waiting threads, shows the INSERT statement as what's "running".
>
> 4) The deadlocked resource is identified as a page of the table
> (six-byte RBA, all six bytes identical between the two waiting threads; or
> first five bytes identical).
>
> 5) The tablespace LKSIZE is ROW.
>
>
>
> I could, I suppose, be reading the MVDB2 panel incorrectly about the
> locking). If I'm correct, then this is contradicting the "no locking
> conflicts on INSERTs" statement which you've made. Since LKSIZE is ROW, no
> lock escalation should be taking place.
>
>
>
> I'm currently trying to identify what I've got wrong in my description of
> the situation, because I'm describing something which you (and a couple of
> others) are telling me can't be happening. If I can figure this out, I'm
> sure it'll cause progress in identifying the actual problem.
>
>
>
> --Phil
>
>
>
> *From:* Michael Hannan [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Sent:* Thursday, January 04, 2018 8:03 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
>
>
>
>
>
> In Reply to Philip Sevetson:
>
> Is there a discussion, somewhere, about what kinds of locks an INSERT
> statement takes on a table with LKSIZE=ROW? I thought I recalled that a
> successful INSERT set up some kind of lock on the target page. But I don't
> know whether that's current code - I saw it maybe ten years ago!
>
> I ask because we're having a serious deadlocking issue, in production, and
> the statements in the suspended threads are all INSERTs to a table, all
> pointing to a small range of pages (INSERT hotspot). I need to start
> studying what best practices are for dealing with this problem, because I
> know others have run into it.
>
> Any pointers to the manual, redbooks, whitepapers, or even suggested
> Google search terms would be helpful.
>
> Phil,
>
> The best comprehensive thing to read in my opinion is the redbook: DB2
> z/OS 9 Resource Serialisation and Concurrency. Locking info doesn't go out
> of date that quickly, mostly.
>
> Insert experiencing Locking delays and contention greatly depends on
> whether you are in Data Sharing or not. Insert has relatively few problems
> in non Data Sharing since it conditionally locks a page (but not for row
> level locking). No page locks for row level locking at all. I don't think
> you can get a deadlock on the Insert itself but possibly on a subsequent
> SELECT or UPDATE.
>
> Data Sharing complicates things a lot since it has to take page
> consistency P-locks. That is why we have Member Cluster to prevent
> different DS members from fighting each other for the same pages. In
> addition INSERT algorithm 2 was introduced (to Db2 12 I think it was) to
> further improve the local members by getting each Inserter thread to use
> different pages (thereby even reducing conditional lock overhead). I am not
> sure if Insert Algorithm 2 is used for Row Level Locking cases. I would
> have to look that up. There maybe no reason to.
>
> Row Level Locking can be a way to greatly decrease locking problems,
> depending on the cause. Many sites have been overly scared of it,
> especially in Data Sharing.
>
> Michael Hannan,
> DB2 Application Performance Specialist
> CPT Global Ltd
>
>
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
>
> -----End Original Message-----
>
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Tushar Jha

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Isaac Yassin)

Hi Phil,

In your 4th point, you mention the deadlocked resource as page  : 

"4)      The deadlocked resource is identified as a page of the table (six-byte RBA, all six bytes identical between the two waiting threads; or first five bytes identical)."

 

Even though you have LOCKSIZE ROW, then I am guessing it could be the SPACE MAP page or SPACE MAP pages.

I guess, there is a way to find out if the page is a space map or not, but I am not too sure about that though.

 

Thanks and Regards,

Tushar Jha

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Tushar Jha)



In Reply to Tushar Jha:

Even though you have LOCKSIZE ROW, then I am guessing it could be the SPACE MAP page or SPACE MAP pages.

In an earlier post I mentioned that Spacemap pages are not locked, only latched (or "page p-locked" in data sharing only for consistency - these would not deadlock as duration is negotiable ).

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)
Thanks, all.

I'm starting this as a new thread in the forum, apologies, but I want to reply to all of the posted thread-splits, and there doesn't seem to be an easier way to do that.

First, of course, "two tables inserting" should have been "two processes/programs inserting." Apologies, and thanks to those who fixed it for me.

Second, thanks for the clarification on escalations from row-level locking.

We appear to have possibly more than one class (java) participating in the mess, so we could have two processes in deadly embrace. I'm expecting, at the moment, that this is why we're seeing this. Trying to find out more.

Some of the classes do enough work that, in the right circumstances, they could maybe max out locks for a process. If that forces lock escalation on the table which MVDB2 is reporting, that could be the problem. Mostly, though, we don't think that many locks are involved.

That's all I've got for the moment. I very much appreciate the input on this problem.




**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Sam Baugh

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)
Just curious, I know that I can see the Lock Escalation occurrences in the
DB2 MSTR log, where do you see these in Mainview? If you don't already
know about it, check out the Mainview panel EZDLOCK,

On Fri, Jan 5, 2018 at 11:45 AM, Sevetson, Phil <[login to unmask email]>
wrote:

> Thanks, all.
>
>
>
> I’m starting this as a new thread in the forum, apologies, but I want to
> reply to all of the posted thread-splits, and there doesn’t seem to be an
> easier way to do that.
>
>
>
> First, of course, “two tables inserting” should have been “two
> processes/programs inserting.” Apologies, and thanks to those who fixed it
> for me.
>
>
>
> Second, thanks for the clarification on escalations from row-level
> locking.
>
>
>
> We appear to have possibly more than one class (java) participating in the
> mess, so we could have two processes in deadly embrace. I’m expecting, at
> the moment, that this is why we’re seeing this. Trying to find out more.
>
>
>
> Some of the classes do enough work that, in the right circumstances, they
> could maybe max out locks for a process. If that forces lock escalation on
> the table which MVDB2 is reporting, that could be the problem. Mostly,
> though, we don’t think that many locks are involved.
>
>
>
> That’s all I’ve got for the moment. I very much appreciate the input on
> this problem.
>
>
>
>
>
>
>
>
> **This e-mail, including any attachments, may be confidential, privileged,
> or otherwise legally protected. It is intended only for the addressee. If
> you received this e-mail in error or from someone who was not authorized to
> send it to you, do not disseminate, copy, or otherwise use this e-mail or
> its attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.**
> -----End Original Message-----
>

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

I have a slightly different question, first.

If I’m INSERTing VALUES(x,x,x) to a table T1, and T1 has a unique index defined on it, does this serialize (prevent) other inserts to T1 until my UOW commits? Because that’s what the UOWs are doing – INSERTing rows to a table with a unique index.

So after the insert, and before the commit, you have a row whose value(s) for the index column(s) is present but not committed. Does that force DB2 to hold off all further inserts until COMMIT/ROLLBACK??

--Phil

Phil,

You made me think about how Db2 enforces uniqueness with a Unique Index, in addition to possibility of locks on parent tables due to Referential Integrity.

If an INSERT encounters a duplicate key condition in the unique index, I am guessing it won't care if the prior Insert of the same key is committed or not (although not 100% sure). However if an other transaction has deleted that key, we would find a pseudo deleted RID. If new Inserter fails Lock avoidance (with CLSN and Punc bits), Db2 would have to try a lock on the underlying row for that pseudo-delete RID in order to be sure the Delete was committed, and to be able to reinsert that key again. It might even be a conditional lock, if does not want to wait for Deleter to commit, to allow Insert to proceed.

From the IBM web site :
"These pseudo-deleted index entries can also result in timeouts and deadlocks for applications that insert data into tables with unique indexes. "

Just trying to find all lock possibilities for an INSERT, although perhaps your deadlock was detected on some other SQL in the same transaction.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 06, 2018 - 03:11 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 06, 2018 - 03:13 AM (Europe/Berlin)

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Michael Hannan)

Phil,
Another excellent read: "DB2 for z/OS Best Practices - Locks and Latches" (John Campbell - Distinguished Engineer IBM). Easy to obtain with a web search, e.g. DB2zBP_LocksAndLatches_transcript.pdf

All John Campbell presentations are extremely worthwhile of course.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Steven Lamb

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Michael Hannan)

We've used APPEND to force Inserts to the end of the tablespace (with MEMBER CLUSTER in data sharing environments). Depends if you can (temporarily) cope with the unclustered data until you run a Reorg.

Bruce Williamson

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Steven Lamb)

Howzit Phil?

In addition to all the valuable info already provided, another useful resource is DB2 for z/OS Best Practices - Optimizing Insert Performance

Cheers
Bruce

 

P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and find

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Bruce Williamson)
We have identified the main lock cause, and it turns out not to be a problem with the INSERTs. No surprise to y’all, I know.

However, we also note that a lot of different processes (both ways: multiple users, and various statements/Java classes) making inserts on a few pages can cause performance problems, even with LKSIZE=ROW (TS and IX page splits). We’ve explicitly (re)CLUSTERed the target table in a way to eliminate the hot spot, added some FREEPAGE and PCTFREE (to TS and indexes), are instituting scheduled REORGs for it (as it’s known to have a lot of INSERT activity), and are strongly recommending that the owner project team institute archive/delete of old data.

Now, we have to explain to the customer why their other table, the one we’ve identified as the resource in a bunch of Deadlocks, needs to be used differently (or not at all). Currently, it is written to by an UPDATE statement to increment a data value, used as a unique TXN identifier. And the X-lock is held for the duration of the TXN.

--Phil

From: Bruce Williamson [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 4:11 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


Howzit Phil?

In addition to all the valuable info already provided, another useful resource is DB2 for z/OS Best Practices - Optimizing Insert Performance
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W7c1e3103820b_4d9e_add6_b5ade3280dcb/page/Insert%20Performance

Cheers
Bruce



P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement https://www.ibm.com/developerworks/rfe ), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" https://www.ibm.com/developerworks/rfe/execute?use_case=groupLanding&GROUP_ID=1283 group and find

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)
The fastest way to find a typo in something is to publish it. ETA: Main DEADLOCK cause.

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:33 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks

We have identified the main *deadlock* cause, and it turns out not to be a problem with the INSERTs. No surprise to y’all, I know.

However, we also note that a lot of different processes (both ways: multiple users, and various statements/Java classes) making inserts on a few pages can cause performance problems, even with LKSIZE=ROW (TS and IX page splits). We’ve explicitly (re)CLUSTERed the target table in a way to eliminate the hot spot, added some FREEPAGE and PCTFREE (to TS and indexes), are instituting scheduled REORGs for it (as it’s known to have a lot of INSERT activity), and are strongly recommending that the owner project team institute archive/delete of old data.

Now, we have to explain to the customer why their other table, the one we’ve identified as the resource in a bunch of Deadlocks, needs to be used differently (or not at all). Currently, it is written to by an UPDATE statement to increment a data value, used as a unique TXN identifier. And the X-lock is held for the duration of the TXN.

--Phil

From: Bruce Williamson [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 4:11 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


Howzit Phil?

In addition to all the valuable info already provided, another useful resource is DB2 for z/OS Best Practices - Optimizing Insert Performance
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/W7c1e3103820b_4d9e_add6_b5ade3280dcb/page/Insert%20Performance

Cheers
Bruce



P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement https://www.ibm.com/developerworks/rfe ), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" https://www.ibm.com/developerworks/rfe/execute?use_case=groupLanding&GROUP_ID=1283 group and find

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

In Reply to Philip Sevetson:

The fastest way to find a typo in something is to publish it. ETA: Main DEADLOCK cause.

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:33 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks

We have identified the main *deadlock* cause, and it turns out not to be a problem with the INSERTs. No surprise to y’all, I know.

However, we also note that a lot of different processes (both ways: multiple users, and various statements/Java classes) making inserts on a few pages can cause performance problems, even with LKSIZE=ROW (TS and IX page splits). We’ve explicitly (re)CLUSTERed the target table in a way to eliminate the hot spot, added some FREEPAGE and PCTFREE (to TS and indexes), are instituting scheduled REORGs for it (as it’s known to have a lot of INSERT activity), and are strongly recommending that the owner project team institute archive/delete of old data.

Now, we have to explain to the customer why their other table, the one we’ve identified as the resource in a bunch of Deadlocks, needs to be used differently (or not at all). Currently, it is written to by an UPDATE statement to increment a data value, used as a unique TXN identifier. And the X-lock is held for the duration of the TXN.

--Phil

Phil,

So amusing. This technique of updating Last Used Key, has been known to be a really bad one for many years.

I guess you have seen that IBM has provided some alternatives like Sequences, and Columns with Generated Values.

You could even have a table of key values yet to be used (row level locking), one per row, and read the next available one using SKIP Locked Data, assuming not Data Sharing. That would stop two transactions competing for the same key value. So IBM did certainly provide other ways to solve this problem. We even have background processes to cleanup pseudo-deleted RID pages.

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

[db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Michael Hannan)
I appreciate the due diligence. I am aware of most of these alternatives, and have proposed three of them as ways to fix the problem.

The application team is desperately holding on to their current architecture, because (1) it’s been in production for a year, and (2) a change involves them having to change several tables and a lot of code. I shrugged, and told them that they won’t be able to solve their problem with current architecture. If the volume was low enough that there weren’t any collisions (as in the past), it would work, but not with the number of people and functions using that row now.

--Phil

From: Michael Hannan [mailto:[login to unmask email]
Sent: Wednesday, January 10, 2018 9:02 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks


In Reply to Philip Sevetson:
The fastest way to find a typo in something is to publish it. ETA: Main DEADLOCK cause.

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2018 9:33 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks

We have identified the main *deadlock* cause, and it turns out not to be a problem with the INSERTs. No surprise to y’all, I know.

However, we also note that a lot of different processes (both ways: multiple users, and various statements/Java classes) making inserts on a few pages can cause performance problems, even with LKSIZE=ROW (TS and IX page splits). We’ve explicitly (re)CLUSTERed the target table in a way to eliminate the hot spot, added some FREEPAGE and PCTFREE (to TS and indexes), are instituting scheduled REORGs for it (as it’s known to have a lot of INSERT activity), and are strongly recommending that the owner project team institute archive/delete of old data.

Now, we have to explain to the customer why their other table, the one we’ve identified as the resource in a bunch of Deadlocks, needs to be used differently (or not at all). Currently, it is written to by an UPDATE statement to increment a data value, used as a unique TXN identifier. And the X-lock is held for the duration of the TXN.

--Phil

Phil,

So amusing. This technique of updating Last Used Key, has been known to be a really bad one for many years.

I guess you have seen that IBM has provided some alternatives like Sequences, and Columns with Generated Values.

You could even have a table of key values yet to be used (row level locking), one per row, and read the next available one using SKIP Locked Data, assuming not Data Sharing. That would stop two transactions competing for the same key value. So IBM did certainly provide other ways to solve this problem. We even have background processes to cleanup pseudo-deleted RID pages.



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

Sites should really trap these serious design faults during design/development, with some sort of review process.

UPDATE Table SET Key = Key + 1 WHERE CURRENT OF CURSOR;
or similar statement in the code is a dead giveaway, especially in conjunction with INSERTs, during access path review, but that maybe a bit too late in the process life cycle.

Perhaps Data Model review can pick it up. It is important the DB2 knowledgeable persons re-design the Data Model before it becomes impractical DB2 Tables matching the Data Model exactly. Most shops don't realise that the Data Model is already a design, since no two modellers produce the same "design" based on their conceptualisation of the data, and often/usually a bad one. The Data Modellers don't understand performance issues sufficiently. Redesign for performance is of extreme importance. O.K. That is one of my hobby-horses. Ha ha!

Key Increment is a good item for the standards list of "don't dos".

No one can hang onto horrors like this in Production. I am sure that one of the possible solutions can be achieved with only moderate changes. Depends on whether the key allocation is very performance critical or not, once locking problems are avoided.

In Reply to Philip Sevetson:

I appreciate the due diligence. I am aware of most of these alternatives, and have proposed three of them as ways to fix the problem.

The application team is desperately holding on to their current architecture, because (1) it’s been in production for a year, and (2) a change involves them having to change several tables and a lot of code. I shrugged, and told them that they won’t be able to solve their problem with current architecture. If the volume was low enough that there weren’t any collisions (as in the past), it would work, but not with the number of people and functions using that row now.

--Phil

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Michael Hannan

RE: [db2 v11 Z/os] Locking, LKSIZE, Deadlocks
(in response to Philip Sevetson)

Phil,

You have mentioned "to eliminate the hot spot". This concerns me a little. Normally a hot Spot is good for performance and hence we have the TABLE APPEND approach in order to make Insert performance better or get better throughput, by creating a hot spot and eliminating search for freespace. Hot spots decrease I/Os and Getpages. Insert at the end can do uneven page split in indexes. Data pages don't have the "split" at all.

In Data sharing it is more complicated due to page p-locks and there we have MEMBER CLUSTER to prevent contention problems on spacemap etc., however I recall you are not in Data Sharing. So I will leave DS aside for now.

If you have page level locking, there is a degradation caused by multiple threads trying to INSERT a row to the same page. DB2 takes a conditional lock on the page and if it finds the page is already locked by another thread, it will move on to select another page to INSERT to instead (nearby). Those attempted conditional lock failures can add a CPU overhead of maybe 10 to 20% (when I saw the overhead many years ago). DB2 z 12 has introduced Insert Algorithm 2, to cause each thread to have its own pages for Insert to overcome this overhead.

Row Level Locking should not incur the same conditional lock page retry.

Random Insert may reduce Index page splits given adequate freespace introduced by Reorg, however transactions doing multiple Inserts per transaction will be penalised by additional Data Getpages and additional random Index Getpages. 

Someone earlier already quoted the presentation by John Campbell on Insert Performance issues. Surely scattering the Inserts done by a thread is not improving its performance! I don't see hotspots as a bad thing at all for Insert, or  even for Update with Row Level locking, and particularly in a non-Data Sharing system. Remember that Insert a row can happen without having to wait for exclusive Lock (with only the special exceptions we already discussed in prior posts).

Which dominates the cost more, the table INSERTs, or the scanning queries on the table? If the former, normally Append is recommended. If the queries are more CPU hungry, then maybe important to Insert by a good cluster index, or use APPEND with regular Reorg. Random scattering of the rows is never my preferred option for Insert or for Queries on groups of rows.

The serious contention problems of Type 1 Indexes went away very long ago. Type 2 Indexes eliminated Index Page logical locks. Insert performs a lot better these days and IBM is always giving us enhancements to improve it, like detecting when to extend and preformat, so as not to cause a performance spike, and also the new Insert Algorithm 2.
 
In Reply to Philip Sevetson:

However, we also note that a lot of different processes (both ways: multiple users, and various statements/Java classes) making inserts on a few pages can cause performance problems, even with LKSIZE=ROW (TS and IX page splits). We’ve explicitly (re)CLUSTERed the target table in a way to eliminate the hot spot, added some FREEPAGE and PCTFREE (to TS and indexes), are instituting scheduled REORGs for it (as it’s known to have a lot of INSERT activity), and are strongly recommending that the owner project team institute archive/delete of old data.

--Phil

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 12, 2018 - 05:09 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 12, 2018 - 05:14 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 12, 2018 - 06:48 AM (Europe/Berlin)