By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

Deadlock on a Prepare?

P: n/a
Hi-

We ran into some very strange deadlocks this AM, and I was hoping to
get some insight. We were running a REORGCHK on a database, and
started getting deadlocks. What is curious is that according to the
deadlock event monitor, the deadlock was between the REORGCHK process
and another client attempting a SQL execution, and the Prepare was
requesting an Exclusive lock. Is that normal? Maybe I'm reading the
monitor output incorrectly, so I'm attaching it here for comment.

Type of lock: Internal - Catalog Cache
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 0
Application Handle: 49
Deadlocked Statement:
Type : Dynamic
Operation: Prepare
Section : 4
Creator : NULLID
Package : SYSSH200
Cursor : SQL_CURSH200C4
Cursor was blocking: FALSE

Thanks,
Todd McNeill
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I don't like to use event monitor. I used to capture lock snapshot to do the
analyze. Are you sure you post all the output?
"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
Hi-

We ran into some very strange deadlocks this AM, and I was hoping to
get some insight. We were running a REORGCHK on a database, and
started getting deadlocks. What is curious is that according to the
deadlock event monitor, the deadlock was between the REORGCHK process
and another client attempting a SQL execution, and the Prepare was
requesting an Exclusive lock. Is that normal? Maybe I'm reading the
monitor output incorrectly, so I'm attaching it here for comment.

Type of lock: Internal - Catalog Cache
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 0
Application Handle: 49
Deadlocked Statement:
Type : Dynamic
Operation: Prepare
Section : 4
Creator : NULLID
Package : SYSSH200
Cursor : SQL_CURSH200C4
Cursor was blocking: FALSE

Thanks,
Todd McNeill

Nov 12 '05 #2

P: n/a
Of course it may occur.

DB2 (I am talking here about version 7, because I do not have time to
independently research version 8, yet) allocates 9216 predefined plans
for dynamic cursors, just for CLI/ODBC purpose (CLP, REXX, etc.
cursors used to be extra).

cursor_hold_num = 2
isolation_num = 4
stmt_group_num = 3
stmt_num = 384

That allowed me to develop DB2 SQL API and code almost any DB2 SQL
application without precompiling and bindding. I am sure that DB2
vendor will never advise it, but it works for me because I have my own
DB2 dynamic cursors scheduler. The idea is simple, if we have already
9216 plans in each database, why would we ever need or create (pre
compile and bind) new ones.

How DB2 dispatch and control them it was always my most preferred DB2
obsession. I would offer all my Halloween candies for that
explanation.

I am very interested in that so if somebody has any knowledge about
DB2 dynamic cursors scheduling please send me by email
(db****@sympatico.ca).
Nov 12 '05 #3

P: n/a
The output was far too much to post here, it included all of the lock
information as well. I was mainly attempting to point out that this
application was attempting a Prepare and requesting an exclusive lock.
I was wondering if that was expected behavior.

Todd
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<G3*****************@newssvr17.news.prodigy.c om>...
I don't like to use event monitor. I used to capture lock snapshot to do the
analyze. Are you sure you post all the output?
"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
Hi-

We ran into some very strange deadlocks this AM, and I was hoping to
get some insight. We were running a REORGCHK on a database, and
started getting deadlocks. What is curious is that according to the
deadlock event monitor, the deadlock was between the REORGCHK process
and another client attempting a SQL execution, and the Prepare was
requesting an Exclusive lock. Is that normal? Maybe I'm reading the
monitor output incorrectly, so I'm attaching it here for comment.

Type of lock: Internal - Catalog Cache
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 0
Application Handle: 49
Deadlocked Statement:
Type : Dynamic
Operation: Prepare
Section : 4
Creator : NULLID
Package : SYSSH200
Cursor : SQL_CURSH200C4
Cursor was blocking: FALSE

Thanks,
Todd McNeill

Nov 12 '05 #4

P: n/a
Did you get 911 (I forgot the reason code is 68 or 2)???
DB2 UDB will use the internal lock to lock the package to prevent it from
dropping during the SQL compiling phase. No any user's objects should be
locked during this phase.
And there is a bug (I thought it is, but this is not a big deal) for DB2 UDB
V8.1. I got "A deadlock has occurred ..." in my db2diag.log. And the funny
thing is the component name is "access plan manager". I was told by IBM, the
information "A deadlock ..." is not a correct information. The correct
information should be "The Database System Monitor output buffer is full".
So go back to my original question - Have you got 911?

"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
The output was far too much to post here, it included all of the lock
information as well. I was mainly attempting to point out that this
application was attempting a Prepare and requesting an exclusive lock.
I was wondering if that was expected behavior.

Todd
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<G3*****************@newssvr17.news.prodigy.c om>...
I don't like to use event monitor. I used to capture lock snapshot to do the analyze. Are you sure you post all the output?
"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
Hi-

We ran into some very strange deadlocks this AM, and I was hoping to
get some insight. We were running a REORGCHK on a database, and
started getting deadlocks. What is curious is that according to the
deadlock event monitor, the deadlock was between the REORGCHK process
and another client attempting a SQL execution, and the Prepare was
requesting an Exclusive lock. Is that normal? Maybe I'm reading the
monitor output incorrectly, so I'm attaching it here for comment.

