Menu
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?
771
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.
A. A. Only SQL 7.0 has full built-in row-level locking. SQL 6.5 has limited row-level locking that only occurs for inserts to the end of the last page of a table, if "sp_tableoption 'table_name', 'Insert row lock', true" is set.
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.
152
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.
24
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
