SQL Server: Lock rows when insert/update data from xml using Merge Statement

Question

I am weak in sql server isolation and locking hint. i am working with sql server default isolation. i saw when we update a table then table is getting lock instead of row should be locked. i am updating/inserting data with in Begin Tran.

Please tell me best approach i should follow as a result during insert and update table should not be locked rather relevant rows should be locked.

1) Tell me how could i use lock hint during insert and update by merge statement?
2) Can i use any lock hint for insert statement?
3) what lock hint is used for delete data from table ?
4) What isolation i should use?

This is code by which data will be inserted and update into table from xml using merge statement.

Declare Xml Data Type and Assign Some Xml Data.  
Declare @Data xml  

set @Data=  
'<Root>  
<Student>  
<Name>Rakesh</Name>  
<Marks>80</Marks>  
</Student>  
<Student>  
<Name>Mahesh</Name>  
<Marks>90</Marks>  
</Student>  
<Student>  
<Name>Gowtham</Name>  
<Marks>60</Marks>  
</Student>  
</Root>'  
select @Data as StudentData  


Merge Statement usign Xml Data.  

Merge into Student as Trg  
Using (select d.x.value('Name[1]','varchar(20)') as Name ,  
d.x.value('Marks[1]','int') as Marks from   
@data.nodes('/Root/Student')as d(x)) as Src  
on Trg.Name=Src.Name  
When Matched Then update set   
 Trg.Marks=Src.Marks  
when not matched by target then   
 insert (Name,Marks) values(Src.Name,Src.Marks);  

Looking for suggestion. please guide me what lock hint i should use during insert/update statement in merge statement. thanks

Answer 1

Hi @Sudip Bhatt ,

In the merge statement, you can use delete to delete data from the table, and you have already got a solution in another thread. Different isolation levels have their own advantages and disadvantages. The more negative effects to prevent, the higher the isolation level required, and the worse the concurrency of the program. The most common is the default isolation level of sqlserver.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Rather that start looking for hints, you should first look at query plan and make sure that you have indexes to support your operation.

Answer 3

I suggest you read this:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms184286(v=sql.105)?redirectedfrom=MSDN

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15