Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Sunday, September 09, 2012 11:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, March 08, 2014 5:07 PM
Points: 72, Visits: 180
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:02 AM
Points: 103, Visits: 151
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.
Post #1356578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse