The Database Engine typically acquires locks at multiple levels of granularity to fully protect a resource this process is described as the lock hierarchy. This means that SQL Server dynamically manages locking without any input needed from the user. Lock escalation is internally managed and decides at which point to move a set of locks to a higher granularity. SQL Server uses lock escalation to manage the locking granularity. A lock as an in-memory structure is 96 bytes in size, so locking millions of rows could have a high overhead compared to gaining a singular lock on the table. This overhead comes in the form of increased memory usage. Still, it has a higher overhead because a lock must be held on each row. Locking at a lower granularity, such as rows, increases concurrency, as access is only restricted to those rows rather than the whole table. SQL Server makes use of multigranular locking to try and minimize the cost of locking by allowing different types of resources to be locked by a transaction. The following table shows the resources that SQL Server can place locks on:Ī lock on an application-specific resourceĪ lock on anything that has an entry in sys.all_objects.
Now that we know that locks are essential for the basic functioning of a healthy server, let us have a look at the different resources that can be locked. In short, locks are there to protect resources. If a conflicting lock is held by another transaction, the query will be made to wait until the other lock is released.
The query processor then requests the locks from the lock manager, which grants them if no conflicting locks are held by other transactions. The locks acquired also depend upon the transaction isolation level setting (more on that in a future blog). Based on this information, it then determines what types of locks are required to protect each resource. When the database engine processes a statement, the query processor decides which resources need to be accessed and how they will be used.
This means that SQL Server must have measures in place to handle concurrency and prevent adverse side effects. In a multi-user system, there will be many users who wish to access the same resources at the same time. Let us make it clear from the off locks are an essential part of SQL Server. In this blog, I aim to give a basic answer to that question and provide you with an overview of the different lock modes in SQL Server and how it all works… We notice them more when something goes wrong and we run into blocking or other performance problems, but what are locks and how do they work in SQL Server? We have all heard of locks and can probably even name a few shared or exclusive locks, for example.
0 kommentar(er)
