Understanding SQL Server Lock contention: Secrets of NOLOCK and Rowlock

relational databases, such as SQL Server, use locks to avoid concurrency conflicts when multiple users modify data. When a set of data is locked by a user, other users cannot modify the group's data unless the first user finishes modifying and releasing the lock.

Some databases, including SQL Server, use locks to prevent users from retrieving records of changes that have not been submitted. In these systems, if user a modifies a set of records, the other user can retrieve only if user A has finished modifying it.

The database sets the locks on each physical layer: Record rows (rows), data pages (pages, millions of rows of records), extended pages (extends, multiple data pages), entire tables, and even entire databases. Some databases (such as Oracle, etc.) use only fine-grained locking mechanisms, while other databases use a large range of lock mechanisms on pages, extended pages, tables, and databases. Most databases, including SQL Server, also support the row-locking mechanism, but often use a large-scope locking mechanism. This is largely due to the high cost of managing locks. Locks are complex and numerous, so it would be extremely painful if they were all row locks: a 1 million-row data update would easily consume huge amounts of memory, making it impossible to manage at all.

Description of Lock contention

Databases that do not just use row-level locks use a technique called the Escalation Lock (lock) to obtain higher performance. Unless it is clear that it is for the entire data table, the practice of these databases is to start using row-level locks and then start using a wide range of locking mechanisms as the modified data increases.

Unfortunately, this method of blending locks produces and amplifies new problems: deadlocks. If two users modify records in a different table in reverse order, and the two records are logically unrelated, but physically adjacent, the operation will first raise the row lock and then upgrade to a page lock. In this way, two users are required to lock something, resulting in a deadlock.

For example:

User A modifies some records of table A, and the resulting page lock locks not only the record being modified, but also many other records.

User B modifies some records of table B, and the page lock that is raised locks user A and other data that is being modified.

User A wants to modify the data that User B locks (and is not necessarily modifying) in table B.

User B wants to modify or simply want to access data that user a locks in table A (and is not necessarily being modified).

In order to solve this problem, the database will often detect the existence of a deadlock, if any, one of the transactions to be revoked, so that the other transaction can be completed successfully. In general, it is a matter of undoing the small amount of modified data so that the rollback is less expensive. A database with row-level locks rarely has this problem because two users are less likely to modify the same record at the same time, and have fewer locks due to the very accidental order of data modification.

Also, the database uses lock timeouts to avoid allowing users to wait too long. The introduction of query timeouts is also for the same purpose. We can re-submit those queries that have timed out, but this will only cause a blockage in the database. If a time-out occurs frequently, There is a problem with how the user uses SQL Server. The normal situation is that there are few timeouts.

In a running environment with high server load, the SQL Server lock mechanism using hybrid locks does not perform well. The reason for this is lock contention (lock contention). Lock contention causes deadlock and lock wait issues. In a multi-user system, many users will be in the database at the same time, there are more users at the same time access to the database, at any time to generate locks, users are scrambling to acquire locks to ensure that their operation is correct, deadlock occurs frequently, in this case, the user's mood is conceivable.

Indeed, if there are only a handful of users,SQL Server will not experience much trouble. When it comes to internal testing and publishing, it's hard to spot concurrency problems due to fewer users. But when you're excited about hundreds of concurrent, continuous insert,update, and some delete operations, how to see if there's trouble, then you'll have to scramble to read Oracle's literature. But I have a solution, the method only needs to check your T-SQL code, few adjustments and system tests. Use this method to teach you the proper system testing process.

Workaround for lock contention

If you visit streamload.com between June and August this year, you may see errors such as "Encounter Deadlock", "Lock timeout", "need object" and so on. These errors are caused by lock contention. Having consulted a large number of documents and discussions, I have learned this knowledge, which is what is discussed above, and I repeat the following:

SQL Server starts with row-level locks, but often expands to page and table locks, resulting in deadlocks.

SQL Server encounters a lock when a select is not modified, even if the user does not modify the data. Fortunately, we can do this manually with two keywords from SQL Server: Nolock and Rowlock.

They are used in the following ways:

SELECT COUNT(UserID)
From the Users with (NOLOCK)
WHERE Username like ' foobar '

And

UPDATE Users with (rowlock)
SET Username = ' Fred ' WHERE Username = ' foobar '

Use of Nolock

Nolock can ignore locks and read data directly from the database. This means that locks can be avoided, which improves performance and scalability. But it also means that there is a possibility of code errors. You may read uncommitted data that is being processed by the running transaction without validation. This risk can be quantified.

If it's a financial code or some unconventional total (you want to be absolutely secure), you should be careful and not use this technique. But I think the use of this technology will be better than your 90% application system performance, when the user (or interactive code) to find a non-submitted changes, the use of technology will ensure that the technology does not use the same as not to cause big trouble. In fact, you may find that most of your data is rarely or even unmodified, so that we don't waste a lot of time because the data is locked.

