Is there a way to lock a row in the SQL 2005-2008 database without starting a transaction, so other processes cannot update the row until it is unlocked?
+1
A:
Everything you execute in the server happens in a transaction, either implicit or explicit.
You can not simply lock a row with no transaction (make the row read only). You can make the database read only, but not just one row.
Explain your purpose and it might be a better solution. Isolation levels and lock hints and row versioning.
Ricardo C
2008-09-21 19:02:10
+2
A:
You can use RowLock or other hints but you should be careful..
The HOLDLOCK hint will instruct SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint will lock only this record and not issue a page or table lock.
The lock will also be released if you close your connection or it times out. I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks. SQL Server has numerous locking hints that you can use. You can see them in Books Online when you search on either HOLDLOCK or ROWLOCK.
Gulzar
2008-09-21 19:02:42
I've tried this, but it doesn't what I want. It indeed holds the lock, when I perform an update. The update waits until the lock is released and after that it updates the record (this is not what I want). Second, I can still perform a select without any problems.
Martijn
2010-09-22 08:26:21
A:
Do you need to lock a row, or should Sql Server's Application locks do what you need?
An Application Locks is just a lock with a name that you can "lock", "unlock" and check if it is locked. see above link for details. (They get unlocked if your connection gets closed etc, so tend to clean themselfs up)
Ian Ringrose
2009-09-22 15:03:16
I cannot use application locks because other applications accessing the 'locked' row doesn't know that they need to check application lock first.
alex
2009-09-22 19:49:46