DB2 IS (Intent Share) Locks

[praveen]

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

[Mark A]

"praveen" <pi************@gmail.comwrote in message
news:11**********************@w3g2000hsg.googlegro ups.com...

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

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.

[praveen]

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" <[e-mail]:

"praveen" <[e-mail] in message

news:11**********************@w3g2000hsg.googlegro ups.com...

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

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.

[Sanjuro]

On Oct 4, 11:30 am, praveen <[e-mail]:

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" <[e-mail]:

"praveen" <[e-mail] in message

news:11**********************@w3g2000hsg.googlegro ups.com...

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

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.

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

[Knut Stolze]

praveen wrote:

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?

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

[praveen]

On Oct 4, 3:47 pm, Knut Stolze <[e-mail]:

praveen wrote:

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?

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

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?

[Mark A]

"praveen" <pi************@gmail.comwrote in message

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?

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.

[Knut Stolze]

praveen wrote:

On Oct 4, 3:47 pm, Knut Stolze <[e-mail]:

>praveen wrote:

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?

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

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?

Correct. They cannot obtain an _exclusive_ lock on the table.

When is this IS lock obtained by DB2?

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