Transactions deadlocking on inserts

Andrew (FR1) Kirkland

Transactions deadlocking on inserts
We are experiencing the following problem and would appreciate any
assistance that the list can give, as we are at a loss as to a solution :

Two occurrences of the same transaction are deadlocking with each other
when they try to Insert into the same physical page. The contention is
between a physical data page of a Partition and variably the
Header (Page 000), Space Map (Page 001) or 1st page of a Compression
Dictionary (Page 002) of the same Partition.

The transaction is initiated by ALLOCATE, CONVERSE commands from a
different CICS region. On return from the DB2 transaction the initiating
CICS region issues a FREE command and the DB2 transaction gets an
implicit commit.

The Partitioned Tablespace uses DB2 compression, has a Locksize of Page,
is a 4K page Tablespace and will start the online day at 20% of data
volume. This volume will build up throughout the online day. The
table is cleared down to 20% overnight after which it is Reorged to
re-apply free space. It was first felt that every nth Insert was
suffering as a result of additional page formatting, so we changed the
reorg to use PREFORMAT; however, we are still getting the deadlocks.

The DB2 package is bound Release(Commit) and we are getting approx.
20 trans/s at the moment but need to grow to between 100-150/s for peak
hours.

The application is in a sub-system which is in a 3-way data sharing
group but all transactions are local.

We are a bit perplexed as to why we get deadlocks on an Insert
transaction? Any advice gratefully received.

Regards,

Robin Houliston
Production DBA
Royal Bank of Scotland
Edinburgh






The Royal Bank of Scotland plc is registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.

The Royal Bank of Scotland plc is regulated by IMRO, SFA and Personal Investment Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer.

'Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent.'



Tim Lowe

Re: Transactions deadlocking on inserts
(in response to Andrew (FR1) Kirkland)
Andrew,
What DB2 release are you on?
If V5, are you using type-2 indexes?
Are you using DB2 data sharing?
Have you considered using MEMBER CLUSTER on the tablespace? (V5 Apar
PQ02897)
If you are not using incremental copy, have you considered altering the
tablespace to TRACKMOD NO?

I hope this helps.

Thanks,
Tim



"Kirkland,
Andrew (FR1)" To: [login to unmask email]
<[login to unmask email] cc:
.CO.UK> Subject: Transactions deadlocking on inserts
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


12/14/2000
09:01 AM
Please
respond to
DB2 Data Base
Discussion
List






We are experiencing the following problem and would appreciate any
assistance that the list can give, as we are at a loss as to a solution :

Two occurrences of the same transaction are deadlocking with each other
when they try to Insert into the same physical page. The contention is
between a physical data page of a Partition and variably the
Header (Page 000), Space Map (Page 001) or 1st page of a Compression
Dictionary (Page 002) of the same Partition.

The transaction is initiated by ALLOCATE, CONVERSE commands from a
different CICS region. On return from the DB2 transaction the initiating
CICS region issues a FREE command and the DB2 transaction gets an
implicit commit.

The Partitioned Tablespace uses DB2 compression, has a Locksize of Page,
is a 4K page Tablespace and will start the online day at 20% of data
volume. This volume will build up throughout the online day. The
table is cleared down to 20% overnight after which it is Reorged to
re-apply free space. It was first felt that every nth Insert was
suffering as a result of additional page formatting, so we changed the
reorg to use PREFORMAT; however, we are still getting the deadlocks.

The DB2 package is bound Release(Commit) and we are getting approx.
20 trans/s at the moment but need to grow to between 100-150/s for peak
hours.

The application is in a sub-system which is in a 3-way data sharing
group but all transactions are local.

We are a bit perplexed as to why we get deadlocks on an Insert
transaction? Any advice gratefully received.

Regards,

Robin Houliston
Production DBA
Royal Bank of Scotland
Edinburgh






The Royal Bank of Scotland plc is registered in Scotland No 90312.
Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.

The Royal Bank of Scotland plc is regulated by IMRO, SFA and Personal
Investment Authority.

This e-mail message is confidential and for use by the addressee only. If
the message is received by anyone other than the addressee, please return
the message to the sender by replying to it and then delete the message
from your computer.

'Internet e-mails are not necessarily secure. The Royal Bank of Scotland
plc does not accept responsibility for changes made to this message after
it was sent.'








Andrew (FR1) Kirkland

