SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting records from the same table through different sessions


Deleting records from the same table through different sessions

Author
Message
koustav_1982
koustav_1982
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 194
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 ?
suba.sathyanathan 40131
suba.sathyanathan 40131
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 164
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search