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
Jim YoumansSr Database AdministratorAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.