Re: Transactions deadlocking on inserts
(in response to Tim Lowe)
> Thanks Tim,
>
> We are V5 and entirely use Type 2 Indexes. Although the Table in question
> is participating in a 3-way data sharing group, all transactions to this
> table are local.
>
> We do have PQ02897 but would prefer not to have to resort to non-clustered
> driven inserts (to better support eventual enquiries on the same table).
>
> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 14, 2000 3:41 PM
> To: [login to unmask email]
> Subject: Re: Transactions deadlocking on inserts
>
>
> *** Warning : This message originates from the Internet ***
>
> Andrew,
> What DB2 release are you on?
> If V5, are you using type-2 indexes?
> Are you using DB2 data sharing?
> Have you considered using MEMBER CLUSTER on the tablespace? (V5
> Apar
> PQ02897)
> If you are not using incremental copy, have you considered altering
> the
> tablespace to TRACKMOD NO?
>
> I hope this helps.
>
> Thanks,
> Tim
>
>
>
> "Kirkland,
> Andrew (FR1)" To: [login to unmask email]
> <[login to unmask email] cc:
> .CO.UK> Subject: Transactions
> deadlocking on inserts
> Sent by: DB2
> Data Base
> Discussion
> List
> <[login to unmask email]
> OM>
>
>
> 12/14/2000
> 09:01 AM
> Please
> respond to
> DB2 Data Base
> Discussion
> List
>
>
>
>
>
>
> We are experiencing the following problem and would appreciate any
> assistance that the list can give, as we are at a loss as to a
> solution :
>
> Two occurrences of the same transaction are deadlocking with each
> other
> when they try to Insert into the same physical page. The contention
> is
> between a physical data page of a Partition and variably the
> Header (Page 000), Space Map (Page 001) or 1st page of a Compression
> Dictionary (Page 002) of the same Partition.
>
> The transaction is initiated by ALLOCATE, CONVERSE commands from a
> different CICS region. On return from the DB2 transaction the
> initiating
> CICS region issues a FREE command and the DB2 transaction gets an
> implicit commit.
>
> The Partitioned Tablespace uses DB2 compression, has a Locksize of
> Page,
> is a 4K page Tablespace and will start the online day at 20% of data
> volume. This volume will build up throughout the online day. The
> table is cleared down to 20% overnight after which it is Reorged to
> re-apply free space. It was first felt that every nth Insert was
> suffering as a result of additional page formatting, so we changed
> the
> reorg to use PREFORMAT; however, we are still getting the deadlocks.
>
> The DB2 package is bound Release(Commit) and we are getting approx.
> 20 trans/s at the moment but need to grow to between 100-150/s for
> peak
> hours.
>
> The application is in a sub-system which is in a 3-way data sharing
> group but all transactions are local.
>
> We are a bit perplexed as to why we get deadlocks on an Insert
> transaction? Any advice gratefully received.
>
> Regards,
>
> Robin Houliston
> Production DBA
> Royal Bank of Scotland
> Edinburgh
>
>
>
>
>
>
> The Royal Bank of Scotland plc is registered in Scotland No 90312.
> Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
>
> The Royal Bank of Scotland plc is regulated by IMRO, SFA and
> Personal
> Investment Authority.
>
> This e-mail message is confidential and for use by the addressee
> only. If
> the message is received by anyone other than the addressee, please
> return
> the message to the sender by replying to it and then delete the
> message
> from your computer.
>
> 'Internet e-mails are not necessarily secure. The Royal Bank of
> Scotland
> plc does not accept responsibility for changes made to this message
> after
> it was sent.'
>
>
>
> visit
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
> list can
>
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
> list can


The Royal Bank of Scotland plc is registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.

The Royal Bank of Scotland plc is regulated by IMRO, SFA and Personal Investment Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer.

'Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent.'



Tim Lowe

Re: Transactions deadlocking on inserts
(in response to Andrew (FR1) Kirkland)
Andrew,
I have not seen deadlocks on insert-only transactions using type-2 indexes.
Are you sure that these are only inserts, and there are no deletes or
updates?

If all accesses to this table are froma single system in your sysplex, then
can I assume that this tablespace is not group-bufferpool dependent? (and
therefore ALTER tablespace ....TRACKMOD NO would not help?)

Thanks,
Tim



Marcel Harleman

Re: Transactions deadlocking on inserts
(in response to Tim Lowe)
Robin,

what's the exact message of the deadlock?

Some time ago a deadlock was mentioned by somebody where it all came
down to contention on the highest row-id (recorded if I'm not mistaken
in the header page). I can imagine something similar happening to a
space map (highest page number perhaps) but I fail to see for the
moment how a compression page might be affected (maybe somebody else
has an idea on this). The remedy used then I believe was a random
insert instead of a clustered one.

I'd like to have a look to the effect of preformatting on the
registration of the highest row-id in the header page or something
similar in the space map page, but in the mean time you might decide
for yourself if this might be a good hint or not.

Regards,

Marcel.

