Unlocking DB2 table/row locks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

boris.litvak@gmail.com - 26 May 2005 17:19 GMT

We are looking for a command/SQL statement that cancels all table/row
locks in DB2.
The reason for this is:
1) computer A was a sole client of DB2, and crashed
2) computer B automatically (in our scenario) becomes the new and only
DB2 client, and executes the same application as A.
3) at this time, DB2 still holds locks from transactions started on A,
untill certain timeout expires, AFAIK. We would like to cancel this
timeout time.

How this timeout can be configured?
How can we cancel existing DB2 locks in (or before) stage 2, i.e.
without relying on timeout.

Your help is greatly appreciated.

Boris

Reply to this Message

Mark A - 26 May 2005 17:52 GMT

> We are looking for a command/SQL statement that cancels all table/row
> locks in DB2.
[quoted text clipped - 13 lines]
>
> Boris

Have the application holding the locks issue an SQL commit statement.

Reply to this Message

boris.litvak@gmail.com - 26 May 2005 20:53 GMT

The application instance that held the locks ran on computer A, which
is down now - we cannot commit from it.
Another instance of the same application is now running on B, but I am
not sure it can commit the logs, as it did not start the transactions.
Am I wrong?

Reply to this Message

Mark A - 26 May 2005 23:35 GMT

> The application instance that held the locks ran on computer A, which
> is down now - we cannot commit from it.
> Another instance of the same application is now running on B, but I am
> not sure it can commit the logs, as it did not start the transactions.
> Am I wrong?

You are correct. I did not realize that application A crashed (didn't read
carefully).

But if the application crashed, it should have issued a rollback, which
would release locks. If it is hung, I don't know how to set a timeout
parameter for the connection that will release the locks (maybe someone else
does).

As mentioned by others, you can force the application manually, but I don't
know if that is what you are looking for an on-going solution. Best solution
may be to find out why the application is hanging and fix the problem.

Reply to this Message

boris.litvak@gmail.com - 27 May 2005 09:04 GMT

We are dealing with high availability, thus computer A could have been
struct by lightning, fwiw. Meaning, we do not rely on the application
at all.

Mark A, hik..., I would like to thank you both for the fruitful
conversation, hope it will work :)
I shall try what hik... wrote and shall report the results after the
weekend.

Reply to this Message

boris.litvak@gmail.com - 29 May 2005 15:14 GMT

It worked!
The only question now left in mind - the force application command in
asynchronous.
According to your experience (as you already understand, I am not a
programmer/designer, not a db admin):

How many seconds/milliseconds elapse between the return of 'force
application' command, and the actual applications shutdown? Any
estimate will do.

If the command fails, what can we do other than db2stop?

Reply to this Message

Ian - 30 May 2005 06:10 GMT

> It worked!
> The only question now left in mind - the force application command in
[quoted text clipped - 5 lines]
> application' command, and the actual applications shutdown? Any
> estimate will do.

This depends entirely on what the forced application was doing: The
force will cause the current transaction to be rolled back, so if the
app was in the middle of a big unit of work, it could take a long while.

For small units of work, the force is nearly always immediate.

Reply to this Message

boris.litvak@gmail.com - 29 May 2005 15:24 GMT

Moreover, is it possible to set some timeout values for the locks? Do
they never expire?

Reply to this Message

boris.litvak@gmail.com - 29 May 2005 15:43 GMT

LOCKTIMEOUT :) (update db cfg for ...)

Reply to this Message

Mark A - 29 May 2005 19:20 GMT

> LOCKTIMEOUT :) (update db cfg for ...)

That determines how long an application waiting for lock to be released
(held by another application) before it timeouts with a -911. If the value
is -1 it will wait forever.

Reply to this Message