SQL Server lock escalation issue

Question

Hello everyone,

I am reading SQL Server lock escalation from MSDN Page about SQL Server lock escalation

My question is, seems the major reason why there is lock escalation is to reduce overhead to maintain more locks (e.g. when more row locks are acquired for a table, then row level lock is escalated to table level). My question is, to maintain more locks will improve concurrency, it is a benefit, why it is an overhead? In my humble idea, lock should be as small as enough to make the db performance better by improving concurrency. Could anyone explain in simple way why lock escalation is needed and what is the so-called lock overhead please?

thanks in advance, George

Answer 1

the lock overhead means that managing one table lock is better perf wise than managing a lot of row locks. since each lock takes some memory, a lot of row locks can consume a lot more memory than one table lock. so lock escalation goes from row->page->table lock.

Answer 2

If the SQL Server optimiser estimates/decides that a query will 'visit' all the rows in a certain range it will be more efficient to hold a single lock over that range rather than to have to negotiate many locks (locks have to be tested for type). This is in addition to consuming less lock resources (a system wide resource).

If you have a well designed schema, and indexes appropriate to your query workload, which are being regularly maintained, you should not have to worry about the escalation that is occurring. In many instances, blocking table locks can be eliminated by the appropriate covering indexes.

UPDATE: A covering index for a query means that a lookup into the clustered will not need to be performed, and this reduces the chances of blocking inserts into the table.

Answer 3

The definition of "efficient" is complex. Sometimes it is more efficient to optimize for concurrency if lots of processes can do there thing without collisions. Sometimes it is more efficient to take a temporary concurrency hit to get a single process done faster. The escalated lock will keep other processes out so THIS process can get its job done and get out of the way.

Answer 4

Could anyone explain in simple way why lock escalation is needed and what is the so-called lock overhead please?

When you update a table and lock a row, you need to record this fact somehow: that's a row, it's been updated and locked.

When you update million rows, you need to do this million times, and therefore have some space to keep million locks.

SQL Server keeps a list of locks in memory, while Oracle does in on the tablespaces.

This is probably because Oracle is old (older than me), and SQL Server is young compared to Oracle.

Keeping temporary resources (like locks) in a permanent storage is not so obvious solution from designer's point of view.

Just to mention: you may need a disk write to perform a SELECT FOR UPDATE.

Oracle's core features were developed in early 80's, when keeping things in memory was not an option at all. They just had to use disk space somehow.

If disk space was to be used anyway, you had to place a lock somewhere on disk.

And where to keep a lock for a row if not within the row itself?

Developers of SQL Server's lock system, when inventing design of their RDBMS called Sybase, decided to store temporary things (i. e. locks) in the temporary storage (i. e. RAM).

But Oracle's design is always balanced: if you have a 1,000,000 rows in your database, then you have a storage space for 1,000,000 locks, if you have a billion rows, you may store billion locks, etc.

SQL Server's design is flawy in this sense, because your RAM and HDD space may be unbalanced. You may easily have 16M of RAM and several terabytes of disk space. And you memory just cannot hold all the locks.

That's why when the lock count reaches a certain limit, SQL Server decides to escalate the locks: instead of keeping locks for, say, 10 individual rows in a data page (which requires 10 records), it locks the whole data page (which requires 1 record).

Oracle, on the other hand, when updating a row, just writes the lock right into the datapage.

That's why Oracle's locks are row-level.

Oracle doesn't "manage" the locks in a common sense of word: you can't, say, get a list of locked pages in Oracle.

When a transaction needs to update a row, it just goes to the row and sees if it's locked.

If it is, it looks which transaction holds a lock (this information is contained within the lock descriptor in the data page) and adds itself to that transaction's notification queue: when the locking transactions dies, the original one gets notified and locks the data.

From the concurrency's point of view, lock escalation is totally a poor man's solution: it adds nothing to concurrency. You can, say, get a lock on a row you didn't even touch.

From the performance's point of view, doing things in memory is, of course, faster than doing them on the disk.

But since Oracle caches the datablocks and the actual operations described above are performed in memory anyway, the performance is same or close to it.

Answer 5

For specific info on how locks are maintained, you can see chapter 8 of Microsoft SQL Server 2005: The Storage Engine (I'm not affiliated, this is just the first internals info I came across). If you have a books24x7 account, it's on there. It shows on a >16gb memory machine there are 2^25 (33554432) slots in the lock hash table, with an upper limit of 2^31 slots.

For a given application you may very well find total throughput to be higher using only fine grained locks. As you can probably guess, it all depends on how the overhead of lock management compares to potential excessive locking.