DB2 IS (Intent Share) Locks

Hi
When does DB2 go for an IS (Intent Share) lock?

IS mode is defined as a mode in which "The lock owner can read data in
the locked table, but cannot update this data. Other applications can
read or update the table."
Why are concurrent applications allowed to update the table but the
lock owner is not?

In a normal SELECT query, it is observed that DB2 goes for NS (Next
Key Share) locks on the selected rows and an IS lock on the table.
What is the significance of the IS lock here?

Thanks
Praveen

re: DB2 IS (Intent Share) Locks

"praveen" <pillai.praveen@gmail.comwrote in message
news:1191452726.344050.299530@w3g2000hsg.googlegro ups.com...This prevents someone else from getting an exclusive lock on the table,
either from lock escalation of multiple exclusive row locks, or from an
explicit lock table statement (in exclusive mode).

Multiple IS locks on the same table are OK and do not interfere with each
other.

You worry too much.

re: DB2 IS (Intent Share) Locks

Hi Mark,
Thanks for your response.
A few queries -
1. As per the definition provided on the IBM DB2 website, if a
particular application has an IS lock on a table, other applications
can read or update the table. But as per your answer, someone else
cannot get an exclusive lock on the table (which is needed for
update). This seems contradictory. Am I missing something here?

2. Can multiple NS row locks cause an escalation to a table lock?

We are facing a production scenario with sufficient number of
deadlocks/timeouts. So your help will be greatly appreciated.
Thanks
Praveen

On Oct 3, 11:03 pm, "Mark A" <nob...@nowhere.comwrote:

re: DB2 IS (Intent Share) Locks

On Oct 4, 11:30 am, praveen <pillai.prav...@gmail.comwrote:You might be observing lock escalations due to unavailability of
sufficient memory for any new locks. Two primary parameters that
control lock memory is 'locklist' and 'maxlocks'. Both are DB CFG.
What are your values. Make sure you have a high enough value for
MAXLOCKS (40-60) and then increase locklist by 20% till your
escalation messages go away.

Deadlocks can be because of several reasons. You can dig into details
only after you have got rid of all lock escalations. Basically,
applications are now holding locks larger than they require. After
your lock escalation problems are sorted out, turn db2 deadlock event
monitor and start analyzing individual situations. It is normal and
expected to have infrequent deadlocks in any database. Don't be
alarmed if you see a few every now and then.

~sanjuro

re: DB2 IS (Intent Share) Locks

praveen wrote:
If someone updates a row, an exclusive lock on the row is needed - not on
the table.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

re: DB2 IS (Intent Share) Locks

On Oct 4, 3:47 pm, Knut Stolze <sto...@de.ibm.comwrote:So, if a transaction obtains an IS lock on a table, other transactions
can update rows in the table by obtaining exclusive row locks but they
cannot obtain a lock on the table?
When is this IS lock obtained by DB2?

re: DB2 IS (Intent Share) Locks

"praveen" <pillai.praveen@gmail.comwrote in messageThat is correct. An exclusive lock on a table only occurs when someone
issues a lock table statement (exclusive mode), or lock escalation to table
level occurs (for an exclusive lock on a row), or when some DB2 operation
needs an exclusive lock on the table (such as drop table).

The only thing you need to worry about (assuming your programs are not
issuing lock table statements) is lock escalation. This shows up in the
db2diag.log and in database snapshot, so you should be able to tell if this
is happening.

re: DB2 IS (Intent Share) Locks

praveen wrote:
Correct. They cannot obtain an _exclusive_ lock on the table.
Before the S lock on a row is acquired. The reason is that DB2 must check
that the table does not have an exclusive lock before going to a row and
working on the row. Likewise, if DB2 would set the IS lock after the S
lock, someone else could have locked the table exclusively in between.
Therefore, such hierarchical locks are obtained top-down, i.e. from
database-to-tablespace-to-table-to-page-to-row level.

p.s: Hierarchical locks are standard techniques for database systems (the
internal implementation). You should be able to find a lot of literature
on that.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany