What is Multiversion Concurrency Control (MVCC) and who supports it? [closed]
Oracle has had an excellent multi version control system in place since very long(at least since oracle 8.0)
Following should help.
- User A starts a transaction and is updating 1000 rows with some value At Time T1
- User B reads the same 1000 rows at time T2.
- User A updates row 543 with value Y (original value X)
- User B reaches row 543 and finds that a transaction is in operation since Time T1.
- The database returns the unmodified record from the Logs. The returned value is the value that was committed at the time less than or equal to T2.
- If the record could not be retreived from the redo logs it means the database is not setup appropriately. There needs to be more space allocated to the logs.
- This way the read consitency is achieved. The returned results are always the same with respect to the start time of transaction. So within a transaction the read consistency is achieved.
I have tried to explain in the simplest terms possible...there is a lot to multiversioning in databases.
PostgreSQL's Multi-Version Concurrency Control
As well as this article which features diagrams of how MVCC works when issuing INSERT, UPDATE, and DELETE statements.
The following have an implementation of MVCC:
SQL Server 2005 (Non-default, SET READ_COMMITTED_SNAPSHOT ON
)
Oracle (since version 8)
MySQL 5 (only with InnoDB tables)
PostgreSQL
Firebird
Informix
I'm pretty sure Sybase and IBM DB2 Mainframe/LUW do not have an implementation of MVCC