Question re Locks taken by Insert SQL

Michael Hannan

Question re Locks taken by Insert SQL

Maybe this question will be too hard?

We have a monitor reporting at times high latch wait for Insert SQL, and at times higher Lock Wait than Latch wait.

Now I have checked for things like Lock Escalation occurring on the table and ruled that out, and also ruled out Lock Table SQL. I have to check for RR readers to rule that out based on IBM Doco below. I doubt that, but want to consider what other things will cause Lock Waits.

The DB2 Resource Serialisation Redbook says:
"Inserts are thus important for locking purposes only if they suspend other programs doing
updates or deletes (or some selects). They cannot be suspended themselves except when:
The lock size is a table, table space, or table space partition due to either lock escalation
or due to table space LOCKSIZE being TABLE or TABLESPACE.
The UW first does a SELECT with RR, which gets an S table space or table lock and then
the INSERT would promote the lock to SIX. This would be incompatible with all other
transactions except those getting an IS lock on the TS or table.
A SELECT with RR isolation (RR reader) is holding an S lock on a set of rows and the new
row you are trying to insert would satisfy the RR reader’s selection criteria."

Now I read elsewhere that R.I. checks can take an S Lock on the DBD, so that could suspend an Inserter if another process has updated the DBD and not committed, or is suspended waiting to update the DBD.

It seems that Inserters will fail if non-unique key is detected, without waiting for a Lock on anything, as it can see the RID in the index, for the key value.

What if another process has deleted the Unique Key value to be Inserted? The Index will have a pseudo-deleted RID entry, in case of  rollback of the Deleter. Will Insert of that Unique key fail due to pseudo-deleted entry, or will the Inserter attempt to find out if the pseudo-delete is committed, via techniques similar to Lock Avoidance Commit LSN and Punc Bits, and eventually have to Lock to determine if the Delete is commited?   It could use conditional S Lock to check if the Delete is committed perhaps, in order to fail promptly instead of waiting for the Deleter to Commit. 

I can test easily that Insert will fail if Deleter has not committed, which we can be sure already, will fail. So no need to test that.  However I am not sure if Inserter is guaranteed to work if Deleter has committed, and success of one test would not be proof, it will always succeed.

The doco from IBM does not seem to cover Locks for guaranteeing Uniqueness at all, that I can see. No suggestion that checking Uniqueness will try for any type of lock, but suggests Lock suspend is not possible for this reason. 

Any experience on Uniqueness Check Locking or lack of it would be interesting. I have failed to find any doco on the topic so far.

Michael Hannan,

DB2 Application Performance Specialist