OCC! OCC! OCC!

(This is OCC as I understand and not the official documentation)

So by now everyone by now must have heard about OCC or noticed the RecVersion column in kernel rollup or even looked at OCC in action if one attended convergence and got hold of an evaluation version.

This is a short discussion to give you a brief overview of how OCC really works.

The base concept is very simple.

Every row has a new column called RecVersion. This is automatically set to 1 when a record is being inserted. When you select a record for update, this row version is assigned a random number. When you actually update the record the kernel will check if the original row version is still the same in the database. If not then someone else has changed the row and the kernel will throw a new error called UpdateConflict.

For e.g. ReqTrans has the following record

ReqPlanId RefId Qty RecVersion DataAreaId

MP-1 I-001 1 987 DAT

The above record is selected for update and you change the quantity. The kernel has in memory two versions of the row.

Original Version (the one that can be accessed by reqTrans.orig())

ReqPlanId RefId Qty RecVersion DataAreaId

MP-1 I-001 1 987 DAT

New Version

ReqPlanId RefId Qty RecVersion DataAreaId

MP-1 I-001 2 345 DAT

(Notice that qty as well as recVersion has changed).

Now when you call for update or doupdate... Kernel will check if the row in the database still contains recVersion as 987. If yes, then the update will go ahead. If no, then the kernel will throw update conflict.

So how does this increase performance?

In V3 try the following job

static void Job2(Args _args)

{

ReqTrans reqTrans;

;

ttsbegin;

select forupdate firstonly reqTrans;

reqTrans.Qty = 1;

reqTrans.update();

ttscommit;

}

Put a debug on reqTrans.Update() and run the job. Now check the locks in the sql server (Enterprise manager under Management – current activity – Locks/Object)

You will see a lot of intent locks on table ReqTrans, OCC will save taking these intent locks and converting them to actual update locks while updating the records.

If you try this in V4 then you will actually see what is happening with the locks.

There is one more advantage, take a look at the following code:

static void Job2(Args _args)

{

ReqTrans reqTrans;

;

ttsbegin;

while select forupdate reqTrans

{

if (reqTrans.Qty == 1)

{

reqTrans.Qty = 2;

reqTrans.update();

}

ttscommit;

}

You will notice that not all the records that are selected for update are actually updated. In this case (since OCC does not put an update lock) till you actually update the row, other processes are not blocked from accessing the records.

So how is this implemented?

You will find the following code..

catch (Exception::UpdateConflict)

{

if (appl.ttsLevel() == 0)

{

if (xSession::currentRetryCount() >= #RetryNum)

{

throw Exception::UpdateConflictNotRecovered;

}

else

{

retry;

}

else

{

throw Exception::UpdateConflict;

}

What this code is doing is executing a retry if current ttsLevel is zero.

Why do you need this check?

If you execute a retry before final ttscommit, database will not roll back the transactions. So if you are inserting a transaction somewhere in the process then the data will get inserted twice for e.g.

static void Job2(Args _args)

{

ReqTrans reqTrans;

LedgerTrans ledgerTrans;

;

ttsbegin;

while select forupdate reqTrans

{

try

{

ledgerTrans.AccountNum = '1';

ledgerTrans.insert();

if (reqTrans.Qty == 1)

{

reqTrans.Qty = 2;

reqTrans.update();

}

catch (exception::UpdateConflict)

retry;

}

ttscommit;

}

Notice that in the above code the retry is before ttscommit. In such cases if the process executes retry statement then the data in ledgertrans is going to be inserted again.

Which leads to another thing that one must take care of during retry.

Look at the following code:

static void Job2(Args _args)

{

ReqTrans reqTrans;

int countI;

;

try

{

ttsbegin;

while select forUpdate ReqTrans

{

if (reqTrans.Qty == 1)

{

reqTrans.Qty = 2;

reqTrans.update();

countI++;

}

ttscommit;

}

catch (exception::Deadlock)

retry;

box::info("Updated "+int2str(countI)+" records");

}

Any guess what is going to happen with countI in case of retry? You are right, the countI variable is never reset. Remember that retry may or may not roll back database however it will never ever roll back objects in the memory and you need to roll them back manually.

That’s it folks! Enjoy!

Send mail to harry@systomatics.com with questions or comments about this web site.

Disclaimer: I am working with Microsoft Business Solutions. The code on this site may or may not be related to my official duties with Microsoft. I do not claim in any expertise in modules represented on this website. Essentially there is just one person doing functional specifications (in head), design specifications (in head), coding and some testing. There is no way the project on this site will be free of bugs. The projects are intended as guidelines and may god help you if you decide to implement the projects without making any changes. If you implement any project resulting into data corruption or anything like that then do not even think of suing me because a. I have already warned you and b. I don't have any money. I may or may not respond to your emails about supporting the project. I may or may not upgrade the projects to the next service pack / version.