Stored Procedure and DB stability

[BenedictV]

I was going through the note: 1650957 - SAP HANA Database: Starting the Script Server. I came across this line about stored procedures...

"The disadvantage of database procedures, in general, is that they might somehow affect the stability of the database itself. In order to minimize the risk of destabilizing the SAP HANA database...."

A google search did not return anything on how a SP might affect a DB's stability. I am just curious since I have never heard this thing about SPs anywhere before 🙂

Can anyone throw some light on this?

[lbreddemann · Accepted solution]

Besides certain parts of stored procedures the scriptserver also executed AFL/BFL function code. These can be externally written and may use resources outside the workload management restrictions. Having such code in a separate process should provide a better isolation against any issues in that kind of code.

I doubt that sqlscript procedures were considered specifically problematic when this statement was put into the documentation/sap note.

[k_gorin]

The general problem with most DB development using SP is it's harder to keep tracks of what's going on. For example you could easily get a deadlock condition from 2 SP using the same lock objects. It's the same kind of problems as in multithreaded development.

[lbreddemann]

I'm not following: what does a potential deadlock of two session has to do with stored procedures? And why would having a scriptserver process even change this? Bear in mind that SQL locks work on transaction level, which is a concept of the user sessions and not of the server side execution context/process/threads.

[k_gorin]

Well TS was asking about "disadvantage of database procedures". Your answer was irrelevant and following the general "SAP has already delt with that" trend. I'm talking explicit locks, not transaction isolation.

I'm sorry that you are unable to follow simple concepts, but rush to down vote everyone else.

[lbreddemann]

I downvoted your "answer" because it didn't address the question around the separate scriptserver but basically made a general statement about database procedures in general which you didn't substantiate.

Why would the use of database procedures lead to any additional locks in your view? And what do you believe is the difference between "explicit locks" and transaction isolation?

I'm going to ignore your condescending tone for now but would appreciate if you would dial it back to a civil conversation.

[k_gorin]

I'm talking about lock table statement. What do you mean by "locks in my view"?

[lbreddemann]

It looks like you mix up the idea of database locks with database procedure usage. Locks (implicit and explicit) are used all the time when accessing data in HANA (and most other DBMS for that matter). Whether or not you put your statements into a stored procedure doesn't change this or any of the effects of it.

When using explicit wide-ranging locks like a TABLE LOCK, then, of course, you can make it easier for yourself to create deadlocks. Connection to the usage of DB procedures? None.

Locks are not dependent on the coding artefact you use, but on the transaction they belong to.