DB2 Version This Was Written For
9.7
Parameter Name
DB2_SKIPINSERTED
Where This Parameter Lives
DB2 Registry (db2set)
How To Check Value
> db2set -all |grep DB2_SKIPINSERTED [i] DB2_SKIPINSERTED=YES [DB2_WORKLOAD]
OR
> db2 "select substr(reg_var_name,1,32) name, substr(reg_var_value,1,16) value, level, is_aggregate, substr(aggregate_name,1,32) aggregate_name from SYSIBMADM.reg_variables where reg_var_name='DB2_SKIPINSERTED' with ur" NAME VALUE LEVEL IS_AGGREGATE AGGREGATE_NAME -------------------------------- ---------------- ----- ------------ -------------------------------- DB2_SKIPINSERTED YES I 0 DB2_WORKLOAD
Description
Affects db2’s locking/scanning behavior when using CS or RS isolation levels. Causes DB2 to skip uncommitted inserts as if they did not exist. Variable is activated at database start time, and is engaged (or not) at statement compile or bind time.
This variable has no effect if Currently Commited behavior is enabled (CUR_COMMIT).
Impact
Can reduce locking/deadlocking and increase concurrency if applications can tolerate the data integrity changes. Potentially dangerous if the application does not explicitly support this behavior.
Default
NO/OFF
Range/Values
[ON, OFF}
Recycle Required To Take Effect?
Yes
Can It Be Set To AUTOMATIC?
No, there is no AUTOMATIC option for this parameter.
How To Change It
db2set DB2_SKIPINSERTED=YES
Rule of Thumb
If your application does not explicitly support it, do not set this parameter – leave it at the default of NO.
Tuning Considerations
The main consideration here is if your application supports this value. With the new cur_commit parameter, it is less likely you will use this. But if your application supports this behavior, you should set this parameter.
Along with DB2_SKIPDELETED and DB2_EVALUNCOMMITTED, you can drastically reduce deadlocking.
Related Error Messages
War Stories From The Real World
WebSphere Commerce has supported DB2_SKIPINSERTED, DB2_SKIPDELETED, and DB2_EVALUNCOMMITTED for years. They’re part of the aggregate DB2 Registry setting DB2_WORKLOAD=WC, but we were setting them independently long before that. I have personally seen them cause a WebSphere Commerce site go from having dozens of deadlocks per day to just one or two. So for WebSphere Commerce databases, they absolutely must be set.
I wonder how having the three related parameters set is different from Currently Committed behavior available in DB2 9.7?
Think long and hard before enabling this, because you are changing the behavior of your isolation level by enabling this, and may allow concurrency phenomena that you did not intend.
While I agree that some applications may be written more to locks than Isolations levels and phenomena and as such turning this on may change/break those applications, SKIPINSERTED does not change or violate the isolation levels (CS, RS) that it is applied to. So I don’t classify this one at all in the ‘dangerous’ category as skipdeleted and evaluncommitted are (also just perused your reducing deadlocks article).
Note also that a long time ago when we added lock avoidance techniques (ie. if a page is known to be committed and latched we can process rows w/o locking) this also caused some issues. For example, if applications were written to use exclusive locks even on rows they hadn’t updated yet (if ever) but the expectation was that any scan above UR would wait on the lock. This was also a driving motivation for WAIT FOR OUTCOME which allows us at an individual statement level to disable every concurrency improvement we’ve made over the years, especially including Currently Committed.
With Currently Committed we can avoid many deadlock and lock wait scenarios while not breaking isolation level guarantees. But as pointed out in other articles, applications written to the implementation (as in lock waits) either implicitly or explicitly could have issues. We definitely don’t allow any additional phenomena to occur, but you could see them either more frequently or where you didn’t before – the implementation may have masked phenomena you were open to all along. The same can occur based on access plan changes, like table vs. index, or by adding say a new index.
A great way of putting it. I think a significant number of developers fail to even attempt to understand isolation levels these days, much less the minor distinctions like this.
I would hope the owner of the Currently Committed development line item would have a pretty good handle on these ;-).