If you are using EJB's in WCS and optimistic locking avoids database deadlocks and helps lower locks placed on the database and allows more applications to run concurrently against the database.Optimistic locking is implemented by creating OPTCOUNTER column for each table.
1) Add OPTCOUNTER column in the table and optCounter CMP field with unchecked getter\setter methods option in creating new field in CMP bean.
2) Map the table OPTCOUNTER to the optCounter field as a CMP managed field, I see this step missing for all EJB's currently implemented so I did not add optCounter mapping either for the new EJB.
3) Adding this.initializeOptCounter(new XCustomKey(this.customId)) in ejbCreate method()
4) Adding optimistic locking in DeploymentDescriptor --> Bean tab, by checking the Concurrency Control (Enable Optimistic locking)
5) Adding triggers for optCounter and also a file to add these.
CREATE or REPLACE TRIGGER perf_xcustom_1 before update ON XCUSTOM for each row
WHEN ((new.optcounter is null) or (new.optcounter = old.optcounter))
begin if (:old.optcounter < 32767) then :new.optcounter := :old.optcounter + 1; else :new.optcounter := 1; end if; END;
From Infocenter explanation for implementing trigger:
For optimistic locking to work properly, every query that updates a database table row must increment the OPTCOUNTER column value, or reset it to 1 when the current value is 32767. The WebSphere Commerce server uses this technique. However, if database table rows are updated by other code or manual procedures that do not update the OPTCOUNTER column values, then the database triggers defined in the WC_installdir/schema/db_type/wcs.perf.trigger.sql (where db_type is the database type (DB2, Oracle)) schema files ensure that the OPTCOUNTER column values are incremented properly.
Reference:
http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.admin.doc/concepts/cpmoptlock.htm
1) Add OPTCOUNTER column in the table and optCounter CMP field with unchecked getter\setter methods option in creating new field in CMP bean.
2) Map the table OPTCOUNTER to the optCounter field as a CMP managed field, I see this step missing for all EJB's currently implemented so I did not add optCounter mapping either for the new EJB.
3) Adding this.initializeOptCounter(new XCustomKey(this.customId)) in ejbCreate method()
4) Adding optimistic locking in DeploymentDescriptor --> Bean tab, by checking the Concurrency Control (Enable Optimistic locking)
5) Adding triggers for optCounter and also a file to add these.
CREATE or REPLACE TRIGGER perf_xcustom_1 before update ON XCUSTOM for each row
WHEN ((new.optcounter is null) or (new.optcounter = old.optcounter))
begin if (:old.optcounter < 32767) then :new.optcounter := :old.optcounter + 1; else :new.optcounter := 1; end if; END;
From Infocenter explanation for implementing trigger:
For optimistic locking to work properly, every query that updates a database table row must increment the OPTCOUNTER column value, or reset it to 1 when the current value is 32767. The WebSphere Commerce server uses this technique. However, if database table rows are updated by other code or manual procedures that do not update the OPTCOUNTER column values, then the database triggers defined in the WC_installdir/schema/db_type/wcs.perf.trigger.sql (where db_type is the database type (DB2, Oracle)) schema files ensure that the OPTCOUNTER column values are incremented properly.
Reference:
http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.admin.doc/concepts/cpmoptlock.htm