Explicit Locks in DB2

ramp_senti · New User Joined: 17 Mar 2006 Posts: 26

Hi..,
Can any of u explain me how to use explicit locks in DB2 with the syntax.

martin9 · Posted: Fri Mar 17, 2006 6:30 pm

hy ramp_senti,

Syntax

+------------------------------------------------------------------------+
! !
! >>--LOCK TABLE--table-name-------------------------------------------> !
! +-PARTITION--integer-+ !
! !
! >--IN----SHARE--------MODE------------------------------------------>< !
! +-EXCLUSIVE-+ !
! !
+------------------------------------------------------------------------+

martin9

KS · New User Joined: 28 Feb 2006 Posts: 91 Location: Chennai

Hi,

Following are the two syntax for LOCK :

1. LOCK TABLE table-name IN SHARE MODE;
ex - LOCK TABLE UPAD.EMPLOYEE IN EXCLUSIVE MODE;

2. LOCK TABLE table-name PART n IN EXCLUSIVE MODE;
ex- LOCK TABLE UPAD.EMPLOYEE PART 1 IN EXCLUSIVE MODE;
When the statement is executed, DB2 locks partition 1 with an X lock. The
lock has no effect on locks that already exist on other partitions in the
table space.

Thanks,
KS

ramp_senti · New User Joined: 17 Mar 2006 Posts: 26

Thanks Martin & KS..

How long will the lock be valid..i mean will it be released once the program gets over or do we have to release it explicitly...

KS · New User Joined: 28 Feb 2006 Posts: 91 Location: Chennai

I think locks are held until commit or rollback.
Correct if am wrong.
Thanks,
KS

ramp_senti · New User Joined: 17 Mar 2006 Posts: 26

Hai KS..,
It seems that the lock gets released only when the program ends...i tried to run a select query on the same table which is locked by another program..but the select query is performed only when the program which has locked the table ends....

Okay then any info abt explicit row level locking...