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

This article mainly introduces "how to extract SQLServer dirty reading data". In daily operation, I believe many people have doubts about how to extract SQLServer dirty reading data. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to extract SQLServer dirty reading data". Next, please follow the editor to study!

When designing database applications, you should keep in mind the different types of locks and the different isolation levels at which transactions occur. In general, the default mode of SQLServer does a good job of doing what you want to use, but sometimes it is useful to use SQL statements to manually add tips on how locks are applied to the data table.

This article mainly introduces two kinds of data table hints: NOLOCK and READPAST. We will create a datasheet to use as the query datasheet in the example. Execute the script in listing A to create an SalesHistory data table and add some data.

NOLOCK

This datasheet hint, also known as READUNCOMMITTED, can only be used for SELECT statements. NOLOCK indicates that no shared lock has been added to the data table to prevent other transactions from modifying the data.

The advantage of this statement is that it eliminates the need for the database engine to handle locking problems in queries, improves concurrency and improves database performance, because the database engine does not have to maintain the use of shared locks. The problem is that because the statement cannot handle all the locks of the data table to be read, some "dirty data" or uncommitted data may potentially be read.

If a transaction is rolled back, a data read operation with an NOLOCK connection applied will be able to read uncommitted data. This type of reading leads to inconsistencies in processing, which can cause a lot of problems. This is a technique you should know when using NOLOCK.

As a negative effect, NOLOCK queries can also pose the risk of reading "phantom" data or reading data that is available in one database read transaction but may be rolled back in another transaction. (I will explain this negative impact in detail in the second part of this series.)

The following example shows how NOLOCK works and how dirty data reading is produced. In the following script, I use a transaction to insert a record in the SalesHistory data table.

How to extract NOLOCK and READPAST from SQLServer dirty reading data

BEGINTRANSACTIONINSERTINTOSalesHistory (Product,SaleDate,SalePrice) VALUES ('PoolTable',GETDATE (), 500)

This transaction is still open, which means that records inserted into the data table can still be locked to prevent other operations. In a new query window, run the following script that uses the NOLOCK data table prompt to return the number of records in the SalesHistory data table.

SELECTCOUNT (*) FROMSalesHistoryWITH (NOLOCK)

The returned record value is 301. Because the transaction that inserted the record into the SalesHistory data table has not been committed, we can undo it. I roll the transaction back by using the following statement:

ROLLBACKTRANSACTION

This statement deletes the previously inserted record from the SalesHistory data table. Now let's run the same SELECT statement we ran earlier.

SELECTCOUNT (*) FROMSalesHistoryWITH (NOLOCK)

The value of the number of records returned this time is 300. The transaction that I first queried to read the record has not yet been committed, which is a dirty data read.

READPAST

This is a datasheet hint that is less used than NOLOCK. This prompt indicates that the database engine ignores locked rows or data pages when returning results.

The advantage of this datasheet hint is the same as that of NOLOCK, which does not block when processing queries. In addition, reading dirty data does not appear in the READPASTA because locked records are not returned. The disadvantage of this statement is that since locked records are not returned, it is difficult to determine whether the result set or modification statement contains all the necessary records. You may need to add some logic to your application to ensure that all the necessary records are eventually included.

The example prompted by the READPAST datasheet is similar to the NOLOCK example. I will use a transaction to update a record in the SalesHistory data table.

BEGINTRANSACTIONUPDATETOP (1) SalesHistorySETSalePriceSalePrice=SalePrice+1

Because I did not commit or roll back the transaction, the lock added to the update record is still valid. In a new query editing window, run the following script that uses the number of records in the READPAST statistics for the SalesHistory datasheet.

SELECTCOUNT (*) FROMSalesHistoryWITH (READPAST)

At first, the SalesHistory data table contains 300 records, and the UPDATE statement is locking one record in the table, so the above script using READPAST returns 299 records, which means that the record I want to update is locked, so it is ignored by the REASPAST prompt.

At this point, the study on "how to extract SQLServer dirty reading data" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.