sql rowlock on select statement

sql-server tsql locking pessimistic-locking
David picture David · Apr 20, 2010 · Viewed 11.5k times · Source

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

Answer

gbn picture gbn · Apr 20, 2010

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.

Related questions

SQL Server - How to lock a table until a stored procedure finishes

I want to do this: create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible? Ultimately I …

Read More
Confused about UPDLOCK, HOLDLOCK

While researching the use of Table Hints, I came across these two questions: Which lock hints should I use (T-SQL)? What effect does HOLDLOCK have on UPDLOCK? Answers to both questions say that when using (UPDLOCK, HOLDLOCK), other processes will …

Read More
Only inserting a row if it's not already there

I had always used something similar to the following to achieve it: INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WHERE PrimaryKey = @primaryKey) ...but once under load, a primary key violation occurred. This is …

Read More