Locking behavior in nested stored procedure

[Martin_Fei]

Crosspost from Stack Overflow: sql - Is a lock obtained with "select for update" released when the stored procedu...

I am on SAP HANA DB SP8. How are locks handled when nesting stored procedures?

More specifically: I have one SP called p_outer which calls another SP called p_inner. p_inner locks one row of table T via select ... for update. After p_inner returns to p_outer, will the row in Tstill be locked? There is no explicit commit statement in p_inner. The server's autocommit setting is probably set to false, but does this matter?

I did not find this specific issue documented, neither did I find previous material on this specifically for SAP HANA. Other DBMs may handle it differently.

[lbreddemann]

Would be easier to get an answer to this in the SCN space for HANA, but ok...

Locks are handled in the context of a transaction - not of commands.

Therefore, even if you have autocommit set to on (which is a session-level setting) then the procedure call gets executed (including all other procedures that get called in-between) and only afterwards the transaction is finished (either via ROLLBACK or COMMIT)

With that all locks are released.

So, yes, the rows that get locked in the "inner" calls will still be locked in the "outer" call.

However, since this is all one single transaction, the "outer" call can of course access the records locked by the "inner" call.

cheers,

Lars