Why is content import causing blocked transactions

[patrickbachmann]

Hi folks,

I'm doing a fairly large import of a delivery unit from development to staging environment and noticed that during my content import via delivery unit that many transactions are blocked for SLT trying to perform INSERTS and UPDATES into M tables like MARA, MARC etc.  I noticed that the lock is being caused by a transaction running under _SYS_REPO so I assume it's my imported views activating.  Should this activation be preventing inserts and updates?  If that's the case should I stop SLT mass transfer jobs prior to activating lots of views?  I was thinking that my activation is only READING and should not prevent inserts/updates.

Thanks,

-Patrick

[lbreddemann · Accepted solution]

Hi Patrick,

without checking up on this, here's what I think:

Content activation typically leads to the creation of catalog objects.

These catalog objects depend on other catalog objects - the base tables or other views (and probably other object types, but let's keep stuff simple).

Now, when dependent objects get created, SAP HANA needs to be sure, at least for the timeframe of the creation, that the referenced object is still present and unaltered.

Once the new object is ready and all metadata (including the back and forward references to the base objects) are written to the internal catalog structures, the lock on the object can be released again.

I suspect that the lock held by the activation and the locks requested by SLT during INSERT/UPDATE are not compatible, so the waiting occurs.

So far what I can make out of this story.

Whether or not you should pause the data transfer... phew... hard to answer.

If the only negative effect is a slight but passing slow down of the replication, my gut feeling would be to leave it in place. Catching up after a long content activation phase might take too long for your SLAs.

Also: If possible I'd rather be lazy and don't touch the system

- Lars

[patrickbachmann · Accepted solution]

Hi Lars,

Thanks for the feedback.  Luckily this is just my staging environment.  I ended up stopped the mass transfer job and not long afterwards the activation of the objects completed, then i turned jobs back on and now they are all in error status.  Yet I'm still seeing replication happening from what I can tell.  I'm creating a msg with SAP for that bizarre behavior.

Anyway I'm interested in what you said about;

"I suspect that the lock held by the activation and the locks requested by SLT during INSERT/UPDATE are not compatible, so the waiting occurs."

Can you elaborate on this compatibility thing at all?  That intrigues me.  Also something of interest, this is the only system where have 2 mass transfer jobs pulling from 1 SAP source system and writing into 2 target schemas on 1 destination HANA system. 

Thanks!

-Patrick

[patrickbachmann · Accepted solution]

I'm closing this thread even though I was hoping to squeeze more information out of you... you are probably on your 3 month leave that you lucky guys get....

[lbreddemann · Accepted solution]

Hi Patrick,

sorry - for some reason this one slipped my attention...

Anyhow, what I meant by "incompatible lock modes" was something like this:

Transaction A writes data into the table. To do that, a kind of "shared lock" is set for the table accessed. The point here is not to prevent data change, but to make sure that the table is neither dropped or structurally changed during the access.

Also: when there are exclusive locks on the table, this kind of lock cannot be set.

Transaction B now wants to activate the models and sets exclusive locks on the table during the activation so that the table reference can be set (I _guess_ here(!) that maintaining the reference between the column view and the table is handled just similar to ALTER/DROP TABLE concerning the locking scheme).

In this setup the lock mode "shared lock" on top of the "exclusive lock" wouldn't be compatible, that is both locks cannot exist at the same time for the same lock objects.

Does that make any sense?

Right now I don't have the option to check this hypothesis, but a try to check on that would include some tracing for the locks... (just in case anyone volunteers do try that).

Cheers,

Lars

[patrickbachmann · Accepted solution]

Makes sense, thanks Lars for the extra tidbit!