MVCC snapshot Isolation level for SQL Server

MVCC snapshot Isolation level for SQL Server

MVCC

Product Introduction edit Multi-version Concurrency Control multiple versioning concurrency controls most MySQL transactional storage engines, such as Innodb,falcon and PBXT, do not use a simple row-locking mechanism. In fact, they are used in conjunction with another mechanism called "multi-version concurrency control (MVCC)", which is used to increase concurrency. MVCC is used not only in MySQL, but also in Oracle, PostgreSQL, and some other database systems. You can consider MVCC as a compromise of row-level locking, which in many cases avoids the use of locks while providing a smaller overhead. Depending on the implementation, it can allow non-blocking reads, locking only the necessary records when the write operation is in progress. MVCC will save a snapshot of the data at a point in time. This means that transactions can see a consistent view of the data, no matter how long they need to run. This also means that the data for the same table that different transactions see at the same point in time may be different. If you have never had an experience, it may be more abstract, but it will be easy to get acquainted with it slowly. Each storage engine has different implementations for MVCC. Some of these differences include optimistic and pessimistic concurrency control. We will show one side of the MVCC work through a simplified version of the InnoDB behavior. InnoDB: Implement MVCC by adding two additional hidden values for each row of records, one to record when this row of data was created, and another to record when this row of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the system version number when these events occur. This is a growing number of transactions as they are created. Each transaction will record its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as the version number of the transaction. Let's take a look at how this policy is applied to specific operations when the isolation level is repeatable READ: SELECT InnoDB must have each row of data to ensure that it complies with two conditions: 1, InnoDB must find a version of the line, at least as old as the version of the transaction ( That is, its version number is not greater than the version number of the transaction. This ensures that the data is present either before the transaction starts, or when the transaction is created, or when the row data is modified. 2. The deleted version of this line of data must be undefined or larger than the transaction version. This ensures that this row of data is not deleted before the transaction begins. Rows that meet both conditions may be returned as query results. Insert:innodb records the current system version number for this new line. Delete:innodb sets the current system version number to the deletion ID for this row. Update:innodb will write a new copy of this line of data, whichThe version of the current system version number for each copy. It also writes this version number to the deleted version of the old row. The result of this extra record is that there is no need to acquire a lock for most queries. They simply read the data as quickly as they can, ensuring that only rows that match the criteria are selected. The downside of this scenario is that the storage engine must store more data for each row, do more checking, and handle more of the cleanup. MVCC only works under repeatable Read and read commited isolation levels. Read uncommited is not MVCC compatible because the query cannot find the row version that is appropriate for their transactional version; they can only read the latest version at a time. Seriablable is also not compatible with MVCC because the read operation locks each row of data [1]  that they return.2 Description edit Concurrency control is automatically provided by using the MVCC (multi-version Concurrency control) algorithm. MVCC maintains multiple versions of a data without conflicting read and write operations. That is, each write operation on the data element x produces a new version of X, and Gbase 8m selects a version for each read operation of X. Gbase 8m is optimized for better performance due to the elimination of conflicts between read and write operations of data elements in the database. Especially for the database read and write two methods, they do not have to wait for other simultaneous write and read the same data. In a concurrent transaction, the database write waits only for the write that is being updated on the same row of data, which is the weakness of the existing row-level lock. At the same time MVCC reclaim unnecessary and long-time unused memory, prevent the waste of memory space. MVCC optimizes the database concurrency system so that the system has the highest performance when there are a large number of concurrent users, and can directly perform hot backup without shutting down the server.3 advantages over locking edit The main advantage of using the MVCC multi-version concurrency control ratio locking model is that in MVCC, the lock requirements for the retrieval (read) data do not conflict with the lock requirements of the write data, so the read does not block the write, and the write never blocks the read. There are also table and row-level locking mechanisms in the database for applications that cannot easily accept MVCC behavior. However, proper use of MVCC always provides better performance than locks.4GBase8 feature editor The query functionality in Gbase is provided by MVCC for consistent non-lock reading (which we refer to as consistency reading below), which is to provide a snapshot of the database at a point in time to implement the information query. A query is just a change made to a firm that was submitted before that point in time, not a change or uncommitted transaction after the point in time. Of course, if the transaction itself makes a change, it is visible to the query. The default level of Gbase is read COMMITTED, which is a query statement in a transaction under that isolation level, with a consistent read using the current timestamp, with a different timestamp for each query. But to repeatable READisolation level, all consistent reads in the same transaction, the timestamp used is the timestamp of the first query, so that the read is the data snapshot created by the first read of the transaction. The user will get a new snapshot of the data only by committing the current transaction and issuing a new query. Consistency read is gbase in read COMMITTED and Repeatable readThe default mode used in the SELECT statement is processed under the isolation level. The consistency read does not set any locks on the data it reads, so the table can be modified by other users while consistent reading a table. Note that the consistency read is invalid when the DROP table and ALTER table are working. Consistency reading is not valid on drop table because gbase cannot use a table that has been dropped, and the table has been deleted. The consistency read is not valid on ALTER table because Gbase will recreate a new table within the transaction and insert records from the old table to the new table. This way, when the user performs a consistent read again, no rows will be seen in the new table because the data in the new table is outside the snapshot for the first consistent read.  http://baike.baidu.com/view/1887040.htm?fr=aladdin