Database consistency is also one of the important indicators to measure DBMS performance. At present, most commercial databases (DB2, SQL Server) use the Two-Phase Locking (2PL) protocol for concurrency control, which ensures the serialization of concurrent transaction execution. However, 2PL needs to lock any data before it can read or write to it. In the blocking compatibility matrix, S locks (Share Locks) and X locks (Exclusive Locks) are incompatible, so when transaction 1 is performing a read operation on data A (plus an S lock), and transaction 2 wants to write to the data (add an X lock), then transaction 2 must wait for transaction 1 to release the S lock on data A before proceeding. Multi-Version Concurrency Control (MVCC) solves this problem well. In a multi-version system, each write data generates a new version, and the read operation can read the appropriate version as needed, so the read and write operations do not block each other. MVCC increases concurrency, but it also introduces the storage overhead of maintaining multiple versions.
The Microsoft SQL Server database engine introduces a new implementation of the existing transaction isolation level - committed reads, which provide statement-level snapshots using row versioning. The SQL Server database engine also introduces a new level of transaction isolation - snapshots to provide transaction-level snapshots that also use row versioning.
Setting the READ_COMMITTED_SNAPSHOT database option to ON enables committed read isolation using row versioning. Setting the ALLOW_SNAPSHOT_ISOLATION database option to ON enables snapshot isolation. When either option is enabled for the database, the database engine maintains the version of each row that is modified. Whenever a transaction modifies a row, the image of the row before the modification is copied to a page in the version store. A version store is a collection of data pages in tempdb. If there are multiple transaction modification lines, multiple versions of that line will be linked in a version chain. A read operation using row versioning will retrieve the last version of each row that was committed at the time of the transaction or statement start.
Applications written for SQL Server 2008 or new to SQL Server implement isolation of read commits using row versioning by specifying the transaction isolation level for read commits when the READ_COMMITTED_SNAPSHOT database option is ON. All reads will look at the row version that was committed when the statement started. This will provide a statement-level snapshot of the data.
Applications written for SQL Server will implement snapshot isolation by specifying the snapshot transaction isolation level when the ALLOW_SNAPSHOT_ISOLATION database option is ON. All reads in a snapshot transaction will look at the version of the row that was committed when the transaction started. This will provide a transaction-level snapshot of the data.
For transactions that use row-based isolation levels, reads do not request shared locks on the data. This means that readers using row versioning do not prevent other readers or writers from accessing the same data. Similarly, the writer does not get in the way of the reader. However, writers get in the way of each other (even when running at a level of isolation based on row versioning). Two write operations cannot modify the same data at the same time.
The Snapshot Isolation feature extends the locking framework in SQL Server 2008 by enabling applications to view values before any data modifications occur. This prevents the application from being locked while still providing truly submitted data. SQL Server 2008's Read Committed Snapshot requires a database administrator to activate, allowing data to be read by read-only transactions. So SI's concurrency control of read-only transactions is very good, but it is unclear whether this is the case for update transactions. It is more unfavorable for long-running update transactions to compete with short-term high-level transactions. If a transaction across databases attempts to use the Snapshot Isolation (SI) standard, rather than all databases set, the transaction fails. This undoubtedly creates certain obstacles to scalability. It seems that Microsoft still has a long way to go to achieve its own SI that is stronger than the SQL 92 specification.
Before any modification, make a copy of the previous version, and all subsequent read operations will read the copied version, and the modification will create a new version. In this way,Read and write operations do not block each other. The advantage of using this line versioning mechanism is that the concurrency of the program is relatively high, but the disadvantage is that although the user reads not a dirty data, it may be a data value that is being modified and is about to expire. If you modify the data based on this expired value, it will cause a logical error。
SQL commands:
Reference links:
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
The hyperlink login is visible.
|
Posted on 1/28/2021 5:55:52 PM
|
|
|
Posted on 1/29/2021 9:13:40 AM
|
want