5

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

gbn
  • 422,506
  • 82
  • 585
  • 676
David
  • 53
  • 1
  • 1
  • 3

2 Answers2

5

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    +1 definitely - don't even get into that "locking by yourself" mess - it's just not worth the trouble... – marc_s Apr 20 '10 at 09:55
  • Thanks, I will go with timestamp method. – David Apr 20 '10 at 10:01
  • +1, I do this, when you load the data for the page get the complete datetime of the last change. when you go to save the data include `AND LastChangeDate=.......` on the UPDATE, if rowcount=0 then I issue an error message about someone else has already changed that data. – KM. Apr 20 '10 at 12:00
5

Here i am giving two set of sql to row lock during select statement.

BEGIN TRAN

SELECT *
FROM   authors AU
WITH   (HOLDLOCK, ROWLOCK)
WHERE  AU.au_id = '274-80-9391'

/* Do all your stuff here while the row is locked */

COMMIT TRAN

The HOLDLOCK hint politely asks SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint politely asks SQL Server to lock only this row rather than issuing a page or table lock.

Be aware that if lots of rows are affected, either SQL Server will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing.

another one

SELECT id From mytable WITH (ROWLOCK, UPDLOCK) WHERE id = 1

another good link i want to share with you on lock. https://www.mssqltips.com/sqlservertip/1257/processing-data-queues-in-sql-server-with-readpast-and-updlock/

thanks

Mou
  • 15,673
  • 43
  • 156
  • 275
  • +1 for answering the question I came here to find, but it doesn't answer the OP's question. You can't open a transaction, allow user input on a web page, then commit the transaction. The whole transaction has to be one call to the server. – Kristen Hammack Jan 25 '17 at 14:25