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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.