August 29, 2012 at 5:44 am
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
August 29, 2012 at 6:10 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2012 at 6:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2012 at 10:25 am
"Amusing" deadlocks?
August 30, 2012 at 11:13 am
🙂
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply