Set TRANSACTION Isolation Level XXX--each setting is only for the current transaction block
XXX Value:
READ UNCOMMITTED
READ COMMITTED
Repeatable READ
SNAPSHOT
SERIALIZABLE
Test1 table data is as follows:
Update the Name for AAAAA now and set the 2-minute delay
The above update uses Rowlock by default, so for the table is the IX lock (line 12th), at this point, if the query other rows, is not blocked, because IX is compatible with IS.
However, if update uses TABLOCK, the table will use an X lock, and queries for that table are blocked because X is incompatible.
Setting the transaction isolation level to READ UNCOMMITTEDallows dirty reads ( Note that this transaction isolation level setting is only currently active )
Equivalent to the following settings, but nolock only for the current SELECT statement:
This will be blocked if you use the following:
Finally, at the end of the delay, the update data is rolled back, causing the query with the result to be dirty read, and the blocked query will get the following data:
The transaction isolation level of SQL Server