Home » SQL Server Locks

SQL Server Locks

sql server DBA 999

SQL Server 2005/2008 Lock Escalations

Please read my previous post on SQL Server Locks and concurrency control before this….
Lock Escalations are there for a reason. For every lock taking place on SQL Server it takes up around 100 bytes approximately in memory in RAM. So, if we are holding 500,000 locks then it would be 47.68372 MB of memory used. This includes all types of locks.

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.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

•When locking rows or index key ranges, … Read the rest

sql server DBA 999

SQL Server 2008/2005 Concurrency control with locks

Today let us discuss more on SQL Server 20008/2005 Locks and concurrency control……

Locks are very essential to maintain concurrent data access without issues. Say you are trying to update some data row and someone else wants to read that data, to avoid phantom reads (inconsistent view of data or data which is not yet committed) a Exclusive lock is placed on the row. But it does not start nor end there. First thing when you issue a update statement is a lock is placed on database which is called intent exclusive (IX) then same IX on table with EX(Exclusive lock on table) once it available for you.
Some locks are compatible with each other like a simple select queries … Read the rest