This post is specific to WebSphere Commerce. If you’ve always assumed that stagingprop copied the whole row and every change, I’ve found an execption. Stagingprop does not copy the OPTCOUNTER column.
What is OPTCOUNTER?
OPTCOUNTER is a column used in optimistic locking. Basically, Commerce notes the value of OPTCOUNTER at the start of the transaction. Then when it is ready to issue an update statement, it checks the value of OPTCOUNTER, and doesn’t do the update if OPTCOUNTER doesn’t match. Every update updates OPTCOUNTER. So this means if there was an update to OPTCOUNTER between the start and end of the transaction, there might be a conflict. Have you ever seen the triggers that started with ‘perf’ and wondered what they were for? They’re for making sure OPTCOUNTER is properly updated.
This allows Commerce to run with a lower isolation level. But it pushes some data integrity to the app level. So I’m on the fence as to whether I like it or not. On one hand, all the locking paramters in the db2 registry and the CS isolation level really do reduce deadlocks. On the other hand, it’s not hard for someone to add in custom functionality without adding in the optimistic locking. I’m a DBA. I don’t trust applications further than I can throw them, and since they’re ultimately just ones and zeros, I can’t even get a grip to throw them.
Example of OPTCOUNTER Not Being Propagated
Here’s what that looks like. Since they’re only updated stage, the tables related to espots are a perfect example:
On Stage:
> db2 "select * from intvsched where emspot_id = 48155"
INTVSCHED_ID INITIATIVE_ID EMSPOT_ID STARTDATE ENDDATE PRIORITY STOREENT_ID OPTCOUNTER
------------ ------------- ----------- -------------------------- -------------------------- ----------- ----------- ----------
94856 92056 48155 2012-02-22-09.04.00.000000 9999-12-31-23.59.59.000000 - 11251 26179
1 record(s) selected.
On Prod:
> db2 "select * from intvsched where emspot_id = 48155"
INTVSCHED_ID INITIATIVE_ID EMSPOT_ID STARTDATE ENDDATE PRIORITY STOREENT_ID OPTCOUNTER
------------ ------------- ----------- -------------------------- -------------------------- ----------- ----------- ----------
94856 92056 48155 2012-02-22-09.04.00.000000 9999-12-31-23.59.59.000000 - 11251 -
1 record(s) selected.
As you can see, every part of the row is identical other than the OPTCOUNTER, and that’s just fine.
