Lock Contention and RollBack

[mce]

Hello,

I am trying to load data to a cube in many parallel threads using RunTI.
Each parallel thread is hitting a different portion of the cube.
But before loading data, we do some master data check to make sure elements exists in dimensions and we also lookup some values from other cubes.
For some reason my threads are hitting contention lock and do roll back and once they roll back, they all execute in order not in parallel. They keep waiting each other.

Below is a sample tm1server.log record describing the lock contention.

Code: Select all

17956   [c78]   DEBUG   2019-06-24 18:22:40.383   application_name.Lock.Exception   CommitActionLog::Rollback: Called for thread '17956' of user 'CAMID("CognosEx:u:uid=runti_user")' executing function 'ProcessExecuteEx'.
20180   [c96]   DEBUG   2019-06-24 18:23:00.137   application_name.Lock.Exception   Contention encountered attempting to acquire lock (0x000000000BE7ACD8) on object [Dimension "DimX", addr=0x000000000BE7AC10, index=R-1639] in IX mode at ..\application_name_r7s\DimensionData.cpp:1732 during function 'ProcessExecuteEx'. Entering wait state 'IXC'.
Blocked by the following 1 thread:
Thread 22000 holds the lock in IX mode

No thread is updating "DimX" dimension and there is no public MDX subset in this dimension. The cube I am updating does not have DimX dimension at all. I use DIMIX('DimX', vDim) in the process and I look up some values from a cube that has DimX dimension. That is how I touch this dimension in this process, which are all read only calls. However for some reason there is IX lock on this object as per log file, but I do not understand why these read operations put an IX lock on this dimension.

Any help will be appreciated. Thanks in advance.

Regards,

[Steve Rowe]

What do you see against thread 2200 in TM1Top?

[mce]

What do you see against thread 2200 in TM1Top?

Only one process is in Run state, all others are in wait state in Tm1Top. In this case thread 2200 is at Run:R state. All other threads are in Wait:IXC state.

[qml]

mce,

Does your master process also execute a DIMIX on the DimX dimension? Or any other function on that dim?

Contrary to what you might think, DIMIX sometimes requires more than just an R lock. I don't understand it fully, but I have observed that e.g. the first DIMIX on a given dim after a server restart might need an IX/W lock. I suspect reading from the dim index triggers some sort of caching/update - maybe to do with hierarchies. I haven't been able to produce a full empirical catalogue of the circumstances that trigger it, but it does happen.

[PavoGa]

I have not tested this, but one possible way around this is to build a temporary subset and use the SubsetElementGetIndex function instead of DIMIX against the base dimension.

[mce]

Does your master process also execute a DIMIX on the DimX dimension? Or any other function on that dim?

No, not at all.

I have not tested this, but one possible way around this is to build a temporary subset and use the SubsetElementGetIndex function instead of DIMIX against the base dimension.

I will try this to see if it makes any difference. Thanks.

[mce]

I have not tested this, but one possible way around this is to build a temporary subset and use the SubsetElementGetIndex function instead of DIMIX against the base dimension.

I tested this and compared performances for DIMIX against SubsetElementGetIndex in a large dimenson (having more than 10k members).
The result is DIMIX is 400 times faster than SubsetElementGetIndex. Hence, when I replaced DIMIX with SubsetElementGetIndex, my processes never finishes at all and keep running. So, it looks SubsetElementGetIndex is not an option in such cases.

[ykud]

Hi mce,

Did you end up figuring out the root causes of it? What version of PA you’re seeing this in?
We see something similar (although not consistently) on PA 2.0.6 if 19.

Y

[PavoGa]

I tested this and compared performances for DIMIX against SubsetElementGetIndex in a large dimenson (having more than 10k members).
The result is DIMIX is 400 times faster than SubsetElementGetIndex. Hence, when I replaced DIMIX with SubsetElementGetIndex, my processes never finishes at all and keep running. So, it looks SubsetElementGetIndex is not an option in such cases.

Yep, saw the same thing. Using a temporary dimension and DIRECT functions to manage it.

[lotsaram]

Did you try DIMIX( cDim |‘:‘| cHier, vEle) ?

[PavoGa]

Did you try DIMIX( cDim |‘:‘| cHier, vEle) ?

Do not know if this directed at my response, but I was not really clear. Where I'm using the temporary dimensions is when generating an email table of incorrect mappings/etc and, for instance the same account pops up 200 times. Only wanting one line for it, the tmp dimension takes care of it.

If DIMIX(...) still locked it, I'd try ElementIndex(cDim, cHier, vEle) as well. Possibly the new function is optimized to avoid IX/W lock???

[lotsaram]

Did you try DIMIX( cDim |‘:‘| cHier, vEle) ?

Do not know if this directed at my response, but I was not really clear. Where I'm using the temporary dimensions is when generating an email table of incorrect mappings/etc and, for instance the same account pops up 200 times. Only wanting one line for it, the tmp dimension takes care of it.

If DIMIX(...) still locked it, I'd try ElementIndex(cDim, cHier, vEle) as well. Possibly the new function is optimized to avoid IX/W lock???

I guess the question was actually directed at mce whether it is potentially faster to use Dimix rather than ElementIndex