Type of lock: Internal - Catalog Cache
Mode of lock: X - Exclusive
Mode application requested on lock: X - Exclusive
Node lock occured on: 0
Lock object name: 0
Application Handle: 49
Deadlocked Statement:
Type : Dynamic
Operation: Prepare
Section : 4
Creator : NULLID
Package : SYSSH200
Cursor : SQL_CURSH200C4
Cursor was blocking: FALSE

Thanks,
Todd McNeill

Nov 12 '05 #5

P: n/a
Yes, it was a -911, reason code "2" (deadlock). The event did not
appear in the diag.log, but did appear in the deadlock event monitor,
the output of which I posted earlier.

Some more information that I forgot to post was: v8.1 FP6 Workgroup
Server on Solaris.

Thanks,
Todd

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<U5*****************@newssvr17.news.prodigy.c om>...
Did you get 911 (I forgot the reason code is 68 or 2)???
DB2 UDB will use the internal lock to lock the package to prevent it from
dropping during the SQL compiling phase. No any user's objects should be
locked during this phase.
And there is a bug (I thought it is, but this is not a big deal) for DB2 UDB
V8.1. I got "A deadlock has occurred ..." in my db2diag.log. And the funny
thing is the component name is "access plan manager". I was told by IBM, the
information "A deadlock ..." is not a correct information. The correct
information should be "The Database System Monitor output buffer is full".
So go back to my original question - Have you got 911?

"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
The output was far too much to post here, it included all of the lock
information as well. I was mainly attempting to point out that this
application was attempting a Prepare and requesting an exclusive lock.
I was wondering if that was expected behavior.

Todd
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<G3*****************@newssvr17.news.prodigy.c om>...
I don't like to use event monitor. I used to capture lock snapshot to do the analyze. Are you sure you post all the output?
"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
> Hi-
>
> We ran into some very strange deadlocks this AM, and I was hoping to
> get some insight. We were running a REORGCHK on a database, and
> started getting deadlocks. What is curious is that according to the
> deadlock event monitor, the deadlock was between the REORGCHK process
> and another client attempting a SQL execution, and the Prepare was
> requesting an Exclusive lock. Is that normal? Maybe I'm reading the
> monitor output incorrectly, so I'm attaching it here for comment.
>
> Type of lock: Internal - Catalog Cache
> Mode of lock: X - Exclusive
> Mode application requested on lock: X - Exclusive
> Node lock occured on: 0
> Lock object name: 0
> Application Handle: 49
> Deadlocked Statement:
> Type : Dynamic
> Operation: Prepare
> Section : 4
> Creator : NULLID
> Package : SYSSH200
> Cursor : SQL_CURSH200C4
> Cursor was blocking: FALSE
>
> Thanks,
> Todd McNeill

Nov 12 '05 #6

P: n/a
The internal lock will not cause 911 sql error code. You need to find out
which user's object (table or row) is locked and block the other
application. Especially you need to check if the escalation happened
earlier.
For how to understand the event monitor output you posted in this newsgroup,
you can ask IBM support for the explanation.

"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c*************************@posting.google.co m...
Yes, it was a -911, reason code "2" (deadlock). The event did not
appear in the diag.log, but did appear in the deadlock event monitor,
the output of which I posted earlier.

Some more information that I forgot to post was: v8.1 FP6 Workgroup
Server on Solaris.

Thanks,
Todd

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<U5*****************@newssvr17.news.prodigy.c om>...
Did you get 911 (I forgot the reason code is 68 or 2)???
DB2 UDB will use the internal lock to lock the package to prevent it from dropping during the SQL compiling phase. No any user's objects should be
locked during this phase.
And there is a bug (I thought it is, but this is not a big deal) for DB2 UDB V8.1. I got "A deadlock has occurred ..." in my db2diag.log. And the funny thing is the component name is "access plan manager". I was told by IBM, the information "A deadlock ..." is not a correct information. The correct
information should be "The Database System Monitor output buffer is full". So go back to my original question - Have you got 911?

"Todd McNeill" <to********@yahoo.com> wrote in message
news:9c**************************@posting.google.c om...
The output was far too much to post here, it included all of the lock
information as well. I was mainly attempting to point out that this
application was attempting a Prepare and requesting an exclusive lock.
I was wondering if that was expected behavior.

Todd
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<G3*****************@newssvr17.news.prodigy.c om>...
> I don't like to use event monitor. I used to capture lock snapshot to do
the
> analyze. Are you sure you post all the output?
>
>
> "Todd McNeill" <to********@yahoo.com> wrote in message
> news:9c**************************@posting.google.c om...
> > Hi-
> >
> > We ran into some very strange deadlocks this AM, and I was hoping

to > > get some insight. We were running a REORGCHK on a database, and
> > started getting deadlocks. What is curious is that according to the > > deadlock event monitor, the deadlock was between the REORGCHK process > > and another client attempting a SQL execution, and the Prepare was
> > requesting an Exclusive lock. Is that normal? Maybe I'm reading the > > monitor output incorrectly, so I'm attaching it here for comment.
> >
> > Type of lock: Internal - Catalog Cache
> > Mode of lock: X - Exclusive
> > Mode application requested on lock: X - Exclusive
> > Node lock occured on: 0
> > Lock object name: 0
> > Application Handle: 49
> > Deadlocked Statement:
> > Type : Dynamic
> > Operation: Prepare
> > Section : 4
> > Creator : NULLID
> > Package : SYSSH200
> > Cursor : SQL_CURSH200C4
> > Cursor was blocking: FALSE
> >
> > Thanks,
> > Todd McNeill

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.