Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Deleting records from the same table through different sessions Expand / Collapse
Posted Sunday, September 9, 2012 11:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 10, 2016 4:36 PM
Points: 75, 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 ?
Post #1356564
Posted Monday, September 10, 2012 1:12 AM


Group: General Forum Members
Last Login: Wednesday, January 21, 2015 3:47 AM
Points: 103, Visits: 164

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.
Post #1356578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse