Know-how to make up for less than a few locks in ~sqlserver and the transaction level in. NET back to the directory

When a data table is locked by a transaction, when we make a select query, we need to query the information for the WITH (lock option), and if not, select will be blocked until the lock is released, and the following are some of the SQL lock options

Several locks of SQL

NOLOCK (without lock)

When this option is selected, SQL Server does not add any locks when reading or modifying data. In this case, it is possible for the user to read the data in the incomplete transaction (uncommited Transaction) or rollback (roll back), known as "dirty data."

HOLDLOCK (Hold Lock)

When this option is selected, SQL Server will persist this shared lock to the end of the entire transaction and will not be released on the way.

UPDLOCK (Modify Lock)

When this option is selected, SQL Server uses a modify lock instead of a shared lock when reading data, and holds the lock to the entire transaction or to the end of the command. Using this option ensures that multiple processes can read data at the same time, but only that process can modify the data.

TABLOCK (table Lock)

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

Paglock (page lock)

This option is the default option when SQL Server uses a shared page lock when it is selected.

Tablockx (Row it table lock)

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

Here's a look at the. NET Frameworks platform for transaction-level enumeration, which corresponds to the event level of SQL

Copy Code
Namespace System.Transactions
{
Summary:
Specifies the isolation level of a transaction.
public enum IsolationLevel
{
Summary: Serializes the isolation level with the highest binding force, placing a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction completes.
This is the most restrictive level in the four isolation levels. Because the concurrency level is low, you should use this option only when necessary. This option works the same as setting HOLDLOCK on all tables in all SELECT statements within a transaction.
Volatile data can is read but not modified, and no new data can added
During the transaction.
Serializable = 0,
//
Summary: Repeatable read isolation levels, Phantom reads, locking all data used in queries to prevent other users from updating data, but other users can insert new phantom rows into datasets,
and phantom rows are included in subsequent reads of the current transaction. Because concurrency is lower than the default isolation level, you should use this option only when necessary.
Volatile data can be read and not modified during the transaction. New data
Can be added during the transaction.
RepeatableRead = 1,
//
Summary: Cannot read but can be modified, may appear non-repeatable read, specifies to control shared locks to avoid dirty reads when reading data, but the data can be changed before the end of the transaction.
This results in non-repeatable read or phantom data. This option is the default value for SQL Server.
Volatile data cannot is read during the transaction, but can is modified.
ReadCommitted = 2,
//
Summary: Can be read and can be modified, may appear dirty data, perform dirty read or level 0 isolation lock, this means no shared lock, nor accept exclusive lock.
When you set this option, you can perform uncommitted read or dirty reads on the data, and you can change the values within the data before the transaction ends, and the rows can appear in the dataset or disappear from the dataset. This is the least restrictive level in the four isolation levels.
Volatile data can be read and modified during the transaction.
readuncommitted = 3,
//
Summary: Ignore data modification, get 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 is initially read. If
The data has been updated, a error is raised. This allows a transaction to
Get to the previously committed value of the data.
Snapshot = 4,
//
Summary:
The pending changes from more highly isolated transactions cannot is overwritten.
Chaos = 5,
//
Summary:
A different isolation level than the one specified was being used, but the
Level cannot is determined. An exception are thrown if this value is set.
Unspecified = 6,
}
}
Copy Code

Summary of transaction levels

Here are some of the things that are often encountered in nested transactions

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

2 read value is not reproducible: one transaction reads into a record, another transaction changes the record and commits it, and when the first transaction reads the record again, it has changed.

3 Phantom reads: One transaction uses a SELECT clause to retrieve data from one table, another transaction inserts a new record, and the select condition is met, so that the first transaction retrieves the data with the same select condition, and a record is added.

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

ReadCommitted:
Suppose that a transaction places a shared lock on the data being read, then data cannot be overwritten by other transactions, so it avoids dirty reads when the B transaction reads the data from the sum a is consistent. Since data can be overwritten before a is committed, a value read by B may be changed by a after it has been read, resulting in a value that cannot be repeated, or when B uses the same WHERE clause again to get the result set of the data that was not the same as the previous one, which is the Phantom data.

ReadUncommitted:
Assuming that a transaction does not publish a shared lock, nor does it accept an exclusive lock, then a concurrent B or other transaction can overwrite the data read by a transaction, then the state of the data read by the concurrent C transaction may be inconsistent with the data of a or B. Dirty reads, non-repeatable reads, and phantom data can all be present.

RepeatableRead:
(Note the first sentence in the original MSDN text: Place the lock on all data used in the query, so there is no dirty read).
Suppose a transaction places a lock on all data that is read to prevent other transactions from making changes to data, and before a does commit, the data that is read by the new concurrent transaction is consistent with the data in the a transaction, thus avoiding non-repeatable reads. But before a transaction is finished, a B transaction can insert a new record into the table where data is located, and when another transaction is queried again with the same WHERE clause, the resulting number may be the last inconsistency, that is, the 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 complete, which is the strictest lock. It prevents dirty reads, non-repeatable reads, and phantom data.

Its corresponding table is as follows:

Isolation level

Dirty Reads (Dirty read)

Non-repeatable read (Nonrepeatable Read)

Phantom Read (Phantom Read)

Unread (Read UNCOMMITTED)

possible

possible

possible

Read submitted (committed)

No way

possible

possible

REPEATABLE READ (Repeatable Read)

No way

No way

possible

Serializable (Serializable)

No way

No way

No way

Back to Catalog

Know-how to make up for less than a few locks in ~sqlserver and the transaction level in. NET back to the directory