0

I have a question about the using an expression in UPDATE (UPDLOCK, READPAST, ROWLOCK). When I use to specific row I know it works, but when I use a condition that allows search lists I don't know work, for example:

Specific row

UPDATE TABLE
FROM table AS TABLE WITH(UPDLOCK, READPAST, ROWLOCK)
WHERE ID = 1

List rows

UPDATE TABLE
FROM table AS TABLE WITH(UPDLOCK, READPAST, ROWLOCK)
WHERE 
    STATUS = 'OK' AND
    NAME LIKE 'AB%'

In the second example, I don't know if using (UPDLOCK, READPAST, ROWLOCK) will be efficient, if it will lock many pages to find the records.

user8223022
  • 195
  • 2
  • 15
  • SQL Server (by default) uses **row-level locking** - so only the rows that are actually matched by your conditions will be locked - not entire pages or tables – marc_s Jan 10 '20 at 13:18
  • Try it. Start a transaction, lock the row, examine [sys.dm_tran_locks](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver15). – GSerg Jan 10 '20 at 13:19
  • `are actually matched by your conditions` - true, not to be confused with "actually selected". E.g. `readpast` is often used with `top(1)`, and that may lock a whole bunch of pages if e.g. you are joining tables when doing it. – GSerg Jan 10 '20 at 13:23
  • Sorry, but I did not understand, both cases using **(UPDLOCK, READPAST, ROWLOCK)** expression work? I tried both cases and searched for sys.dm_tran.locks and the result was same – user8223022 Jan 10 '20 at 16:18

0 Answers0