Risks of using Multiple Commit Mode

Post Reply
Emixam
Posts: 76
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Risks of using Multiple Commit Mode

Post by Emixam » Wed Sep 09, 2020 6:51 pm

Hello,

I have a daily chore that usually takes 1h20 to run. The following processes are executed:
  1. Update metadata
  2. Update security (Cube, Dimensions and Element security)
  3. Security Refresh
  4. Load data
I also have a SaveDataAll that is executed every hour.

However, multiple times a week, the chore is executed 2 or 3 times in a row. I suspect that a lock conflict happens and then a rollback is trigged and the whole chore is executed again. Sometime, there is user in other parts of the world who try to access to TM1 and I also feel like it might affect the locks.

I'm thinking about changing the property of my chore to "Multiple Commit" to see if it fixes the issue.

My questions are:
1- What are the risks of using Multiple Commit instead of single commit ?
2- Any idea what causes the the chore to run 2 or 3 times in a row ?


Thanks and have a good day

ps: I will use the }bedrock.chore.execution.check TI to exclude SaveDataAll during the daily chore.
Sorry for my English :)

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

Re: Risks of using Multiple Commit Mode

Post by ykud » Sun Sep 13, 2020 7:37 am

2- Any idea what causes the the chore to run 2 or 3 times in a row ?
I'm a big fan of adding
log4j.logger.TM1.Lock.Exception=DEBUG, S1
to the tm1s-log.properties file as it'll show you the lock contention and will say whether a chore is rolled back.
I'd guess that SaveDataAll chore is trying a server-wide lock and causing the chore to restart -- I've seen it quite a few times.
1- What are the risks of using Multiple Commit instead of single commit ?
The only one that comes to mind is that other processes or users be able to see committed values in one of the steps before the whole chore completes. I generally much prefer multiple commit to decrease locking
ps: I will use the }bedrock.chore.execution.check TI to exclude SaveDataAll during the daily chore.
I always see and use a cube with a isChoreRunning flag used instead, with the flag populated in the first step of a multi-commit chore.

Cheers,
Y

Emixam
Posts: 76
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: The Internet

Re: Risks of using Multiple Commit Mode

Post by Emixam » Tue Sep 15, 2020 12:54 am

Thanks Ykud, it's really helpful !
Sorry for my English :)

Post Reply