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

will there be a table lock situation while deleting records from a partitioned table ? Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 5:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 2:21 AM
Points: 72, Visits: 182
HI

Consider the below scenario:

I Have a table with 5 partitions based on the org_id. In other words :
Table A

Partition org_id
1 100
2 101
3 102
4 103
5 104

Now if i execute 5 parallel delete statements based on org_id and some condition [gets satisfied within the partition] from 5 different threads, will there be a table lock generated by the first thread which will make the other 4 threads wait?

DELETE from
Post #1351529
Posted Wednesday, August 29, 2012 6:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 15,557, Visits: 27,931
Assuming the WHERE clauses of the delete statements place the processing squarely within the partition, no, I wouldn't expect to see a table lock. But, you never know. If your stats were out of date, it's possible that the system might decide that it has to span multiple partitions. In that case, you might see a table lock. Table locks, exclusive table locks, not shared ones, are pretty rare though.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1351546
Posted Wednesday, August 29, 2012 6:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 42,481, Visits: 35,550
Depends on how many rows get deleted. If SQL escalates the locks because of the number needed, it will escalate to table unless you enable partition-level escalation (careful if you decide to do that, it can cause amusing deadlocks)

Delete in small batches and you shouldn't have a problem with table locks.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1351553
Posted Thursday, August 30, 2012 10:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 529, Visits: 1,569
"Amusing" deadlocks?
Post #1352355
Posted Thursday, August 30, 2012 11:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 42,481, Visits: 35,550


Ones you can't get without turning that setting on, where you also wouldn't expect to even get a deadlock.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1352381
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse