How to force sql server to lock rows instead of lock full table during Insert Statement

Question

When i am inserting with in Begin TRAN & Commit Tran then SQL server locking full table but i want to pass instruction to sql server to lock rows instead of placing locking on full table.

BEGIN TRAN

INSERT INTO Table1(ID,Name) VALUES (1,'Test')

COMMIT TRAN

please guide me what to change in code. Thanks

Answer 1

Hi @Sudip Bhatt ，

When performing an insert operation, the database does not lock the entire table, but locks the row. The following is the test I did, you can see that only the row is locked(X lock) when inserting data, and there is only an Intent lock(IX lock) on the table：

    use test  
    dbcc useroptions  
    create table t1(id int, cname char(4))  
    begin tran  
    insert t1 values (1,'c1')  
    commit tran  

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