Avoiding Deadlock Transaction Errors by Using ROWLOCK Hint in T-SQL

• When updating a single row data in a table, it may take a nasty while if the table is big and you have a condition in a WHERE clause on a column that is not indexed. In this case SQL Server will have to scan all rows in order to figure out which records to update. The update statement will lock the table and if other statements are executed at that time, you very well are likely to start getting deadlock errors.
• To avoid this unfortunate scenario, you can use the ROWLOCK hint on your UPDATE queries. The hint will override locking the whole table and instead will only lock the row that is being updated. 

for example:

create table hints

    ( id int identity(1,1) primary key,

      name varchar(30),

      age int,

      valid_bit bit

    )

create proc get_hint_details

  (@age int)

As 

BEGIN 

update a

  valid_bit=0

  hints a where a.age=@age

END 

• Adding an index on Age will certainly speed up things, but the whole table will still be locked while SQL Server looks for matching records, and if the table is big enough you are likely to still get deadlock errors. Instead of locking the whole table, you can hint the server to lock only those rows that match the criteria with ROWLOCK hint. This will allow your code to execute statements on rows that are not affected by the UPDATE query. However, if you are updating a large number of rows as in the example above assuming a billion records, you will probably still get deadlocks and may consider different approaches. To lock only the rows the query is affecting, the stored procedure above can be modified as follows:

create proc get_hint_details

  (@age int)

As 

BEGIN 

update a WITH(rowlock)

  valid_bit=0

  hints a where a.age=@age

END 

NOTE:

---> finally we  use this rowlock hint in both delete and update statements.for example if you use so many joins while delete and update table that time we should use rowlock hint to what table will be delete or update to the query and remaining tables use NOLOCK hint.