By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,328 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

DB2/UDB and Read Locks

P: n/a
I have found the DB2/UDB implentation of locks a little more hard to
deal with than Oracle's locking.

First I realize there is lock escalation.

But it would help me if somebody loudly thought out their answers to
these following questions

1) The Default locking behaviour is ROW Level (Page I used to think)
and the default lock is S(hare).
If I issued say the following statement
SELECT X FROM Y WHERE RECORD_DATE > '2005-01-01' its going to
get a SHARE LOCK on rows that match the above criteria

2) SHARE Locks allow Reads by the one who issued the lock and all other
concurrent users on the locked set of data, but nobody can update these
rows.

The inability of other users to update these rows is annoying, but I
understand its becuase of the lack of sufficient ROLLBACK/UNDO
mechanism in DB2.(Concurrency protection one might say)

3) How do I get around this problem.

I would like READ ONLY APPLICATIONS to issue SELECTS that do not bar
other concurrent users from UPDATING these very rows?

Can I do it without explicitly stating the intentions of the
application on locking the table or rows or if not how to issue this
intent. (Not all tools are friendly to issuing these kind of session
settings).

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Zri Man" <Da**********************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I have found the DB2/UDB implentation of locks a little more hard to
deal with than Oracle's locking.

First I realize there is lock escalation.

But it would help me if somebody loudly thought out their answers to
these following questions

1) The Default locking behaviour is ROW Level (Page I used to think)
and the default lock is S(hare).
If I issued say the following statement
SELECT X FROM Y WHERE RECORD_DATE > '2005-01-01' its going to
get a SHARE LOCK on rows that match the above criteria

2) SHARE Locks allow Reads by the one who issued the lock and all other
concurrent users on the locked set of data, but nobody can update these
rows.

The inability of other users to update these rows is annoying, but I
understand its becuase of the lack of sufficient ROLLBACK/UNDO
mechanism in DB2.(Concurrency protection one might say)

3) How do I get around this problem.

I would like READ ONLY APPLICATIONS to issue SELECTS that do not bar
other concurrent users from UPDATING these very rows?

Can I do it without explicitly stating the intentions of the
application on locking the table or rows or if not how to issue this
intent. (Not all tools are friendly to issuing these kind of session
settings).

If you use cursor stability isolation level (the default for most clients),
DB2 will release a lock when it rolls off that row in a cursor operation.
However, if DB2 has to materialize the cursor, that may not always be true.
Locks are also released when a commit is issued.

If you use uncommitted read (UR) isolation level (for a particular SQL
statement) then no share lock is taken:

SELECT X FROM Y WHERE RECORD_DATE > '2005-01-01' with UR

DB2 for z/OS has page locks (the default), but DB2 for LUW never has had
page locks.
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.