mssql-concurrency control -2-isolation

If reproduced, please indicate the source of the blog: www.cnblogs.com/xinysu/, copyright belongs to the blog Garden su Home radish all. Hope you support!

MySQL implements concurrency control through MVCC and locks, and in 4 isolation levels, read-write data and lock-in methods vary to meet different business requirements.

In MSSQL, concurrency control is also achieved through the row versions of Locks and MVCC. In each transaction, the type, level, locking, and release of the lock are controlled by the isolation level of the transaction, in MSSQL, there are 6 isolation levels, and different isolation levels do not apply to locks. Of these two isolation levels, there are 2 mechanisms for applying MVCC, which is the isolation level of the snapshot class: Read commmitted Snapshot and Snapshot.1 concurrency control theoryIn MSSQL, the concurrency control theory commonly used is pessimistic concurrency control and optimistic concurrency control.1.1 Pessimistic concurrency controlPessimistic concurrency, by default during a transaction operation, there must be other transactions competing with it for resources, so during the transaction operation, the data will be locked according to different circumstances, to avoid other transactions during the operation to modify or read the data to ensure consistency of data. pessimistic concurrency control, due to the inclusion of lock mechanism, will greatly affect the concurrency scale. It is primarily used in systems where data is frequently modified and the cost of rolling back a transaction is greater than the cost of locking the data . 1.2 Optimistic concurrency controlOptimistic control, the default transaction when reading data, other transactions do not operate the data, so no lock, directly modify the data, modified to see if there are other users during the reading of the data, and if so, roll back the modified transaction itself. optimistic concurrency control, which is applied to systems where data modifications are infrequent and rollback transaction costs are less than the cost of lock data . 2 Isolation LevelIn each transaction, an isolation level is specified that defines the degree of isolation between this transaction and other transactions. In MSSQL, there are 6 isolation levels, 4 regular isolation levels with 2 snapshot isolation levels: READ UNCOMMITTED, read Committed, read commmitted (row version), read repeattable, Snapshot and Read serializeble.     Read commmitted (line version) may have less contact with snapshot, but it will still be explained. In MySQL, the default isolation level is RR, whereas in SQL Server, the default isolation level is RC, Read committed. 2.1 Isolation Level DescriptionHow do I set the default isolation level for an entire database?    The data inconsistency is described in the previous blog post: Http://www.cnblogs.com/xinysu/p/7260227.html in the fourth chapter: inconsistent data. The S lock is not the whole process of lock-in (MSSQL or is lock application).

2. Read UNCOMMITTED
• RU, read the uncommitted record, always read the latest record
• There may be dirty reading, non-repeatable reading, Phantom reading and other problems
• The read process does not add S lock, equivalent to SELECT * from Tbname with (NOLOCK)
4. Read Committed
• RC for short, Read Committed record
• There may be problems such as non-repeatable reading, phantom reading, etc.
• Read the process plus S lock, regardless of whether the transaction ends, the SELECT statement once the end, immediately release S lock, will not wait until the end of the transaction to release the lock, followed by Strict 2-PL
6. Read commmitted (line version)
• Abbreviation RCSI
• Apply MVCC principle, version read, Read Committed record, but read not necessarily the latest record
• In the same transaction, the read data is the same version
• There is no dirty read, non-repeatable read problem, there may be a phantom reading problem
• The version data in the row versioning isolation level does not exist with the database itself, but instead exists in tempdb, which is described in detail in this isolation level
8. Read repeattable
• Abbreviated RR, REPEATABLE read record
• There may be some problems such as Phantom reading
• Read the process plus S lock until the end of the transaction before releasing S lock, followed by Stong Strict 2-PL
10. Snapshot
• Abbreviation SI
• This isolation level is described in detail below
12. Read serializeble
• RS for short, serialized read record
• No problems such as dirty reading, non-repeatable reading, phantom reading, etc.
• In addition to the reading process to add the S lock, but also add a range of locks; During the process of modifying the data, in addition to adding an X lock, you will also add a range lock to avoid the qualifying data in the operation process, there are other eligible data insert in
• The worst concurrency, unless explicitly business needs and performance impact to use, once encountered a text message business framework By default use this isolation level, after the launch of the death lock K, immediately analyze emergency repair ....

