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

"praveen" <pillai.praveen (AT) gmail (DOT) com> wrote

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.

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... (AT) nowhere (DOT) com> wrote:

On Oct 4, 11:30 am, praveen <pillai.prav... (AT) gmail (DOT) com> wrote:
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

On Oct 4, 1:33 pm, Sanjuro <ashru... (AT) gmail (DOT) com> wrote:
locklist is set to 10000 and maxlocks is 10.

the application is EJB-based. hence the transaction is handled by the
container i.e. they will not get committed till the transaction
workflow completes.

we anyalzed a situation using the deadlock event monitor. one such
scenario...
transaction t1 issues select on table p1 --> obtains NS lock. there is
also an IS lock on this table
transaction t2 issues delete on table c1 --> obtains X lock
transaction t1 issues select on table c1 --> doesnt get lock since t2
has X lock
transaction t2 issues delete on table p1 --> doesnt get lock since t1
has a lock
deadlocked!
one solution is to change the order of the selects.

but my question is why is a "select" by t1 failing to grant a lock to
t2? this is possible if there is a table-level lock on table p1. is
the IS lock responsible for this? which is why i wanted to know how an
IS lock is caused?

thanks
praveen

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

On Oct 4, 3:47 pm, Knut Stolze <sto... (AT) de (DOT) ibm.com> wrote:
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?

"praveen" <pillai.praveen (AT) gmail (DOT) com> wrote in message
That 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.

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