You cannot make up for it ~ Several locks in Sqlserver and transaction levels in. net

Return to the directory. When the data table is locked by the transaction and we perform the select query, We need to query the information for the with (Lock option). If it is not added, the select statement will be blocked, until the lock is released, the following describes several SQL lock options of the SQL lock NOLOCK (no lock) when this option is selected, SQLServer does not apply any lock when reading or modifying data.

Return to the directory. When the data table is locked by the transaction and we perform the select query, We need to query the information for the with (Lock option). If it is not added, the select statement will be blocked, until the lock is released, the following describes several SQL lock options SQL lock NOLOCK (no lock) when this option is selected, SQL Server does not apply any lock when reading or modifying data

Back to directory

When the data table is locked by the transaction and we perform the select query, We need to query the information for the with (Lock option). If it is not added, the select statement will be blocked until the lock is released, the following describes several SQL lock options.

Several SQL locks

NOLOCK (no lock)

When this option is selected, SQL Server does not apply any lock when reading or modifying data. In this case, the user may read the data in the uncommitted Transaction or Roll Back, that is, the so-called "dirty data ".

HOLDLOCK)

When this option is selected, SQL Server will keep the shared lock until the end of the entire transaction, instead of releasing it on the way.

UPDLOCK)

When this option is selected, SQL Server uses the modification lock to replace the shared lock when reading data, and keeps the lock until the entire transaction or command ends. This option ensures that multiple processes can read data at the same time, but only the process can modify data.

TABLOCK)

When this option is selected, SQL Server sets a shared lock on the entire table until the command ends. This option ensures that other processes can only read but cannot modify data.

PAGLOCK)

This option is the default option. When selected, SQL Server uses the share page lock.

TABLOCKX (exclusive table lock)

When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.

Next, let's take a look at the transaction-level enumeration on the. net frameworks platform, which corresponds to the SQL event level.

Namespace System. transactions {// Summary: // Specifies the isolation level of a transaction. public enum IsolationLevel {// Abstract: serialization isolation level, with the highest binding force. Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction is completed.
// This is the maximum isolation level. Because the concurrency level is low, this option should be used only when necessary. This option is used to set HOLDLOCK for all tables in all SELECT statements in the transaction. // Volatile data can be read but not modified, and no new data can be added // during the transaction. serializable = 0, // Abstract: The isolation level of the Repeatable read. Phantom reads may occur. All data used in the query is locked to prevent other users from updating data, however, other users can insert new Phantom rows into a dataset,
// And the phantom row is included in the subsequent reading of the current transaction. Because concurrency is lower than the default isolation level, this option should be used only when necessary. // Volatile data can be read but not modified during the transaction. new data // can be added during the transaction. repeatableRead = 1, /// Abstract: it cannot be read but can be modified. It may be non-repeated. It specifies to control the shared lock when reading data to avoid dirty reading, however, the data can be changed before the transaction ends,
// Generate non-repeated reading or phantom data. This option is the default value of SQL Server. // Volatile data cannot be read during the transaction, but can be modified. readCommitted = 2, /// Abstract: It can be read or modified. Dirty data may occur, and dirty reads or 0-level Isolation locks are executed. This means no shared locks are issued, the exclusive lock is also not accepted.
// When this option is set, uncommitted read or dirty read can be performed on the data. The value in the data can be changed before the transaction ends, the row can also appear in the dataset or disappear from the dataset. This is the minimum limit among the four isolation levels. // Volatile data can be read and modified during the transaction. readUncommitted = 3, /// Abstract: Ignore the data modification and obtain the data before the modification. // Volatile data can be read. before a transaction modifies data, it verifies // if another transaction has changed the data after it was initially read. if // the data has been updated, an error is raised. this allows a transaction to // get to the previusly committed value of the data. snapshot = 4, // Summary: // The pending changes from more highly isolated transactions cannot be overwritten. chaos = 5, // Summary: // A different isolation level than the one specified is being used, but the // level cannot be determined. an exception is thrown if this value is set. unspecified = 6 ,}}

Summary of transactions

The following are common situations in nested transactions:

1. Dirty read: A transaction reads data that has not been committed by another transaction, so you will see some data that is finally rolled back by another transaction.

2. The read value cannot be reproduced: one transaction reads a record, and the other transaction changes this record and commits it. At this time, when the first transaction reads this record again, it has changed.

3 phantom read: A transaction uses the select clause to retrieve the data of a table. The other transaction inserts a new record and meets the select condition. In this way, after the first transaction uses the same select condition to retrieve data, an additional record is generated.

Below are some of the instructions for using IsolationLevel (from blog: http://www.cnblogs.com/CN5135/archive/2011/10/24/2222350.html)

ReadCommitted:
Assume that transaction A places A shared lock on the Data being read, so Data cannot be rewritten by other transactions. Therefore, when transaction B reads Data, the total Data read by transaction A is consistent, therefore, dirty reading is avoided. Since Data can be rewritten before A is submitted, A value read by B may be changed by A after it is read, resulting in that the value cannot be obtained repeatedly; or when B uses the same where clause again, it obtains a result set that is different from the previous data, that is, phantom data.

ReadUncommitted:
Assume that transaction A does not publish A shared lock or accept an exclusive lock, then concurrent transactions B or other transactions can rewrite the data read by transaction, the status of the data read by the concurrent C transaction may be different from that of A or B. Dirty reads, non-repeated reads, and phantom data may exist.

RepeatableRead:
(Note the first sentence in the original MSDN text:QueryLock on all data used in, so there is no dirty read ).
Assume that transaction A places A lock on all the Data read to prevent other transactions from making changes to the Data. Before transaction A does not commit, if the Data read by A new concurrent transaction exists in Data, the Data state is consistent with that in transaction A, thus avoiding repeated reads. However, before the end of transaction A, transaction B can insert A new record to the table where the Data is located. When other transactions query with the same where clause again, the number of results may be inconsistent last time, that is, phantom data.

Serializable:
An exclusive lock is placed on the data table to prevent other users from updating rows or inserting rows into the dataset before the transaction is completed. This is the strictest lock. It prevents dirty reads, non-repeated reads, and phantom data.

Its corresponding table is as follows:

Isolation level	Dirty read (Dirty Read)	Non-repeated read (NonRepeatable Read)	Phantom read (Phantom Read)
Read uncommitted)	Possible	Possible	Possible
Read committed)	Impossible	Possible	Possible
Repeatable read)	Impossible	Impossible	Possible
Serializable)	Impossible	Impossible	Impossible

Back to directory