Lock Contention and RollBack

Post Reply
User avatar
mce
Community Contributor
Posts: 309
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Lock Contention and RollBack

Post by mce » Wed Jun 26, 2019 12:17 pm

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,

User avatar
Steve Rowe
Site Admin
Posts: 1965
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Lock Contention and RollBack

Post by Steve Rowe » Wed Jun 26, 2019 1:35 pm

What do you see against thread 2200 in TM1Top?

User avatar
mce
Community Contributor
Posts: 309
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: Lock Contention and RollBack

Post by mce » Wed Jun 26, 2019 2:43 pm

Steve Rowe wrote:
Wed Jun 26, 2019 1:35 pm
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.

User avatar
qml
MVP
Posts: 1078
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Lock Contention and RollBack

Post by qml » Wed Jun 26, 2019 3:04 pm

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.
Kamil Arendt

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Lock Contention and RollBack

Post by PavoGa » Thu Jun 27, 2019 2:58 pm

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.
Ty
Cleveland, TN

User avatar
mce
Community Contributor
Posts: 309
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: Lock Contention and RollBack

Post by mce » Thu Jun 27, 2019 4:39 pm

qml wrote:
Wed Jun 26, 2019 3:04 pm
Does your master process also execute a DIMIX on the DimX dimension? Or any other function on that dim?
No, not at all.
PavoGa wrote:
Thu Jun 27, 2019 2:58 pm
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.

User avatar
mce
Community Contributor
Posts: 309
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: Lock Contention and RollBack

Post by mce » Fri Jun 28, 2019 3:52 pm

PavoGa wrote:
Thu Jun 27, 2019 2:58 pm
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.

User avatar
ykud
Posts: 60
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Lock Contention and RollBack

Post by ykud » Thu Aug 15, 2019 9:56 am

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

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Lock Contention and RollBack

Post by PavoGa » Thu Aug 15, 2019 1:27 pm

mce wrote:
Fri Jun 28, 2019 3:52 pm

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.
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3324
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Lock Contention and RollBack

Post by lotsaram » Thu Aug 15, 2019 4:56 pm

Did you try DIMIX( cDim |‘:‘| cHier, vEle) ?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Lock Contention and RollBack

Post by PavoGa » Thu Aug 15, 2019 10:20 pm

lotsaram wrote:
Thu Aug 15, 2019 4:56 pm
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???
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3324
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Lock Contention and RollBack

Post by lotsaram » Fri Aug 16, 2019 6:32 am

PavoGa wrote:
Thu Aug 15, 2019 10:20 pm
lotsaram wrote:
Thu Aug 15, 2019 4:56 pm
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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Post Reply