2.2 Read commmitted Snapshot isolation and Snapshot isolationRead commmitted Snapshot Isolation using row versioning Statement-levelSnapshot, in a transaction, when the data is modified or deleted, the write replication mechanism is called to ensure that the old version of the row data being written satisfies the consistency before the transaction operation.    RCSI guarantees read consistency at the statement level. Snapshot Isolation using row versioning Transaction-levelsnapshot, and the write replication mechanism is called when the transaction begins. SI guarantees read consistency at the transaction level. How do I manage row version information? Both of the row version information is stored in the tempdb database, not stored in its own database, which requires tempdb to have sufficient space to store the version information, if the tempdb space is insufficient, the row version write failed, resulting in the isolation level is not working properly. Storage engine for transactions using RCSI or SI isolation level, at the beginning of SI transaction, assign a transaction sequence number XLN, each allocation increment 1, in order to achieve transaction level consistency, here note RCSI transaction sequence number is not a transaction a serial number, This is a transaction sequence number for each SQL within a transaction to implement a statement-level snapshot. Under these two isolation levels, you need to maintain a logical copy of all the data modifications that have been performed (the line version), which is stored in tempdb, with each logical copy (row version) having the transaction sequence number XLN of the transaction marked this time. That is, the most recent row values are stored in the current database, and the history row version information includes the latest version, which is stored in tempdb. Note here that when modifying data in a transaction, write row version information, write to the cache pool, and refresh to the tempdb file, to avoid the performance caused too much impact. This time, you might ask? Wouldn't that be tempdb? To store very much historical version data, is there a removal mechanism? This is there, on the one hand, row version information is not deleted immediately, because the transaction requirements that are running under the row versioning isolation level are guaranteed to ensure that parallel transactions are not affected if the row version information that is being used in tempdb. On the other hand, the storage engine for the database tracks the earliest available transaction sequence numbers, and then periodically deletes all row versions of XLN that are smaller than the serial number. How do I read row version information? The transaction read data at the two snapshot isolation level does not acquire a shared lock on the data being read and therefore does not clog the transaction being modified, providing its DB concurrency capability due to reduced lock requests and number.      However, the schema lock for the table is obtained, and if the table is discovering schema modifications (such as column additions and modifications), it will be blocked.      How to read the appropriate version of the row, there is a difference between RCSI and SI. RCSI: each time the statement is startedTo commit all data while reading the LatestTransaction sequence, which enables each statement within a transaction under RCSI to be viewed when each statement startsExistence of LatestA snapshot of the data, that is, multiple SQL query gaps within a transaction have other transactions modified data, then the same transaction multiple times the same SQL query results will appear inconsistent. SI: Every time When you start a transaction, commit all data, read the nearest but lower than the snapshot transaction sequence number, that is, multiple SQL queries within a transaction, read the same version of the data, even if there are multiple query gaps with other transaction modification data, read the results are consistent. How do I modify row version information? In a RCSI transaction, using a blocking scan (where the data value is read with an update lock (U Lock) on the data row to select the row to update, the row record that satisfies the condition upgrades the update lock to the exclusive lock, note that this is not the row version information that is scanned in tempdb, But the most recent row record in the actual database, the mechanism to modify the data is the same as RC. If the data row does not meet the update criteria, the update lock is released on that row, and the next row is locked and scanned.       After the lock is held, the data is updated and the lock is released after the transaction ends. In the case of SI transactions, an optimistic approach to data modification is used: Data modifications are made using row versions of data, until the data modification is completed, the locks on the actual data are obtained, and the modified data rows are committed when the data rows conform to the update criteria.If the data row has been modified outside of the snapshot transaction, an update conflict will occur and the snapshot transaction will also be terminated. update conflicts are handled by the database engine and cannot be disabled for update conflict detection. From a simple SQL analysis, where conditions are all primary keys (only for personal test speculation):

