Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 569
David Avatar asked Apr 20 '10 09:04

David


People also ask

What does Rowlock do in SQL?

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.

Does select statement lock the rows?

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).

How do I use update select?

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.


1 Answers

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.

like image 173
gbn Avatar answered Sep 20 '22 16:09

gbn