• Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 20
  • Last Modified:

DB2 problem with lock timeouts

DB2 10.5 on Windows

I am having issues with timeouts in my dev system.  The lock timeout parameter is set to 55 seconds.  The way I understand it is that if the operation cannot acquire the lock it is trying for within 55 seconds it will error out with the message...

ERROR [40001] [IBM][DB2/NT64] SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".

The RC 68 is "The transaction was rolled back due to a lock timeout."

I have this happen when I am trying to create an index or an FK constant, which I can understand.  It can also happen when I do

"SELECT * FROM TABLE WHERE ID = 5 WITH UR;"

So my question is two-fold.

1. How can I see what is preventing me from acquiring the lock?
2. How can if fix this to make it stop happening?

I have tried to use MON_GET_APPL_LOCKWAIT to figure it out but not sure what is being returned.

It is very frustrating.  Any suggestions?

Thanks!

Jim
0
Jim Youmans
Asked:
Jim Youmans
1 Comment
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Jim,

Getting into the weeds a bit, there are several kinds of locks, all of which have the same general effect of locking other tasks from some or all of the data in a table.  The simplest is a row lock where the database will lock a single row in a page for the duration of the transaction.  The next level lock is a page lock.  All items in the page are locked with a single lock.  If enough rows are locked on a page (by a single process) DB2 may escalate the lock to be a page lock and manage only 1 lock instead of a lock for each row.

The point of that is that these types of locks typically have no real impact on other user queries as long as the tasks don't need access to the same data.  But depending on changes, the locks can have a huge impact on system processes.  If the data change is to a column that has an index, the index must also be updated.  An area code change to a phone number from 205-123-4567 to 904-123-4567 effectively requires two changes to the index.   '205-123-4567' must be deleted from the index and '954-123-4567' must be added.  The deletion will happen from the low numbers of the index and that addition will happen to the high numbers.  During the time the change is being made, significant portions of the index may be locked, especially if the change requires splitting or merging blocks.

Building an index on a table often requires that no changes to the table during the build process.  There are exceptions (in some cases the engine can build the index on the table and catch up with any changes by reading them from the log file) but it's easiest to think of this as "DB2 needs exclusive write access to the entire table".

Earlier, I steered you to a function to see the locks.  You can query the tables directly, but it's more involved.

This will show you who is holding locks and how many.

SELECT TABSCHEMA, TABNAME, COUNT(*) AS NUMBER_OF_LOCKS_HELD
FROM SYSIBMADM.LOCKS_HELD
WHERE DB_NAME = 'my_db'
GROUP BY DBPARTITIONNUM, TABSCHEMA, TABNAME


Use LIST APPLICATION SHOW DETAIL to see the running tasks and get the ID or use other queries of "LOCKS_HELD".   This query will then show you what the user/task has locked.

SELECT TABSCHEMA, TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_ESCALATION
FROM SYSIBMADM.LOCKS_HELD
WHERE AGENT_ID = xxx


You can then decided whether to kill tasks or let them continue.

A simpler approach may be to force a lock on the table and then run the CREATE INDEX statement.

LOCK TABLE xxx;

Be sure to follow it up with a COMMIT or ROLLBACK after the CREATE INDEX statement completes.


Whew....  Didn't mean to be so wordy....
1

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now