Effective Record Locking Management in SAP CAP for Concurrent Row Selection and Update

[richa_dwivedi]

Hello All,

How can we effectively manage record locking in SAP CAP when multiple users are selecting the same rows for operations, ensuring that only one user can update the rows while others receive an error?

[robinjayasinghe]

Which runtime are you using? CAP Java or CAP node.js?

[richa_dwivedi]

robinjayasinghe Using CAP node.js

[robinjayasinghe]

On a DB level we support shared locks: https://cap.cloud.sap/docs/node.js/cds-ql#forsharelock

On a logical/UI level, users normally work with draft-flows when data is edited. Here, you can rely on draft-locks: https://cap.cloud.sap/docs/node.js/fiori#draft-locks

I hope this answers your question.

[richa_dwivedi]

Hi robinjayasinghe

Do you have any implementation example on forShareLock()?

[robinjayasinghe]

No running sample but you can see how it's used in our release notes: https://cap.cloud.sap/docs/releases/archive/2022/jun22#shared-locks

So, if you have a CQL statement in a custom handler (e.g. a On handler) you can just append forShareLock() to the statement and you're good to go. 🙂

[richa_dwivedi]

robinjayasinghe Yes, I've tried the same, but it's not working for me.

[robinjayasinghe]

In case you're running locally on SQLite this statement might either produce an error or does not have an effect at all. I just checked: SQLite does not support "SELECT FOR UPDATE" https://www.sqlite.org/lang_select.html. So, this would only work on SAP HANA or PostgreSQL.