\nThe theory<\/strong><\/p>\n
According to Books Online<\/a><\/p>\n Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention<\/p><\/blockquote>\n And for non partitioned tables also states that <\/p>\n Lock escalation is triggered when lock escalation is not disabled on the table and a single Transact-SQL statement acquires at least 5,000 locks<\/p><\/blockquote>\n That in English means that if you try to update too many rows at once, you will lock the whole table until your updates finish. <\/p>\n Obviously in that process, no one will be allowed access that table. If you have users working while you’re planning to UPDATE thousands of rows, that is something to always keep in mind.<\/p>\n In a simple scenario, we can say that the threshold would be close to that number, although, that number can vary depending on other factors like available memory on the server. However for this example I’ll be alone in the server, so the threshold shouldn’t be much different than that stated in BOL.<\/p>\n First let me show you this phenomenon. <\/p>\n Open a new tab in SSMS and run<\/p>\n We can observe how SQL Server will stop anyone from accessing those rows, open another tab, and run <\/p>\n So we can see how SQL Server locks each row individually<\/p>\n Also if we try to access those rows, we will have to wait, otherwise we are free to access other rows not affected by the transaction. If we run <\/p>\n We will have to wait until the current transaction ends by a COMMIT or a ROLLBACK.<\/p>\n But if we pick some other rows, there is no problem at all and rows are returned.<\/p>\n Now it’s when we push it a bit further to see how LOCK ESCALATION happens; I’d love to have a magic number that always works, but as I said, it varies<\/p>\n I’ll play it safe by going a bit bigger than 5000 Updates, so you will see how rows are not locked anymore but the table is.<\/p>\n First let’s ROLLBACK our transaction and open a new one<\/p>\n Now if we switch to our other tab and run again<\/p>\n See there is an eXclusive lock on the object, not on each individual row anymore, which means no other user can access the entire object while the transaction is running, and that my friends, means downtime.<\/p>\n Any attempt to access the table will have to wait until the open transaction finish. You can see the output of sp_WhoIsActive<\/a> if I try to do a SELECT<\/p>\n
\nHow it happens<\/strong><\/p>\n\r\nUSE AdventureWorks2014 \r\n\r\nBEGIN TRAN\r\n\r\nUPDATE TOP(100) Person.Address\r\nSET AddressLine1 = AddressLine1\r\n<\/pre>\n
\r\nUSE AdventureWorks2014\r\n\r\nSELECT * FROM sys.dm_tran_locks\r\n<\/pre>\n
![\"01_hundred_locks\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/04\/01_hundred_locks.png\")
<\/a><\/p>\n\r\nUSE AdventureWorks2014\r\n\r\nSELECT TOP 100 * FROM Person.Address\r\n<\/pre>\n
\r\nUSE AdventureWorks2014\r\n\r\nSELECT TOP 100 * FROM Person.Address\r\nORDER BY AddressID DESC\r\n<\/pre>\n
![\"02_SELECT_TOP_100_DESC\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/04\/02_SELECT_TOP_100_DESC.png\")
<\/a><\/p>\n\r\nUSE AdventureWorks2014 \r\n\r\nROLLBACK \r\n\r\nBEGIN TRAN\r\n\r\nUPDATE TOP(5500) Person.Address\r\nSET AddressLine1 = AddressLine1\r\n<\/pre>\n
\r\nUSE AdventureWorks2014\r\n\r\nSELECT * FROM sys.dm_tran_locks\r\n<\/pre>\n
![\"03_Table_LOCKED\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/04\/03_Table_LOCKED.png\")
<\/a><\/p>\n
![\"04_sp_WhoIsActive\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2016\/04\/04_sp_WhoIsActive.png\")
<\/a><\/p>\n