DB2 IS (Intent Share) locks

[c***@gmail.com]

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

The IBM website defines IS mode 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?

When a SELECT query is executed, what are the locks that DB2 obtains?

Thanks in advance
Praveen

[Knut Stolze]

Post by c***@gmail.com
When does DB2 go for an IS (Intent Share) lock?
The IBM website defines IS mode 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?
When a SELECT query is executed, what are the locks that DB2 obtains?

IS at table level indicates that the owner of that lock reads some rows of
that table. IX on table level indicates that the owner changes some rows
of that table. The updated and changed rows don't have to be the same.

The "Intent" locks are important for everything operating on table level
like lock escalation or DROP TABLE statements. For example, if one user
reads data in the table, another wants to drop a table, you must bring both
together. The IS indicates that dropping the table is not such a good idea
right now... Another alternative would be that a DROP TABLE locks all
existing rows exclusively - but that is a huge performance killer and you
would have to prevent new rows from being inserted somehow. Lock
escalation is similar: if DB2 runs short of lock space, it can't escalate
many row locks on a table to a table if there are other transactions
accessing rows of that table with incompatible locks.