Put 'WITH UR' on the end of your select statement. This will get rid of the
query locks and thus prevent the lock timeout. There are probably better
ways of getting the last row but we would need to have the table layout and
indexes before we could answer that. Even a query takes a readonly lock to
ensure database integrity.
|---------+--------------------------------------------------------->
| | "Barry_R_Pieper" |
| | <db2udbdba-ezmlmshield-x36526313.[Email Address Removed] | azyDBA.com> |
| | |
| | 28/01/2005 10:00 |
| | |
|---------+--------------------------------------------------------->
>----------------------------------------------------------------------------------------------|
| |
| To: "LazyDBA Discussion" <[Email address protected] |
| cc: |
| 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