Update lock does lock entire table's all rows

Question

BEGIN TRANSACTION
UPDATE dummy WITH (UPDLOCK)
SET   name = 'James'
WHERE SSN=10

i read this post https://stackoverflow.com/a/9818448 please have a look what he is saying but i just want to know what he is saying that is true or not ?

He is saying UPDLOCK lock entire table's all rows if that field is not under index. means there is no index on SSN field then sql server read all rows and lock.....is it true.

he suggest to use UPDLock this way

UPDATE dummy WITH (UPDLOCK, INDEX(TBLINDEX))
SET name = 'James'
WHERE SSN=10

1) just confirm me what @ManuelConde is saying that is true ? if yes that what will be work around solution if i do not have index on that field called SSN

if i use UPDLOCK and ROWLOCK together then does this problem still happen means sql server will lock all rows instead of SSN=10 ?

UPDATE dummy WITH (UPDLOCK, ROWLOCK)
SET name = 'James'
WHERE SSN=10

please explain to drive out my confusion. Thanks

Answer 1

Hi SudipBhatt-9737,

UPDATE dummy WITH (UPDLOCK, INDEX(TBLINDEX))

Please note that Index hints WITH INDEX(TBLINDEX) are only allowed in a FROM or OPTION clause, and cannot be used in the UPDATE statement.

If there is no index to locate the row to be locked, all the tested rows will be locked, and the lock on the qualified rows will be retained until the transaction is completed. But using indexes, SQL Server can quickly locate and lock only those rows that meet the conditions.

if i use UPDLOCK and ROWLOCK together then does this problem still happen means sql server will lock all rows instead of SSN=10 ?

Based on my test, without or with index, it seems there is no difference between using UPDLOCK , ROWLOCK together and using UPDLOCK alone.

Using UPDLOCK , ROWLOCK without index

Using UPDLOCK without index

Using UPDLOCK with clustered index

Using UPDLOCK , ROWLOCK with clustered index

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can use this to investigate which locks you get:

   BEGIN TRANSACTION  
   UPDATE dummy WITH (UPDLOCK)  
   SET   name = 'James'  
   WHERE SSN=10  
     
   SELECT resource_type, request_mode, resource_associated_entity_id,  
          object_name(try_convert(int, resource_associated_entity_id)),   
          COUNT(*) AS [Count]  
   FROM   sys.dm_tran_locks  
   WHERE  request_session_id = @@spid  
   GROUP  BY resource_type, request_mode, resource_associated_entity_id,  
             object_name(try_convert(int, resource_associated_entity_id))   
   go  
   ROLLBACK TRANSACTION  

If you know to know what the codes mean, you can lock in the documentation for sys.dm_tran_locks.

Answer 3

try_convert' is not a recognized built-in function name.

The function TRY_CONVERT was first introduce in SQL Server version 2012, when you get that error message, then because you are suing an older version.