Search code examples
sql-servert-sqllockingpessimistic-locking

sql rowlock on select statement

I have an ASP.Net webpage where the user selects a row for editing. I want to use the row lock on that row and once the user finishes the editing and updates another user can edit that row i.e. How can I use rowlock so that only one user can edit a row?

Thank you

Solution

  • You can't lock a row like that using DB engine locks.

    Most other strategies would rely on keeping the connection open (such as sp_getapplock) and this is nonsensical in web apps.

    Even if you set a flag on the row, what happens if the user simply closes the browser mid-edit?

    I'd suggest using a timestamp/rowversion column to detect changes to the row in other sessions.