Use SQL Server update locks to prevent data dirty reads

Sometimes we need to control that a record is no longer updated after the program is read, until the transaction has finished executing the release. At this point we can add the update lock to all queries that will manipulate the current record to prevent the query from being modified by other transactions. This operation only locks a row in the table without locking the entire table, and the experience is better.

Test the SQL code as follows:

Execute the following statement in a query

BEGIN Tran SELECT Investstate from INVESTORDERSABC with (UPDLOCK) where id=10005 waitfor delay ' 00:00:10 '  update inves TORDERSABC set investstate= ' 2 ' where Id=10005commit Tran

1. Execute the following statement in a different query

SELECT investstate from Investordersabc  where id=10005

It is found that the value before the first transaction is executed is the original value 0, until the update is complete before it becomes 2, and if the lock is added, the code is as follows:

SELECT investstate from INVESTORDERSABC with (UPDLOCK) where id=10005

The discovery of the SQL statement must wait until the transaction in the first connection completes, because the update lock for the SQL connection considers that the update lock in the first transaction might modify the data, so it must be executed before the transaction execution is complete. The update lock becomes an exclusive lock at this time.

2. If the update operation is performed:

BEGIN TRAN Update investorders set investstate= ' 3 ' where Id=10005commit Tran

The discovery cannot be changed until the first query is complete. In fact, and lock without lock has nothing to do, why? Because SQL Server automatically uses exclusive locks when it executes an INSERT, UPDATE, or delete command.

3, the transaction above does not add isolation level, the default isolation level of the transaction is read committed, not lock so in the 1th can also be queried. When the isolation level for a database transaction is repeatable read,serializable, if the query requires a shared lock:

SELECT investstate from INVESTORDERSABC with (HoldLOCK) where id=10005

Use SQL Server update locks to prevent data dirty reads