CODEWITHSUNDEEP
db2
Yash
Yash

Reputation: 1008

LOCK table in SHARE mode does not block client

Based on the documentation at https://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_sql_locktable.dita a SHARE MODE lock prevents other processes from executing anything but read-only operations on the table.

We tried the following: Process 1(P1) : LOCK table T1 in SHARE MODE. P2: LOCK table T1 in SHARE MODE.

We were expecting P2 to be held up for the lock to be released as P1 had aquired the lock first. Instead, the statement succeeds for P2.

Does this not conflict with the statement in the documentation?

We have two identical programs P1 and P2 and want to ensure that only one of them gets a chance to INSERT records in the table at a time. We want each process to :

1.Lock the table in share mode

2.Insert records

3.Commit

Is there any other way to achieve this?

Regards,

Yash

Upvotes: 1

Views: 303

Answers (1)

data_henrik
data_henrik

Reputation: 17118

Two SHARE locks are compatible, i.e., as long as both programs/transactions only try to read they can proceed. Once P1 acquires an EXCLUSIVE lock on table T1, other transactions cannot proceed. Moreover, if P1 has a SHARE lock on T1, then P2 would need to wait when requesting an EXCLUSIVE lock.

In general, I would try to avoid using LOCK TABLE if possible. Do you have other options to synchronize the work of the two programs?

Upvotes: 3

Related Questions