For example, if you want to count all users who joined streamload.com between June 2000 and August, there is no reason to lock any records: September 1, 2000, the number of users is determined. Another example is to enumerate the list of files in streamload.com: This is not a big problem, even if it is not 100% correct. Because you either don't own the file, of course it doesn't matter if you can find it, or if you do have the file, in which case you know if you've modified the file, and if the file has been uploaded.

However, if the data is modified, it is a fundamental modification to the database, or the data must be absolutely guaranteed to be correct for the user (such as billing or balance data), then you should not use the technology.

Use of Rowlock

Rowlock tells SQL Server to use only row-level locks. The Rowlock syntax can be used in select,update and DELETE statements, but I am accustomed to using only the update and DELETE statements. A row-level lock is always raised if there is a specified primary key in the UPDATE statement. However, when SQL Server batches several of these updates, some data is exactly on the same page (page), which is likely to happen in the current situation, as in a directory where it takes a long time to create files, while you are updating those files. When a page lock is thrown, things start to get worse. If you do not specify a primary key when you update or delete, the database will of course think that a lot of data is affected, and then the page lock is thrown directly, and things get worse.

This can be avoided by specifying the use of row-level locks. However, be careful that if you mistakenly use on too many rows, the database is not smart enough to automatically upgrade row-level locks to page locks, and the server consumes a lot of memory and CPU because of the overhead of row-level locks until it is unresponsive. In particular, the "manage/Current Activity" (Management/current activity) item in Enterprise Manager is the main attention. This item takes a long time to load the lock information. This information is useful when you use a row-level lock, if you see hundreds of locks under "lock/Process" (locks/processes), it is not surprising, but fortunately the lock timeout and deadlock problem is reduced.

Precautions

I think SQL Server prefers to use the NOLOCK keyword, while the Rowlock keyword is determined by the user as appropriate. You can use NOLOCK only in the SELECT statement, where the SELECT statements include the inner query and the select Use in the INSERT statement, which can also be used under a connection query, for example:

SELECT COUNT(Users.userid)
From the Users with (NOLOCK)
JOIN usersinusergroups with (NOLOCK) on
Users.userid = Usersinusergroups.userid

NOLOCK and Rowlock Use effect

It's hard to quantify how much streamload.com or your website's performance has improved after using NOLOCK and Rowlock. However, before using NOLOCK and Rowlock, the streamload.com is very slow, often unusable, and very unstable. After use, it becomes fast, easy to access and stable. The two are simply worlds apart. These changes are certainly not difficult to find in the document about locks. Those documents will suggest that you rewrite your app, that when the table data is used, the lock is generated (yes, that's it), then you should use small transactions and execute it in batches (good, real experience is the case), using low-level isolation measures (and yes, NOLOCK is an extreme example), It is also recommended that you have a limited connection to allow the processor to work together (a very complex description, and always feel strange as a good idea). I don't know if the database consultant will mention the techniques (or similar techniques) in this article, but I just want to say that Streamload.com's health is really improving because of the technology. If you encounter a problem with lock contention, you can also try Nolock and Rowlock.

Statement

The use of nolock and rowlock requires self-judgment and careful use. I use this technique by looking at my stored procedures and instant query statements, where I feel and how to use them in my own understanding. I need to decide if it is acceptable for a visitor or user to use Nolock to cause some return inaccuracies, or whether rowlock will cause too many locks to occur. In most cases, I don't think it's a problem, but maybe your code doesn't work and you need to be careful. You need to create some independent process, whether lock, how to lock, as a contrast. When an update or delete query affects many rows of data, you also encounter other problems when you use Pagelock,tablock.

Attached:---------------UPDLOCK uses an update lock when reading a table instead of a shared lock and retains the lock until the end of the statement or transaction.　　The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data at a later time, while ensuring that the data has not been changed since the last time the data was read.　　This is a description of the update lock in SqlServer2000. When we use Updlock to read the record, we can add the update lock to the record, and the record of the lock cannot be changed in other threads but only after the end of the transaction of this thread, I have the following example: Begin TRANSACTION--Start a transaction
SELECT Qty
From MyTable with (UPDLOCK)
WHERE Id in (All-in-a-
UPDATE myTable SET Qty = Qty-a.qty
From MyTable as A
INNER JOIN @_table as B on a.id = b.ID
Commit TRANSACTION-commits a transaction so that other threads or transactions cannot change the record of the ID when the statement is completed before the execution of these statements is complete. The other can be modified and read, the only read-only, if modified can only wait until these statements are completed before they can be manipulated. This ensures that the data is modified correctly.

Understanding SQL Server Lock contention: Secrets of NOLOCK and Rowlock