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
Row-level locking means that only the row that is accessed by an application will be locked. Hence, all other rows that belong to the same page are free and can be used by other applications. The Database Engine can also lock the page on which the row that has to be locked is stored.
SELECT statements get shared locks on the rows that satisfy the WHERE clause (but do not prevent inserts into this range). UPDATEs and DELETEs get exclusive locks on a range of rows. INSERT statements get exclusive locks on single rows (and sometimes on the preceding rows).
Example: SELECT FOR UPDATE in action A complete transaction that uses SELECT FOR UPDATE on that table could look like this: BEGIN; SELECT * FROM kv WHERE k = 1 FOR UPDATE; UPDATE kv SET v = v + 5 WHERE k = 1; COMMIT ; Working line by line through the statement above: The first line, BEGIN , initiates the transaction.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With