The shared lock on a database level is imposed to prevent dropping of the database or restoring a database backup over the database in use. For example, when a SELECT statement is issued to read some data, a shared lock S will be imposed on the database level, an intent shared lock IS will be imposed on the table and on the page level, and a shared lock S on the row itself.
In case of a DML statement i. Locks will always be acquired from the top to the bottom as in that way SQL Server is preventing a so-called Race condition to occur. In order to prevent a situation where locking is using too many resources, SQL Server has introduced the lock escalation feature. Without escalation, locks could require a significant amount of memory resources.
Without escalation, a shared lock S will be imposed on the database, 1 intent exclusive lock IX on the table, 1, intent exclusive locks IX on the pages 8KB page hold 16 rows of bytes, which makes 1, pages that hold 30, rows and 30, exclusive locks X on the rows itself.
As each lock is 96 bytes in size, 31, locks will take about 3 MB of memory for a single delete operation. Running large number of operations in parallel could require some significant resources just to ensure that locking manager can perform the operation smoothly.
To prevent such a situation, SQL Server uses lock escalation. This means that in a situation where more than 5, locks are acquired on a single level, SQL Server will escalate those locks to a single table level lock. By default, SQL Server will always escalate to the table level directly, which mean that escalation to the page level never occurs. Instead of acquiring numerous rows and pages lock, SQL Server will escalate to the exclusive lock X on a table level.
While this will reduce the need for resources, exclusive locks X in a table mean that no other transaction will be able to access locked table and all queries trying to access that table will be blocked. Therefore, this will reduce system overhead but will increase the probability of concurrency contention.
Table — This is the default option for any newly created table, as by default SQL Server will always execute lock escalation to the table level, which also includes partitioned tables.
Auto — This option allows the lock escalation to a partition level when a table is partitioned. When 5, locks are acquired in a single partition, lock escalation will acquire an exclusive lock X on that partition while the table will acquire intent exclusive lock IX. In case that table is not partitioned, lock escalation will acquire the lock on the table level equal to the Table option.
Although this looks like a very useful option, it has to be used very carefully as it can easily cause a deadlock. In a situation where we have two transactions on two partitions where the exclusive lock X is acquired, and transactions tries to access the date from partition used by other transaction, a deadlock will be encountered. So, it is very important to carefully control the data access pattern, if this option is enabled, which is not easy to achieve, and this is why this option is not the default settings in SQL Server.
Disable — This option will completely disable lock escalation for a table. Again, this option must be used carefully to avoid the SQL Server lock manager to be forced to use an excessive amount of memory.
As it can be seen, lock escalation could be a challenge for DBAs. If the application design requires deleting or updating more than 5, rows at once, a solution to avoid lock escalation, and the resulting effects, is splitting the single transaction into a two or more transaction where each will handle less than 5, rows, as in this way the lock escalation could be evaded.
Note that only one lock can exist at a single point of time just similar to a person cannot be at 2 different places at the same time. So to issue exclusive lock to one process shared lock has to be removed first. Also only one process can be allowed to update the date on the table by issuing it a exclusive lock to avoid concurrency problems. For more info on concurrency problems refer —. To solve this confusion update lock is used. Once a update lock is issued to one of the above 3 process, it obtains exclusive lock immediately after 2 other process exit reading there by removing the shared lock placed on the data.
Hi Pinal, First of all a big thankyou….. Hi Pinal, Firstly thanks for this article. I have a requirement in which i have to read data from first table, then use this data to perform some query for another table, after the query executed successfully update the data of the first table.. What kind of lock are used in select and update query. Needed help..?? Thanks in advance.. He holds a Masters of Science degree and numerous database certifications. Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service.
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long term multi-channel campaigns to drive leads for their sales pipeline. Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check , we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future. SQL Lock. Shared and exclusive locks cannot be mixed.
If User1 has an exclusive lock on a record, User2 cannot get a shared lock or an exclusive lock on that same record. All locks within a particular category such as shared locks are equal. Some locks can be escalated. For example, if you are using a scroll cursor and you acquire a shared lock on a record, and then later within that same transaction you update that record, your shared lock may be upgraded to an exclusive lock. Getting an exclusive lock is only possible if there are no other locks shared or exclusive on the table; if you and another user both have shared locks on the same record, then the server cannot upgrade your shared lock to an exclusive lock until the other user drops her shared lock.
By default, optimistic and pessimistic tables are locked in shared mode; unless you are altering the table, the locks on tables are usually shared locks. That allows other users to continue to read data from the table, but prevents them from making changes to the table. By default, a lock is held from the time it is acquired until the end of the transaction completed with commit or rollback.
If you try to get an exclusive lock on a record that another user has already locked shared or exclusive , you cannot get a lock; instead, your transaction will fail with an error. This is controlled with a lock timeout setting. The lock timeout setting is the time in seconds that the engine waits for a lock to be released. If transactions tend to be very short, a brief wait allows you to continue activities that otherwise would have been blocked by locks.
If the query of User1 is completed and the User2 query has not timed out yet, a lock is issued for the update transaction of User2. The default lock timeout is controlled with the General.
LockWaitTimeOut parameter. In advanced replication setups, you might also want to set the default lock time for table-level locks with the General. TableLockWaitTimeout parameter. The default timeout can be overridden with the following transaction or section specific commands:.
0コメント