How to row lock in SQL Server 2005. I execute a sql for row locking and that is
SELECT *
FROM authors
WITH (HOLDLOCK, ROWLOCK)
WHERE au_id = '274-80-9391'
it work fine but in this case row is lock for update not for selection. I just want to know how to lock a row as a result another user can not see that row when issue a SQL in SQL Server. please guide me. thanks
You can't hide a row so that it won't be seen by other SQL queries. If you open a transaction, lock a row, and hold open the transaction, you can cause other SQL queries to block waiting for your transaction to end and the lock to clear. However if the queries are run with a different transaction isolation level (e.g.: Read Uncommitted) then they will bypass the lock and still see that row's values.
If you want to skip rows that are locked you can use the READPAST hint in SQL Server. This needs to be specified on the query that is reading the locked rows, not the query that's locking them. From Books Online:
Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped.
A SELECT statement with a READPAST hint will return all the non-locked rows and skip the locked rows.
