SQL Server data table prompts nolock and readpast

When reading or modifying data in the database, the database engine uses a dedicated control type to maintain the integrity of the database, which is called the lock mechanism. The lock mechanism ensures Database Consistency by ensuring that the database records contained in a transaction cannot be modified by other transactions before the transaction is committed.

When designing database applications, you should remember different types of locks and different isolation levels of transactions. In general, the default method of SQL Server can well complete the functions you want to use. However, sometimes it is very useful to manually add the prompt information about how the lock is applied on the data table using SQL statements.

This article mainly introduces two data table prompts: nolock and readpast. We will create a data table as the query data table in the example. Execute the script in list A to create a saleshistory data table and add some data.

Nolock

This data table prompt, also known as readuncommitted, can only be used for select statements. Nolock indicates that no shared lock is added to the data table to prevent other transactions from modifying the data table.

The advantage of this statement is that it can enable the database engine to avoid locking in queries, improve concurrency and improve database performance, because the database engine does not need to maintain shared locks. The problem is that the statement cannot process all the locks of the data table to be read, so some "dirty data" or uncommitted data may be read.

If a transaction is rolled back, the data read operation that applies the nolock connection can read uncommitted data. This type of reading causes many problems due to inconsistent processing. This is a technique you should know when using nolock.

As a negative impact, nolock queries may also lead to the risk of reading phantom data or reading data that can be obtained in one database read transaction but may be rolled back in another transaction. (I will be in this seriesArticle.)

The following example shows how nolock works and how dirty data is read. In the following script, I use a transaction to insert a record into the saleshistory data table.

Begin transaction

Insert into saleshistory

(Product, saledate, saleprice)

Values

('Pooltable', getdate (), 500)

This transaction is still open, which means you can still lock the records inserted into the data table to prevent other operations. In a new query window, run the following script, which uses the nolock data table prompt to return the number of records in the saleshistory data table.

Select count (*) from saleshistory with (nolock)

The returned record value is 301. Because the transaction for inserting records into the saleshistory data table has not been committed, we can undo it. I use the following statement to roll back the transaction:

Rollback transaction

This statement deletes the previously inserted records from the saleshistory table. Now we run the same SELECT statement as before.

Select count (*) from saleshistory with (nolock)

The number of records returned this time is 300. The transaction for which I first query the read records has not been committed. This is a dirty data read.