will there be a table lock situation while deleting records from a partitioned table ?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » will there be a table lock situation while...

will there be a table lock situation while deleting records from a partitioned table ?

Rate Topic

Display Mode

Topic Options

Author

Message

koustav_1982

Posted Wednesday, August 29, 2012 5:44 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 1:39 AM
Points: 70, Visits: 174

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

Grant Fritchey

Posted Wednesday, August 29, 2012 6:10 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 13,371, Visits: 25,143

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

GilaMonster

Posted Wednesday, August 29, 2012 6:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 37,648, Visits: 29,899

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

dan-572483

Posted Thursday, August 30, 2012 10:25 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 328, Visits: 939

"Amusing" deadlocks?

Post #1352355

GilaMonster

Posted Thursday, August 30, 2012 11:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 37,648, Visits: 29,899

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 »

Permissions