Enabling/disabling automerge operation is running for minutes

[former_member536289]

Hello everyone,

I wonder how ENABLE/DISABLE automerge operation is implemented in SAP HANA.
The problem is that our systems (SP11/SP12) suffer from long running DISABLE/ENABLE auto merge regularly (time fluctuates between 20-120 sec).
Moreover, from time to time we face deadlocks by running this operation. Deadlocks which we can't explain since we see no other locks in the system.

Logically it should be just a turn on/turn off operation. But it appears there is something more going on the background.

I've run some tests with empty CDS-based and regular run-time tables.
What I noticed was:

• time of enable/disable automerge was growing in line with the number of embedded calculation views on the top of a table; so time grows in parallel with a volume of growing dependencies. Still for my test tables automerge enable/disable was running for milliseconds but not for minutes;
• during the test I was able to catch one 'automerge enable' lasting for 17 minutes. All this time some activation process was running in the system in parallel.

So based on the described above, my questions are:

• how ENABLE/DISABLE automerge operation is implemented
• what we could do to avoid illustrated issues.

I'd highly appreciate it if anyone could shed some light on this.

Cheers

Elena

[lbreddemann]

How exactly are you switching the setting?

What error message did you get for the deadlock?

Have you tried to check what the thread running the change is doing while you were waiting? Maybe using some stack back traces? The SQL collection SAP note has some nice scripts for that.

What seems obvious is that this is a change in the object definition, which leads to a catalog lock. And generally, after a change is done, dependent objects need to be checked and if necessary invalided. So, it’s not just that simple as a single item update.

[former_member536289]

Hi Lars,

Sorry for so late response.

1. I switch the setting like this, for example:

ALTER TABLE <> ENABLE AUTOMERGE;

2. Eventually I caught a deadlock in the indexserver trace. It appears a deadlock occurs when another thread does activation and some dependent objects get cross-locked.

3. I agree that my tests and traces lead to conclusion it's a result of a catalog lock of an object and all its dependencies. But if it really works this way, I have to disagree that it's something logical.

4. That's why I was looking for some SAP document describing the "internal" mechanism of ENABLE/DISABLE AUTOMERGE to support my tests and observations. Maybe you could point to any?..

5. "And generally, after a change is done, dependent objects need to be checked and if necessary invalided. So, it’s not just that simple as a single item update"

Exactly. It seems here we see an approach, general and convenient from the development and support perspective, which leads to dramatic execution time and deadlocks on a busy system with a lot of dependencies. Correct me I am wrong but I think this specific form of DDL can't harm any dependent objects. Any sense to put a catalog lock and check dependencies here?..
Do you think it would make sense to raise an incident to push an enhancement?

Cheers,

Elena

[lbreddemann]

Without seeing the detailed lock information from the tracefiles the whole dependency check theory remains a hypothesis for the cause of your lock situations.

For the internal mechanisms of how certain operations are implemented, there is no publicly available documentation.

Happy to correct you that, yes indeed, every single catalog update needs to be correctly synchronized (by locks) to prevent inconsistencies. Claiming that this development approach was made out of convenience for development and support seems rather odd and unnecessarily finger pointy to me.

So, how about we dial back the DRAMA here and you open a support incident to see what actually causes the issue?

[former_member536289]

Lars, the fact you don't care is obvious. But in fact as a person who acts here as a SAP representative you should.

"As for the reason for using locks on catalog operations: how would you implement consistent catalog updates otherwise? Think two or more sessions changing that flag in parallel. In order to guarantee that, there needs to be some kind of locking mechanism - here, it is locks."

Still not a reason to check or lock dependent objects. As I said the implementation is development- and support-friendly but not customer-friendly in this specific case.

"that you don't even know how it is in fact implemented, had been implemented that specific way."

Exactly, that's why I came to this site with my question. I got your answer. The only difference is that I see the situation as someone who consumes a solution but you as someone who represents an owner of a solution. Represents in a quite aggressive way btw.

I can't provide logs here because of privacy. If I blur object names, it wouldn't make sense either. What I can say is that now I have more logs to support the idea it happens because of catalog locks. As I suspected and as you confirmed. I'll raise an incident for sure. But based on experience you gave me I am not sure this incident is to be resolved in a positive way.

PS So SAP representative claims that up to 120 sec for DISABLE AUTOMERGE because of excessive catalog locks is not something dramatic? LOL. For now the topic is closed. I'll return when I have some more info.

[former_member536289]

Lars, what kind of inconsistency could enable/disable automerge create? It seems you can't provide any example instead labelling my answers odd, finger pointing, DRAMA etc.

ok, I made conclusions.

[lbreddemann]

Don't really care about your "conclusions", but you wrote about "dramatic" execution times and deadlocks without providing any substantiating material and you were the one coming up with why something, that you don't even know how it is in fact implemented, had been implemented that specific way.

As for the reason for using locks on catalog operations: how would you implement consistent catalog updates otherwise? Think two or more sessions changing that flag in parallel. In order to guarantee that, there needs to be some kind of locking mechanism - here, it is locks. I understand that this can be a difficult concept depending what environment one is used to, but locks are often used not so much to gain the option to change something, but to stop others from changing the same bit at the same time.

Now, you could of course go and say: yes, but the other sessions, that change the catalog, change something different. So, the lock is on the wrong level of granularity. Fair point, but choosing synchronisation points can be tricky and so far, your case is the first where this might be the problem. Maybe, the way you use this is different from most other users? Would that warrant a change in the software?

How about you follow up with a support incident and let the community know about the outcome? There's definitively and audience for that here.