Hi Barry,
The locking depends on the Isolation level you are using.
Derive a explain-plan on the involved SQL-statements.
You will see the isolation levels involved with those SQLs.
Of course if you are setting the isolation in the application,
then you need to review the code.
However, if your application is using the isolation level RR,
then every row that is read in a transaction gets locked.

My thought is : since it has to go through all the rows,
row-lock on even one row makes it not to go forward. Due to
this your select is failing. [somebody correct me if I am
wrong].

You can do the select in different ways.
SELECT * from metricsdata order by timekey order by timekey
desc FETCH FIRST 1 ROWS ONLY with UR;
or
SELECT * from metricsdata where timekey =(
select max(timekey) from metricsdata) with UR;

The other solution would be to set the environment variable
DB2_RR_TO_RS=YES

Hope this helps.
Partha


-----Original Message-----
From: Barry_R_Pieper
[mailto:db2udbdba-ezmlmshield-x36526313.[Email address protected]
Sent: Thursday, January 27, 2005 3:01 PM
To: LazyDBA Discussion
Subject: Deadlock de-bugging help please...



On my DB2/UDB Database server (= DB2/SUN 7.2.8) I am getting SQL0911N
errors on most of my dynamic SQL (entered from the CLP), and I have some
basic questions , any suggestions, hints, or pointers to where to find
basic diagnostic assistance appreciated!

Here is one example:

SELECT * from metricsdata order by timekey desc FETCH FIRST 1 ROWS ONLY

SQL0911N The current transaction has been rolled back because of a
deadlock
or timeout. Reason code "2". SQLSTATE=40001

I have a snapshot of applications and locks and I see two applications
connected. One doing an update to a large table (43 million rows) the
other the dynamic query trying to list the last row in the table.

Questions:

1. First is this the best/only way to display the last row?
2. Why would this simple select get deadlocked? Are some options wrong
(is there a READONLY specification that would be better?
3. I see 1,147 locks - 1,144 held by the updater, 3 by the query. Why
would the query lock the table? Is this the problem?

List Of Locks
Lock Object Name = 8
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = TITM_DB
Table Name = METRICSDATA
Mode = IS
Status = Granted
Lock Escalation = NO

Thanks for the help!


The information contained in this communication may be confidential,
and is intended only for the use of the recipient(s) named above.
If the reader of this message is not the intended recipient, you
are hereby notified that any dissemination, distribution, or
copying of this communication, or any of its contents, is strictly
prohibited. If you have received this communication in error,
please return it to the sender immediately and delete the original
message and any copy of it from your computer system. If you have
any questions concerning this message, please contact the sender.

Unencrypted, unauthenticated Internet e-mail is inherently insecure.
Internet messages may be corrupted or incomplete, or may incorrectly
identify the sender.


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page