Best locking hint for Insert & Delete statement

Question

Best locking hint for Insert & Delete statement

Sudip Bhatt 2,281

I know that when i issue update statement then i can mention UPDLock hint but i am curious to know what locking hint i should use for Insert & Delete statement ?

i want to issue insert & delete statement in such a way as a result full table should not be locked and as a result another user from another session can insert, update & delete data in that particular table.

please tell me what locking hint i should use for Insert & Delete statement ?

What isolation would be best during insert, update & delete as a result relevant rows will be locked and from other session a user can query data from same table and also will be able to perform DML operation.

please guide me in details with a example and scenario. thanks

Accepted answer

2 additional answers

Your answer

Answer 1

Hi @Sudip Bhatt ,

You could use READCOMMITTED which is the default table hint in SQL Server.

Normally you should not need to add any hint to a query, because the database knows what kind of lock to use. It's only in situations where you get performance problems because the database made the wrong decision, that you should add a hint to a query.

You could consider to use with (rowlock) which is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope.

You use the hint when only a single or only a few rows will be affected by the query, to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.

A simple example:

DELETE TOP (10)  
FROM mytable WITH (ROWLOCK)  
WHERE id>10

Best regards
Melissa

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

Sudip Bhatt 2,281 Reputation points

2020-11-19T17:28:17.937+00:00

1) When i will use RowLock hint then should not i mention a isolation to set like

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ DELETE TOP (10) FROM mytable WITH (ROWLOCK) WHERE id>10

without setting isolation ROWLOCK hint will work properly ?

2) When i am inserting data into table like Insert into Table1(id,Name) Values(1,'Test') with in Begin Tran and Commit Tran then sql place what kind of lock when i am working with default isolation called Read Committed ?

3) in case of Inserting data into table with Begin Tran and Commit Tran locking entire table for which other user has to wait when querying data from that table. so how could i place a only locks on rows for Insert / Update?

4) i search google just to how to mention lock during Insert data into table but found no write up. does it means that we can not mention lock for insert statement ?

Sir please answer for my 4 points point wise. thanks

Answer 2

1) When i will use RowLock hint then should not i mention a isolation to set like

Only if you know what you are doing. And I'm afraid that you don't. And in most cases, it's not a very good idea to specify the ROWLOCK hint, but it's better to let SQL Server decide the granularity.

2) When i am inserting data into table like Insert into Table1(id,Name) Values(1,'Test') with in Begin Tran and Commit Tran then sql place what kind of lock when i am working with default isolation called Read Committed ?

If you insert an occasional row, you are likely to get an exclusive lock on row level. No matter the isolation level.

3) in case of Inserting data into table with Begin Tran and Commit Tran locking entire table for which other user has to wait when querying data from that table. so how could i place a only locks on rows for Insert / Update?

Normally, an INSERT does not lock the entire table, that would only be if you insert lots of data.

In any case, if the database is READ_COMMITTED_SNAPSHOT, readers and writes do not block each other.

4) i search google just to how to mention lock during Insert data into table but found no write up. does it means that we can not mention lock for insert statement ?

The INSERT statements accepts lock hints for granularity. Check the Hints section in Books Online.

Sir please answer for my 4 points point wise. thanks

I don't who that was directed to, but since you posted it in reply to Melissa, I think "Lady" or "Madam" would have been more appropriate than "Sir".

Sudip Bhatt 2,281 Reputation points

2020-11-20T13:55:07.593+00:00

please tell me lock hint for Insert Statement because i am inserting data with in loop and before that loop there is Begin Trand and after loop end there is Commit Tran.

so entire table gets locked

so please tell me what lock hint i should use for Insert Statement. i am using default isolation called Read Committed.

Thanks
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-11-20T22:17:15.97+00:00

You keep on asking the wrong question. You should not not use any locking hints at all. You should fix the read problem which is your loop. If you insert all rows at once, the operation will run faster, and the period that other processes are blocked will be a lot shorter.

If you cannot figure out how to convert your loop into a set-based operation, write data into temp tables while your process your XML (I assume it is that XML operation you have posted a few times before) and insert data from the temp table to the target table at the end.

Again: YOU SHOULD NOT USE ANY LOCK HINTS AT ALL!

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

Impossible to answer with the amount of information you have given. We would need to see table definitions (including indexes!) and the statements you are using.

But the best starting point is to not use any hints at all.

Share via

Best locking hint for Insert & Delete statement

2 additional answers

Your answer