Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the role of with (nolock) in sqlserver. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

When using NOLOCK and READPAST to handle a database deadlock exception in a query statement, one of the suggestions is to use NOLOCK or READPAST. Some technical knowledge points about NOLOCK and READPAST: for industries that require strict transactions, such as non-banks, the presence or absence of a record in search records is within the tolerable range, so when it comes to deadlocks, we should first consider whether our business logic can tolerate the occurrence of certain records or not, instead of seeking to unlock both parties. NOLOCK and READPAST are all about how to deal with locked data records when dealing with query, insert, delete and other operations. But at this point, be sure to pay attention to the limitations of NOLOCK and READPAST, and make sure your business logic can tolerate the presence or absence of these records:

To put it simply: NOLOCK may also display data that did not commit transactions. READPAST will not show the locked rows without using NOLOCK and READPAST, and it is possible to report an error during Select operation: the transaction (process ID *) and another process are deadlocked on the lock resource and have been selected as deadlock victims.

Let's demonstrate this situation in order to demonstrate two transaction deadlocks, our following tests need to open two query windows in SQL Server Management Studio. Ensure that the transaction will not be disturbed. Demonstrate an uncommitted transaction, NOLOCK and READPAST processing strategy: query window one, please execute the following script: CREATE TABLE T1 (C1 int IDENTITY (1 int) go BEGIN TRANSACTION insert T1 (c2) values (1) after query window 1 executes, query window 2 executes the following script: select count (*) from T1 WITH (NOLOCK) select count (*) from T1 WITH (READPAST)

Results and analysis: query window 2 shows the statistical results in turn: 1, 0 query window 1 command did not commit the transaction, so READPAST will not calculate the record that did not commit the transaction, this one is locked and READPAST can not see it; while NOLOCK can see the locked record. If we execute in query window 2 at this time: select count (*) from T1 will see that the execution cannot be completed for a long time because the query has encountered a deadlock. To clear the test environment, you need to execute the following statement in query window 1: ROLLBACK TRANSACTION drop table T1

Demo 2: NOLOCK and READPAST processing strategies for locked records this demo also requires two query windows. Please execute the following statement in query window 1: CREATE TABLE T2 (UserID int, NickName nvarchar (50)) go insert T2 (UserID,NickName) values (1 miner 'Guo Hongjun') insert T2 (UserID,NickName) values (2 mini 'Meijun') go BEGIN TRANSACTION update T2 set NickName = 'Yuejun .net' where UserID = 2

Execute the following script in query window 2: select * from T2 WITH (NOLOCK) where UserID = 2 select * from T2 WITH (READPAST) where UserID = 2

Results and analysis: in the second query window, we see the modified records in the query results corresponding to NOLOCK, but we do not see any records in the query results corresponding to READPAST. In this case, dirty reading may occur.

This is what the role of with (nolock) in sqlserver shared by the editor is. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.