>We are experiencing the following problem and would appreciate any
>assistance that the list can give, as we are at a loss as to a solution :
>
>Two occurrences of the same transaction are deadlocking with each other
>when they try to Insert into the same physical page. The contention is
>between a physical data page of a Partition and variably the
>Header (Page 000), Space Map (Page 001) or 1st page of a Compression
>Dictionary (Page 002) of the same Partition.
>
>The transaction is initiated by ALLOCATE, CONVERSE commands from a
>different CICS region. On return from the DB2 transaction the initiating
>CICS region issues a FREE command and the DB2 transaction gets an
>implicit commit.
>
>The Partitioned Tablespace uses DB2 compression, has a Locksize of Page,
>is a 4K page Tablespace and will start the online day at 20% of data
>volume. This volume will build up throughout the online day. The
>table is cleared down to 20% overnight after which it is Reorged to
>re-apply free space. It was first felt that every nth Insert was
>suffering as a result of additional page formatting, so we changed the
>reorg to use PREFORMAT; however, we are still getting the deadlocks.
>
>The DB2 package is bound Release(Commit) and we are getting approx.
>20 trans/s at the moment but need to grow to between 100-150/s for peak
>hours.
>
>The application is in a sub-system which is in a 3-way data sharing
>group but all transactions are local.
>
>We are a bit perplexed as to why we get deadlocks on an Insert
>transaction? Any advice gratefully received.
>
>Regards,
>
>Robin Houliston
>Production DBA
>Royal Bank of Scotland
>Edinburgh
>
>
>
>
>
>
>The Royal Bank of Scotland plc is registered in Scotland No 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB.
>
>The Royal Bank of Scotland plc is regulated by IMRO, SFA and Personal Investment Authority.
>
>This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer.
>
>'Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent.'
>
>
>



Terry Purcell

Re: Transactions deadlocking on inserts
(in response to Marcel Harleman)
Tim & Andrew,

It is possible for an insert to timeout with a type-2 index. I've attached
an extract from a DB2 listserv email from Chris Munson (IBM Santa Teresa) in
August 1999. Sorry for the delay in replying but I had to retrieve the email
from my outlook personal folder in another country, as it is no longer in
the DB2-L archives.

> Finally the lock timeout thing. I spoke to the code owner (again) and it
> appears that my prior append was slightly incorrect. I was told by him
> that the
> search for the page is done first and that the locking done for inserting
> the
> row is conditional (where by if it fails it looks for another page instead
> of
> waiting) so you shouldn't get a lock timeout on the insert. It is true
> that the
> lock is conditional but before doing this first conditional lock data
> manager
> asks index manager for a 'candidate' page to put the row (for cluster
> purposes).
> If index manager returns a candidate page data manager does the
> conditional lock
> and if it is locked it will search for the best place to put it on its own
> and
> will continue with conditional locking as mentioned before. In the
> example
> where program 1 already inserted the value but doesn't commit, Index
> manager
> when it looks for the candidate page (based on data managers first
> request), it
> sees that the key is in the index but is not sure if the row is committed
> and
> therefore gets a lock on the data page (without data manager knowing) so
> it can
> look at it to see its commit status. This is where the timeout happens.
> So the
> data manager guy didn't lie to me, he didn't know that index manager did
> the
> lock when trying to find the candidate page. Actually this is desirable
> so we
> don't spend time trying to find a place to put the row only to find out
> that it
> is a dup. As far as being denied immediately because of a dup instead of
> waiting, the problem is that if this is done and then the first agent who
> did
> the insert did a rollback then the person who was given the -803 may have
> been
> given bad info (thus they wait to see if the first insert commits or does
> a
> rollback). In either case they get the correct answer of a timeout for
> waiting
> too long for the first one to make up its mind, a dup if it does make up
> its
> mind by committing or a successful insert if the first one did a rollback.
> Sorry for the confusion.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
[login to unmask email]
Sent: Thursday, December 14, 2000 11:39 AM
To: [login to unmask email]
Subject: Re: Transactions deadlocking on inserts


Andrew,
I have not seen deadlocks on insert-only transactions using type-2 indexes.
Are you sure that these are only inserts, and there are no deletes or
updates?

If all accesses to this table are froma single system in your sysplex, then
can I assume that this tablespace is not group-bufferpool dependent? (and
therefore ALTER tablespace ....TRACKMOD NO would not help?)

Thanks,
Tim








Tim Lowe

Re: Transactions deadlocking on inserts
(in response to Terry Purcell)
Terry,
I am sorry, you are absolutely correct. I had totally forgotten about that
thread. (and I remember contributing to it!)
I had concentrated too much on deadlocks, and had not considered timeouts
on duplicate key inserts.
But, I can certainly see how this could also create a deadlock.

Andrew,
Could your problem be caused by duplicate keys?

Thanks!,
Tim