• Same transaction, multiple SELECT * from Tbname WHERE id=2
• RCSI, in the same transaction, each time the SQL is started , the data is submitted to the tempdb table (presumably, it should be assigned a hash-like string, etc., if the same transaction is consistent with multiple query results, should not be at the beginning of each SQL, The row version is repeatedly submitted to tempdb, and the latest version information is read from tempdb, and if Tempdb does not have version information, it is read from the database and the records that are read to are stored in tempdb. There is an inconsistency in the same transaction, with multiple read data results.
• SI, in the same transaction, the same SQL within the same transaction reads from tempdb the latest version of the current transaction, the entire transaction is using this version of SQL, if tempdb does not have version information, it is read from the database, and the read records are stored in the Tempdb. In the same transaction, there is no case where multiple read data results are inconsistent.
• UPDATE tbname SET colname= ' xinysu ' WHERE id=18
• RCSI, directly read the data in the database, according to the primary key plus x lock, update the data, this operation is the same as the RC isolation level.
• SI, read the row version of the data, select the row version of the line to be updated, modify the data to the actual database after successful modification, if the actual database data during this operation has been modified by other transactions, the update conflict, the transaction will stop error. That is, SI updates conflict detection at update time.
• Why do you want to update on the line version first, and finally on the actual data?
• Suppose an update run requires 3s, but only 1 rows of records are updated, if updated directly on the actual data, you need to lock the scan record 3s, the last update, the middle will block to other transactions on the data query, but if the row version is updated, you do not need to lock the actual data, The last update of 1 rows of records, very fast, to avoid long-time congestion, improve concurrency ability .

Property	Read Committed isolation level using row versioning	Snapshot Isolation Level
Database-level options start	Read_committed_snapshot	Allow_snapshot_isolation
Transaction settings	Use the default committed read isolation level, or run the SET TRANSACTION isolation LEVEL statement to specify the read COMMITTED isolation Levels	SET TRANSACTION Isolation level to specify SNAPSHOT isolation levels before a transaction starts
Row version Processing	All data submitted before each statement is started.	All data that is committed before each transaction starts.
Update processing	Restore from the row version to the actual data to select the row to update and use the update lock on the selected data row. Gets the exclusive lock on the actual data row to be modified. There is no update conflict detection.	Use the row version to select the rows to update. An attempt is made to obtain an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction terminates.
Update conflict Detection	No	Integration support. cannot be disabled.

3 Isolation Level Test View the database isolation level for the current session: DBCC useroptions, viewing [SET options] = ' isolation level ', to view the isolation levels of the current transaction.    The data inconsistency is described in the previous blog post: Http://www.cnblogs.com/xinysu/p/7260227.html in the fourth chapter: inconsistent data.     The 2-PL lock application release is described in the previous blog post: http://www.cnblogs.com/xinysu/p/7260227.html in the 3rd chapter: Data inconsistency. To set the database isolation level:

• RU, at the start of the transaction, set TRANSACTION isolation level READ UNCOMMITTED
• RC, setting set TRANSACTION isolation Level READ COMMITTED at start of transaction
• RCSI, the entire database-level setting read_committed_snapshot is on, note that the setting of this time need to obtain the exclusive rights of the database, that is, the user thread is not currently allowed to connect to the database, the setting of SQL will always be in a blocking situation. If the default isolation level of the current database is RC, after setting, the default is RCSI, no, you need to set TRANSACTION at the start of the transaction isolation level READ COMMITTED
• Database settings: Under current database, execute ALTER database dbname set read_committed_snapshot on
• Transaction settings: Set TRANSACTION isolation Level READ COMMITTED
• RR, when the transaction starts, set TRANSACTION isolation level repeatable READ
• RS, setting set TRANSACTION Isolation Level SERIALIZABLE when transaction starts
• SI, the entire database level is set Allow_snapshot_isolation to ON, and the isolation level of the transaction is set to SNAPSHOT. Note that the Allow_snapshot_isolation setting here is also an exclusive lock that requires data acquisition.
• Database settings: Under current database, execute ALTER database dbname set allow_snapshot_isolation on
• Transaction settings: Set TRANSACTION isolation Level SNAPSHOT;

During the test, there are 3 tables: No index, no index, and a unique index.

CREATETABLETb_no_index (IDintPrimaryKey  notNULLIdentity(1,1), ageint notNULL, namevarchar( -) );CREATETABLETb_index (IDintPrimaryKey notNULLIdentity(1,1), ageint notNULL, namevarchar( -) );CREATETABLETb_unique_index (IDintPrimaryKey notNULLIdentity(1,1), ageint  notNULL, namevarchar( -) ); CREATEINDEXIx_age onTb_index (age)CREATEINDEXIx_unique_age onTb_index (age)INSERT intoTb_no_index (age)Values(2),(9),( +),(4),(7),( -);INSERT intoTb_index (age)Values(2),(9),( +),(4),(7),( -);INSERT intoTb_unique_index (age)Values(2),(9),( +),(4),(7),( -);

3.1 Read Uncommitted

• Data inconsistency test
• RU Test Conclusion
• Under the RU isolation level
• There is no update loss (lock mechanism), but dirty reads, non-repeatable reads, and phantom reads occur.
• Read no row lock, can read UNCOMMITTED data

3.2 Read Committed

• Data inconsistency test
• Read the situation Test
• RC Test Conclusion
• Under the RC isolation level
• No update loss (lock mechanism), dirty read, but non-repeatable read and Phantom reads occur
• Read need to apply for lock, so there is no dirty reading situation
• Following the strong 2-PL mode, read locks within a transaction are immediately released, and write locks are released when the transaction is committed.

3.3 Read Commit Snapshot isolation

• Test environment settings
• The implementation sets the database isolation level to:
• Check the default isolation level for the current session:
• Data inconsistency test

• Update Conflict test
• RCSI Test Conclusion
• Read no lock, but application form for schema lock, read row version data
• There are no missing updates, dirty reads, but non-repeatable read and phantom read conditions
• No update conflict detection, RCSI with RC update processing mode

3.4 Read reaptable

• Data inconsistency test
• RR Test Conclusion
• Read Plus s lock, release S lock after transaction ends
• There are no missing updates, dirty reads, and non-repeatable reads, but there is a phantom reading situation

3.5 Read Serializable

• Data inconsistency test
• RS Test Conclusion
• Read Plus s lock, release S lock after transaction ends
• Increased range lock
• There are no missing updates, dirty reads, non-repeatable reads, Phantom reads
• Worst concurrency capability

3.6 Snapshot Isolation

• Data inconsistency test
• Update Conflict test
• SI Test Conclusion
• There are no data inconsistencies such as missing updates, dirty reads, Phantom reads, etc.
• Read-no-lock, read-line version data
• Has conflict monitoring, cannot be disabled, and if this isolation level is used, the program is going to do the rollback processing of the update conflict

4 Summary tr> tr>

Isolation level	description	Dirty read	non-repeatable read	Phantom	concurrency control model
READ UNCOMMITTED	uncommitted read	Yes	Yes	YES	pessimistic
Read Committed	Read-committed	NO	Yes	YES	pessimistic
Read commmitted (row version)	Read-committed (snapshot)	NO	YES	YES	optimistic
Read repeattable	repeatable read	NO	no	YES	pessimistic
Snapshot	snapshot	no	no	no	optimistic
Read serializeble	serializable	no	NO	no	pessimistic

mssql-concurrency control -2-isolation