CODEWITHSUNDEEP
db2lockingwaitdb2-luw
Dai Vu
Dai Vu

Reputation: 11

how to lock table in DB2 without waiting

I want to lock a table in DB2 (version 11) and I don't want other people wait for my locking when try to update the locked table. (Thrown an exception or return an error code)

I used the following command: LOCK TABLE MYTABLE IN EXCLUSIVE MODE

But other connections always wait.

So is there any way to stop waiting?

Upvotes: 0

Views: 6650

Answers (2)

mao
mao

Reputation: 12267

For Db2 on Linux/Unix/Windows, If you use 'LOCK TABLE ... IN EXCLUSIVE MODE' then other connections will wait for you to commit/rollback.

For Db2 on Linux/Unix/Windows, by default the other connections will wait for either LOCKTIMEOUT seconds (default = -1 = wait forever ), or whatever the other application has configured in its connection either via SET CURRENT LOCK TIMEOUT (which can include setting NOT WAIT or other values) or in the other application's Db2-client configuration. See documentation here.

For client side configuration options (db2cli.ini, db2dsdriver.cfg, connection-attributes etc) refer to the Db2 Knowledge center for details.

It's important to understand that either it is the other applications that need to adjust their lock timeout (either in their code or in the Db2-client configuration) , or else the database parameter LOCKTIMEOUT will determine their waiting duration.

So , in short, if you want other applications to not wait then those applications have to be programmed/configured for that purpose, or the database configuration LOCKTIMEOUT has to be configured appropriately or both of those.

In practice this means you need to either avoid locking tables in exclusive mode (implicitly or explitly), or you need to perform this at the most quiet time and keep the duration of the lock as short as possible. In particular, your job cannot tell the other Db2-connections how to adjust their lockwait behaviour!

Upvotes: 2

jackic23
jackic23

Reputation: 139

Maybe the other users could use Uncommitted Reads WITH UR?

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0059225.html

Alternatively, maybe you could use IN SHARE MODE instead of an EXCLUSIVE lock

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000972.html

Upvotes: -1

Related Questions