September 9, 2012 at 11:54 pm
Hi ,
Consider the below scenario :
I have a huge table where records are present based on organizations [org_id]
Each org can have multiple records.
Now I want to delete records for more than 1 org , and the way I want to delete is through different sessions,
1 session per org.
Will this cause a deadlock ?
The way I understand is, SQL Server will escalate row locks to table locks if the number of records to be deleted is huge.
Now in my case, 1 org may have huge number of records, while the others may not have that many .
Is there a chance of getting into a table lock here , and the other 2 sessions have to wait till the table lock is release ?
September 10, 2012 at 1:12 am
Hello,
It might acquire table locks but not necessarily end up in a deadlock situation. You might want to delete records in batches for each organization and so that blocking is minimal and delete from both sessions would run parellely or if your tables are partitioned based on the org_id, then partition switch will be a better solution. It is all trial and error so before you arrive at a conclusion please try the solution that you think is feasible.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply