Currently committed is a neat DB2 +9.7 feature that changes how queries wait for locks. For Commerce, it can have a big impact during data load or staging operations by allowing the storefront to continue using data that is being updated. In this post, I will describe how the feature works and how to use it.
Understanding the currently committed feature
Currently committed changes how DB2 deals with locks. Before currently committed, if you ran a query that needed to use locked data, the query went into Lock-Wait until the lock was released.
With currently committed, instead of locking, DB2 is able to provide the query with the last value that was committed.
Let's use the following example:
Transaction TX100 previously set the price for product 123 to $10 and committed. Then a new transaction (TX101) started and changed the price to $60. Remember Commerce doesn't auto-commit, it uses transactions. The price change is not committed until the end of the request. Transaction TX102 starts and attempts to read the price before TX101 has completed. Because write (U/X) and read (S) locks are not compatible, this new transaction locks until the first transaction completes (commits or rolls back). This wait can impact performance.
Now consider the same scenario, with currently committed ON. When the new transaction (TX104) attempts to read the locked row, DB2 is able to return the original value from the transaction logs ($10), and significantly decreases the wait time.
To learn more, check out the DB2 Knowledge Center: Currently committed semantics and cur_commit.
I like it. How do I know if it is enabled?
Although currently committed is enabled by default with all databases first created at the V9.7 level or newer, to maintain compatibility, the feature is not automatically enabled with migrated databases. If your database was originally created before V9.7, the feature might be disabled. The Commerce Knowledge Center mentions this under page: Migrating your database from DB2 V9.5 to DB2 V9.7.
Currently committed is enabled with the cur_commit database configuration setting:
$ db2 get database config for mall | grep CUR_COMMIT
Currently Committed (CUR_COMMIT) = ON
If you are using db2report, look for the setting shows under Config > Database:
This is too good! is there a catch?
Same as with any other system-wide change, it should be tested. With Currently Committed, the lock wait is avoided but there is also cost in retrieving the previously committed values from the transaction logs.
The DB2 knowledge center has this warning under the cur_commit
"Performance considerations might be applicable in a database where there are significant lock conflicts when using currently committed. The committed version of the row is retrieved from the log, and will perform better and avoid log disk activity when the log record is still in the log buffer. Therefore, to improve the performance of retrieving previously committed data, you might consider an increase to the value of the logbufsz parameter."
As part of implementing currently committed, review the log buffer size (logbufsz) and the heap size (dbheap), which needs to be set to AUTOMATIC or larger than logbufsz. The ratio of the transaction logs disk and buffer reads can be found in the db2report, tool under Locking and Performance > Transaction Logs.
Although the queries that use currently committed will not show as locked, the execution time can still be impacted as DB2 needs to retrieve previously committed values from the transaction log buffer or disk.
Monitor the database with db2report during stageprop or data load to confirm the setting is having the desired impact.