DB2 LUW Deadlock

Deb Kumar

DB2 LUW Deadlock
Hi All,
Please suggest.If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and  21 to 40 million rows using user id B, will we face deadlock.


Bill Gallagher

[External] DB2 LUW Deadlock
(in response to Deb Kumar)
Are you deadlocking on the table you are deleting from, or on some other resource?

Is the table clustered or partitioned such that the rows that user A is deleting and the rows that user B is deleting may be on the same page?

What is the commit frequency of your delete process?

Bill Gallagher | Senior Systems Engineer, DBA

From: Debabrata <[login to unmask email]>
Sent: Monday, June 29, 2020 8:45 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - DB2 LUW Deadlock

***External Sender - Please Exercise Caution***

Hi All,

Please suggest.
If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and 21 to 40 million rows using user id B, will we face deadlock.




-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Deb Kumar

[External] DB2 LUW Deadlock
(in response to Bill Gallagher)

Hi Bill,
Table is not partitioned.
User A is deleting record from row 1 to 20  million from table X and User B is deleting record from row 21 to 41 million from table X in the same time. I will face deadlock or not.
On Monday, June 29, 2020, 6:48:12 PM GMT+5:30, Gallagher, Bill R <[login to unmask email]> wrote:

#yiv8877403295 #yiv8877403295 -- _filtered {} _filtered {} _filtered {}#yiv8877403295 #yiv8877403295 p.yiv8877403295MsoNormal, #yiv8877403295 li.yiv8877403295MsoNormal, #yiv8877403295 div.yiv8877403295MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv8877403295 a:link, #yiv8877403295 span.yiv8877403295MsoHyperlink {color:blue;text-decoration:underline;}#yiv8877403295 a:visited, #yiv8877403295 span.yiv8877403295MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv8877403295 p.yiv8877403295msonormal0, #yiv8877403295 li.yiv8877403295msonormal0, #yiv8877403295 div.yiv8877403295msonormal0 {margin-right:0in;margin-left:0in;font-size:11.0pt;font-family:sans-serif;}#yiv8877403295 span.yiv8877403295EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv8877403295 .yiv8877403295MsoChpDefault {font-size:10.0pt;} _filtered {}#yiv8877403295 div.yiv8877403295WordSection1 {}#yiv8877403295
Are you deadlocking on the table you are deleting from, or on some other resource?

 

Is the table clustered or partitioned such that the rows that user A is deleting and the rows that user B is deleting may be on the same page?

 

What is the commit frequency of your delete process?

 

Bill Gallagher| Senior Systems Engineer, DBA

 

From: Debabrata <[login to unmask email]>
Sent: Monday, June 29, 2020 8:45 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - DB2 LUW Deadlock

 

|
***External Sender - Please Exercise Caution***
|


Hi All,

 

Please suggest.

If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and  21 to 40 million rows using user id B, will we face deadlock.

 

 

 

 
-----End Original Message-----This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Joe Geller

RE: [External] DB2 LUW Deadlock
(in response to Deb Kumar)

As Bill asked, are you issuing any commits?  How often? 

If you are not committing until the end, then you will have all kinds of problems.  If you are committing after each delete then you will be ok.  If it is somewhere in between, then maybe - give us the details.

Joe

In Reply to Deb Kumar:


Hi Bill,
Table is not partitioned.
User A is deleting record from row 1 to 20  million from table X and User B is deleting record from row 21 to 41 million from table X in the same time. I will face deadlock or not.
On Monday, June 29, 2020, 6:48:12 PM GMT+5:30, Gallagher, Bill R <[login to unmask email]> wrote:

#yiv8877403295 #yiv8877403295 -- _filtered {} _filtered {} _filtered {}#yiv8877403295 #yiv8877403295 p.yiv8877403295MsoNormal, #yiv8877403295 li.yiv8877403295MsoNormal, #yiv8877403295 div.yiv8877403295MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv8877403295 a:link, #yiv8877403295 span.yiv8877403295MsoHyperlink {color:blue;text-decoration:underline;}#yiv8877403295 a:visited, #yiv8877403295 span.yiv8877403295MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv8877403295 p.yiv8877403295msonormal0, #yiv8877403295 li.yiv8877403295msonormal0, #yiv8877403295 div.yiv8877403295msonormal0 {margin-right:0in;margin-left:0in;font-size:11.0pt;font-family:sans-serif;}#yiv8877403295 span.yiv8877403295EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv8877403295 .yiv8877403295MsoChpDefault {font-size:10.0pt;} _filtered {}#yiv8877403295 div.yiv8877403295WordSection1 {}#yiv8877403295
Are you deadlocking on the table you are deleting from, or on some other resource?

 

Is the table clustered or partitioned such that the rows that user A is deleting and the rows that user B is deleting may be on the same page?

 

