是通过“选择更新"获得的锁吗?在存储过程结束时释放，即使嵌套在另一个SP中? [英] Is a lock obtained with "select for update" released when the stored procedure ends, even when nested in another SP?

问题描述

我在使用SAP HANA DB SP8.嵌套存储过程时如何处理锁?

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

更具体地说:我有一个名为 p_outer 的SP，它调用了另一个名为 p_inner 的SP. p_inner 通过 select ...进行更新锁定表 T 的一行.在 p_inner 返回到 p_outer 之后， T 中的行是否仍被锁定? p_inner 中没有显式的commit语句.服务器的自动提交设置可能设置为false，但这有关系吗?

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 T still 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?

我没有发现这个特定问题的文档，也没有找到以前关于SAP HANA的专门材料.其他DBM可能会以不同的方式处理它.

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.

推荐答案

我在您的交叉文章中回答了以下问题: http://www.saphana.com/message/13458#13458

I answered the question in your cross post here: http://www.saphana.com/message/13458#13458

为方便起见，此处是从链接复制的内容:

For convenience, here the content copied from the link:

锁是在事务而不是命令的上下文中处理的.

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

因此，即使您将自动提交设置为on(这是会话级设置)，过程调用也将被执行(包括所有其他在中间调用的过程)，并且仅在事务完成之后(通过回滚或提交)

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)

所有锁都被释放.

因此，是的，在内部"调用中被锁定的行仍将在外部"调用中被锁定.

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.

这篇关于是通过“选择更新"获得的锁吗?在存储过程结束时释放，即使嵌套在另一个SP中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！