InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 09:45:39 GMT20http://www.sqlservercentral.com/Forums/Topic1399620-361-1.aspx[quote][b]GilaMonster (12/26/2012)[/b][hr][quote][b]Sean Lange (12/26/2012)[/b][hr]The locking you are seeing is because of lock escalation. [/quote]It's not lock escalation.Truncate table locks at the table level because there's no other sensible level to lock at (and iirc it takes an Sch-M lock too)Lock escalation would be if the truncate first started taking row or page locks, got over the threshold and converted the locks to table[/quote]Thanks for the correction Gail. I knew the locking was at the table level but my incorrect terminology certainly made it unclear. :w00t:Wed, 26 Dec 2012 13:07:33 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1399620-361-1.aspx[quote][b]Sean Lange (12/26/2012)[/b][hr]The locking you are seeing is because of lock escalation. [/quote]It's not lock escalation.Truncate table locks at the table level because there's no other sensible level to lock at (and iirc it takes an Sch-M lock too)Lock escalation would be if the truncate first started taking row or page locks, got over the threshold and converted the locks to tableWed, 26 Dec 2012 13:04:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1399620-361-1.aspx[quote][b]geoffrey crombez (12/21/2012)[/b][hr]Hi,when I use the READ_COMMITTED_SNAPSHOT and use DELETE FROM table the other session is not locked when SELECTING data.However when I use TRUNCATE TABLE it does block the other session SELECTING data.The difference I see is that the TRUNCATE does a X lock on PAGE level and the DELETE an IX on PAGE level.So I know not to use TRUNCATE in such scenario, but I would like to understand what is different internally.Kind RegardsGeoffrey[/quote]The difference in what those two statements do. Delete simply deletes rows where a truncate removes the pages. The locking you are seeing is because of lock escalation. A truncate will lock the entire table because all of the pages are being removed. Of course truncate is a lot faster because of the minimal logging involved so the locking should be very short.Wed, 26 Dec 2012 07:31:48 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1399620-361-1.aspxHi,when I use the READ_COMMITTED_SNAPSHOT and use DELETE FROM table the other session is not locked when SELECTING data.However when I use TRUNCATE TABLE it does block the other session SELECTING data.The difference I see is that the TRUNCATE does a X lock on PAGE level and the DELETE an IX on PAGE level.So I know not to use TRUNCATE in such scenario, but I would like to understand what is different internally.Kind RegardsGeoffreyFri, 21 Dec 2012 15:45:46 GMTgeoffrey crombez