What is the commit frequency of your delete process?

 

Bill Gallagher| Senior Systems Engineer, DBA

 

From: Debabrata <[login to unmask email]>
Sent: Monday, June 29, 2020 8:45 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - DB2 LUW Deadlock

 

|
***External Sender - Please Exercise Caution***
|


Hi All,

 

Please suggest.

If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and  21 to 40 million rows using user id B, will we face deadlock.

 

 

 

 
-----End Original Message-----This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Deb Kumar

[External] DB2 LUW Deadlock
(in response to Deb Kumar)
Hi

Thank you.

Not committing after updating each row, will commit after updating every 1000 rows.
will the cause deadlock.



On Monday, June 29, 2020, 7:05:43 PM GMT+5:30, Debabrata <[login to unmask email]> wrote:


Hi Bill,
Table is not partitioned.
User A is deleting record from row 1 to 20  million from table X and User B is deleting record from row 21 to 41 million from table X in the same time. I will face deadlock or not.
On Monday, June 29, 2020, 6:48:12 PM GMT+5:30, Gallagher, Bill R <[login to unmask email]> wrote:

#yiv6804310684 -- filtered {}#yiv6804310684 filtered {}#yiv6804310684 filtered {}#yiv6804310684 p.yiv6804310684MsoNormal, #yiv6804310684 li.yiv6804310684MsoNormal, #yiv6804310684 div.yiv6804310684MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv6804310684 a:link, #yiv6804310684 span.yiv6804310684MsoHyperlink {color:blue;text-decoration:underline;}#yiv6804310684 a:visited, #yiv6804310684 span.yiv6804310684MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv6804310684 p.yiv6804310684msonormal0, #yiv6804310684 li.yiv6804310684msonormal0, #yiv6804310684 div.yiv6804310684msonormal0 {margin-right:0in;margin-left:0in;font-size:11.0pt;font-family:sans-serif;}#yiv6804310684 span.yiv6804310684EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv6804310684 .yiv6804310684MsoChpDefault {font-size:10.0pt;}#yiv6804310684 filtered {}#yiv6804310684 div.yiv6804310684WordSection1 {}#yiv6804310684
Are you deadlocking on the table you are deleting from, or on some other resource?

 

Is the table clustered or partitioned such that the rows that user A is deleting and the rows that user B is deleting may be on the same page?

 

What is the commit frequency of your delete process?

 

Bill Gallagher| Senior Systems Engineer, DBA

 

From: Debabrata <[login to unmask email]>
Sent: Monday, June 29, 2020 8:45 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - DB2 LUW Deadlock

 

|
***External Sender - Please Exercise Caution***
|


Hi All,

 

Please suggest.

If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and  21 to 40 million rows using user id B, will we face deadlock.

 

 

 

 
-----End Original Message-----This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Bill Gallagher

[External] DB2 LUW Deadlock
(in response to Deb Kumar)
The more updates you do between commits, the higher the likelihood of deadlocks unless you are taking care of splitting your delete processes against physically separate pages of data.

Is there a clustering index defined on your table? Are your delete processes taking the clustered index into account when they are deciding which rows each will delete?

Bill Gallagher | Senior Systems Engineer, DBA

From: Debabrata <[login to unmask email]>
Sent: Monday, June 29, 2020 11:08 AM
To: Debabrata <[login to unmask email]>
Subject: [DB2-L] - RE: [External] DB2 LUW Deadlock

***External Sender - Please Exercise Caution***

Hi

Thank you.

Not committing after updating each row, will commit after updating every 1000 rows.

will the cause deadlock.



On Monday, June 29, 2020, 7:05:43 PM GMT+5:30, Debabrata <[login to unmask email]<mailto:[login to unmask email]>> wrote:



Hi Bill,

Table is not partitioned.

User A is deleting record from row 1 to 20 million from table X and User B is deleting record from row 21 to 41 million from table X in the same time. I will face deadlock or not.
On Monday, June 29, 2020, 6:48:12 PM GMT+5:30, Gallagher, Bill R <[login to unmask email]<mailto:[login to unmask email]>> wrote:



Are you deadlocking on the table you are deleting from, or on some other resource?



Is the table clustered or partitioned such that the rows that user A is deleting and the rows that user B is deleting may be on the same page?



What is the commit frequency of your delete process?



Bill Gallagher | Senior Systems Engineer, DBA



From: Debabrata <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Monday, June 29, 2020 8:45 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [External] [DB2-L] - DB2 LUW Deadlock



***External Sender - Please Exercise Caution***


Hi All,



Please suggest.

If we are deleting records, at the same time from same table from 1 to 20 million rows using user id A and 21 to 40 million rows using user id B, will we face deadlock.








-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

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