3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thomas
  • 33,544
  • 126
  • 357
  • 626
  • 1
    i have no idea what you are trying to achieve.... – Mitch Wheat Jan 07 '11 at 07:38
  • 1
    If you post code or XML, **please** highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jan 07 '11 at 07:40
  • 2
    Why "lock" or "hide" a column while you're updating?? This sounds like old-school "dBase" thinking..... – marc_s Jan 07 '11 at 07:40
  • See [the discussion here](http://stackoverflow.com/questions/4609217/sql-server-the-misleading-xlock-optimizations) or [here](http://stackoverflow.com/questions/4596972/how-to-exclusively-lock-a-row-that-prevent-crud-operation) for 2 recent questions on the same issue. It would probably be better if you explain the end goal though. Are you trying to use a table as a queue for example? – Martin Smith Jan 07 '11 at 16:14
  • possible duplicate of [Sql server row lock](http://stackoverflow.com/questions/4439727/sql-server-row-lock) – NGLN Oct 18 '11 at 19:58

2 Answers2

2

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.

Rick
  • 4,575
  • 1
  • 26
  • 20
1

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.

graz
  • 51
  • 1
  • 3