SQL Query to find online users on HANA

[Former Member]

Hello,

I'm trying to query all users that are online on a Hana instance for locking rows for edition. I've tried to query the SYS views M_CONNECTIONS and M_XS_SESSIONS but couldn't find any other user than mine connected while there are more connected users

[lbreddemann · Accepted solution]

This is an effect of lacking permissions.

For a user to be able to see all session the account needs to have the CATALOG READ privilege.

[Former Member · Accepted solution]

Should I be concerned to give all users this permission? I'm implementing record lock based on this so all users should need this permission

[lbreddemann · Accepted solution]

Why would you do this?

HANA provides record locking for you - out of the box.

Also, just because a user is connected doesn't mean it's involved in any form of transaction - and locks only make sense within a transaction.

So, no, I wouldn't know any scenario, where a non-DBA user account would need to see what other users do in the system.

[Former Member · Accepted solution]

Oh... I see.

So I should start a transaction when I open an view, for editing a row, in sapUI5 and commit it when done? I would use the online users table for checking if the user editing the locked row is yet online.

[lbreddemann · Accepted solution]

Goodness...

Look, you seem to be building an application in which users can change data in a stateless UI environment.

That means, there is typically no permanent record lock.

So typically, what you would consider here is so-called optimistic locking.

E.g. you could save version indicators or timestamps with your data and increase those whenever a record got changed. That way you could take the version information for the record your user wants to edit.

On db level there wouldn't be a record lock at this point.

Upon save time, you could go and compare the version indicator you fetched before with the one present in the table for your record.

If they still match, you perform your db-update. If not, then someone else had already overwritten the record meanwhile.

This of course makes it possible that two or more users spend time and effort entering data and only one of them will be able to save the result.

Alternatively you would have to implement a central "logical lock" - very much what the ABAP enqueue does. That's not easy to get right and scalable, though.

In any case, checking which other users are online in itself, is not getting you all that far.