The main advantage of using multi-version concurrency control rather than traditional locking( read-write lock ) is that MVCC locks acquired for reading do not block writing and MVCC locks acquired for writing do not block reading and thus provide high throughput out of your database.
1) At the Read Committed isolation level, a snapshot is taken at the beginning of each statement and is active till the statement ends.
2) At the Repeatable Read and Serialization isolation levels, a snapshot is taken at the beginning of the first statement of the transaction and it remains active till the end of the transaction.
"Consider a case where two write transactions concurrently read data, try to modify the data, and then try to commit their changes. If both transactions are working on the same set of data, then only the latest changes made by a transaction would be visible and the other transaction changes would be lost forever. These are called Lost Updates." ----> In simple terms, it's not possible to achieve this using the Serialization Isolation level (also known as Serializable Snapshot Isolation). This isolation method relies on Multi-Version Concurrency Control (MVCC) instead of using locks. What it does is keep track of the dependencies between the data that's read and written by concurrent transactions, and it prevents situations where updates could be lost.
thanks a lot Vivek.
I would like to add (in context of postgresql):
1) At the Read Committed isolation level, a snapshot is taken at the beginning of each statement and is active till the statement ends.
2) At the Repeatable Read and Serialization isolation levels, a snapshot is taken at the beginning of the first statement of the transaction and it remains active till the end of the transaction.
"Consider a case where two write transactions concurrently read data, try to modify the data, and then try to commit their changes. If both transactions are working on the same set of data, then only the latest changes made by a transaction would be visible and the other transaction changes would be lost forever. These are called Lost Updates." ----> In simple terms, it's not possible to achieve this using the Serialization Isolation level (also known as Serializable Snapshot Isolation). This isolation method relies on Multi-Version Concurrency Control (MVCC) instead of using locks. What it does is keep track of the dependencies between the data that's read and written by concurrent transactions, and it prevents situations where updates could be lost.
how does MVCC differs from pessimistic lock?