SQL Server Lock Escalation-lock upgrade

Articles

1. Locking in Microsoft SQL Server (part 12–lock escalation)
2. Http://dba.stackexchange.com/questions/12864/what-is-lock-escalation
3. R2 Lock escalation (Database Engine)

---Forward from Locking in Microsoft SQL Server (part 12–lock escalation)

Today I ' d like us to talk about Lock escalation in Microsoft SQL Server. We'll cover:

1. What is Lock escalation?
2. How Lock Escalations affects the system
3. How to detect and troubleshoot Lock Escalations
4. How to disable Lock escalation

What is Lock escalation?
All of us know this SQL Server uses row level locking. Let's think about scenario when system modifies the row. Let's create the small table and insert 1 row there and next check the locks we have. As usual every image is clickable.

As can see there is 4 locks in the picture. Shared (S) lock on the database–e.g. Indication. Intent Exclusive (IX) lock on the table (OBJECT) –e.g. Indication, the child objects of Row/key The exclusive lock. Intent Exclusive (IX) lock on the page–e.g. Same indication about child object (Row/key) exclusive lock. and finally exclusive (X) lock on the key (row) we just inserted.

Now let's insert another row in the different session (let ' s keep the original session 1 transaction uncommitted).

When we check the locks we'll see that there is 8 locks–4 per session. Both sessions ran just fine and don ' t block each of the other. Everything works smooth–that great for the concurrency. So far so good. The problem though is a every lock takes some memory space–128 bytes on the "bit OS" and "bytes on" OS). and memory is not the free resource. Let's take a look at another example. I ' m creating the table and populating it with 100,000 rows. Next, I ' m disabling the lock escalation on the table (ignore it for now) and clear all system cache (don ' t do it in PRODUC tion). Now let's run the transaction in repeatable read isolation level and initiate the table scan.

Transaction is isn't committed and as we remember, in repeatable READ isolation level SQL Server holds the locks till end of Transaction. And now let's see how many locks we had and how to much memory does it use.

As can see, now we have the 102,780 lock structures that takes more than 20MB of RAM. And what if we had a table with billions of rows? This was the case when SQL Server starts to use the process that called "Lock escalation" –in nutshell, instead of keeping Locks on every row SQL Server tries to escalate them to the higher (object) level. Let's see how it works.

First we need to commit transaction and clear the cache. Next, let's switch lock escalation for Data table to AUTO level (I'll explain it in details later) and see what'll happe N if we re-run the previous example.

As can see–just 2 locks and only 1Mb of RAM are used (Memory clerk reserves some space). Now let's look what locks does we have:

As can see there are the same (s) lock on the database and now we have the new (s) shared lock on the table. No locks on page/row levels is kept. Obviously concurrency is not as good as it used to be. Now, for example, and sessions would not being able to update the data on the table– (S) lock are incompatible with (IX) on The table level. And obviously, if we have a lock escalation due data modifications, the table would hold (X) exclusive Lock–so other Sessi ONS would not being able to read the data either.

The next question is when escalation happens. Based on the documentation, SQL Server tries to escalate locks after it acquires at least 5,000 locks on the object. If escalation failed, it tries again after at least 1,250 new locks. The locks count on index/object level. So if Table has 2 indexes–a and B you have 4,500 locks on the index A and 4,500 locks on the index B, the locks would no t be escalated. In real life, your mileage may vary–see example below–5,999 locks does isn't trigger the escalation but 6,999 does.

How does it affects the system?

Let's re-iterate our first small example on the bigger scope. Let's run the first session that updates $ rows and check what locks is held.

As you see, we have intent Exclusive (IX) locks on the object (table) and pages as well as various (X) locks on the rows. If we run another session, the updates completely different rows everything would be just fine. (IX) Locks on table is compatible. (X) Locks is not acquired on the same rows.

Now let's trigger lock escalation updating 11,000 rows.

As can see–now the table has exclusive lock. If you run the Session 2 query from above again, it would be blocked because (X) lock on the table held by session 1 is Incompatible with (IX) lock from the session 2.

When it affects us? There is 2 very specific situations

1. Batch inserts/updates/deletes. You ' re trying to import thousands of the rows (even from the stage table). If your import session is lucky enough to escalate the lock, neither of other sessions would being able to access the table T Ill transaction is committed.
2. Reporting–if you ' re using repeatable READ or SERIALIZABLE isolation levels in order to has data consistent in reports, You can have (S) lock escalated to the table level and as result, writers would be blocked until the end of transaction.

And of course, any excessive locking in the system can trigger it too.

How to detect and troubleshoot Lock Escalations

First of all, even if you had the lock escalations it does not mean that it's bad. After all, this is expected behavior of SQL Server. The problem with the lock escalations though was that usually customers was complaining that some queries was running slow. In this particular case waits due lock escalations from other processes could is the issue. If We look at the example above when session 2 was blocked, and run the script (as the session 3) that analyzes Sys.dm_tran _locks DMV, we ' d see that:

I ' m very heavy on the wait statistics as the first troubleshooting tool (perhaps heavier than I need to be). One of the signs of the issues with lock escalations would is the high percent of intent lock waits (lck_m_i*) together WI Th relatively small percent of regular non-intent lock waits. See the example below:

If the system has a high percent of both intent and regular lock waits, I ' d focus on the regular locks first (mainly Check if queries is optimized). There is the good chance, intent locks is not related with lock escalations.

In addition to DMVs (Sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_os_wait_stats, etc), there is Lock escalation Pro Filer event and Lock escalation extended event you can capture. You can also monitor performance counters related with locking and create the baseline (always the great idea)

Last but not least, look at the queries. As I mentioned before in more part of the cases excessive locking happen because of non-optimized queries. And that, of course, can also trigger the lock escalations.

How to disable Lock escalation

Yes, you can disable Lock Escalations. But it should is the last resort. Before you implement this, please consider other approaches

1. For data consistency-reporting (repeatable read/serializable isolation levels) –switch to optimistic (Read Committed snapshot, snapshot) isolation Levels
2. For batch operations consider to either change batch size to be below 5,000 rows threshold or, if it's impossible, you can Play with lock compatibility. For example, the another session, Aquires is lock, the table while importing data. Or use partition switch from the staging table if it ' s possible

If neither option works for your please test the system before you disable the lock escalations. So:

For both SQL Server 2005 and your can alter the behavior on the instance level with Trace Flags 1211 and 1224. Trace Flag 1211 Disables the lock escalation in every cases. In case, if there is no available memory for the locks, the error 1204 (unable to allocate lock resource) would be genera Ted. Trace Flag 1224 would disable lock escalations in case if there are no memory pressure in the system. Although locks would is escalated in case of the memory pressure.

With SQL Server 2005 trace flags is the only options that you have. With SQL Server (also) specify escalation rules on the table, with ALTER table SET lock_escalation stateme Nt. There is 3 available modes:

1. Disable–lock escalation on specific table is disabled
2. TABLE (default) –default behavior of lock Escalation–locks is escalated to the table level.
3. Auto–if table is partitioned, locks would being escalated to partition level when table was partitioned or on table level if Table is not partitioned

SQL Server Lock Escalation-lock upgrade