I'm currently reading dbms book and as i've understood Mvcc (Multi version concurrency control) is used for high concurrent read and write transactions. But "concurrency control on search structures" chapter mentions different locking concepts (lock coupling,link technique etc) for B-Trees.
Doesn't Mvcc applied on B-Tree's internal and leaf nodes in dbms? Are B-Tree concurrency and MVCC completeley different things?If so how Mvvc is implemented in dbms?
MVCC can be implemented in a variety of ways. The only requirement is that somehow older row versions are available.
For instance, SQL Server stores them in a temporary database that is reset when the server restarts.
Postgres stores row versions as hidden rows directly in the b-tree. It adds a hidden key column to the tree. When reading from the tree is only exposes the version that logically should be seen.
RavenDB's Voron manages b-tree pages as immutable data. Writes create entirely new trees. MVCC is therefore implemented as reading from the correct immutable tree.
Databases rarely lock physical structures for an extended amount of time. It is not a good idea to enable the database client to stop progress on database internal structures. Internal structures usually are locked very briefly. Logical row locks are treated separately.
If I had to guess concurrency control on search structures refers to physical thread-safety. This usually does not involve MVCC because there is no need to manage multiple versions. Normal in-memory locks are sufficient for brief accesses.
MVCC means you don't need to use locks.
Imagine each transaction gets a number timestamp that goes up for each transaction. We have transactions 1 and 2 in this example.
Transaction 1 reads A and writes value (A + 1). The snapshot isolation creates a temporary version of (A) which transaction 1 owns. The read timestamp of A is set to Transaction 1.
If transaction 2 comes along at the same time and reads A, it will also read the committed A -- it wont see A + 1 because it hasn't been committed. Transaction 2 can see versions of A that are == lastCommittedA and <= transaction 2.
At the time transaction 2 reads A, it will also check the read timestamp of A and see that a transaction 1 is there and check transaction 1 timestamp < transaction 2 timestamp. Because 1 < 2 then the transaction 2 will be aborted because it already depends on an old value of A.
(I have implemented MVCC in Java. See transaction, runner and mvcc code I have implemented a btree in Python.)
