What is MVCC?

One of the important and attractive points of the PostgreSQL database management system is how it handles contention between transactions. In PostgreSQL, reading data never blocks writing data and vice versa. This is accomplished through a mechanism called “Multi Version Concurrency Control” (MVCC).

MVCC is not a unique feature of PostgreSQL, it is also implemented in some other database management systems such as Oracle, Berkeley DB, CouchDB, and many other systems. What’s important is understanding how PostgreSQL implements MVCC, as this helps software developers design applications capable of concurrent operation using PostgreSQL, and also helps solve a range of complex problems that may arise in the future.

How does MVCC work?

sequenceDiagram
    participant Client as Client
    participant Database as Database
    participant Transaction1 as Transaction 1
    participant Transaction2 as Transaction 2

    Client->>Database: Begin Transaction
    alt Transaction 1
        Database->>Transaction1: Assign Transaction ID
        Transaction1->>Database: Read Data
    else Transaction 2
        Database->>Transaction2: Assign Transaction ID
        Transaction2->>Database: Read Data
    end
    Client->>Database: Perform Update
    alt Transaction 1
        Database->>Transaction1: Create New Version
        Transaction1->>Database: Update Data
    else Transaction 2
        Database->>Transaction2: Create New Version
        Transaction2->>Database: Update Data
    end
    Client->>Database: Commit Transaction
    alt Transaction 1
        Database->>Transaction1: Save New Version
    else Transaction 2
        Database->>Transaction2: Save New Version
    end

In the above sequence diagram, we have two transactions, Transaction 1 and Transaction 2, being performed concurrently on the database.

1. Each transaction starts with the Client calling “Begin Transaction” with the database.
2. The database then assigns a unique transaction ID to each transaction to track them.
3. Transaction 1 and Transaction 2 perform data reading (Read Data) from the database.
4. After reading the data, both transactions perform data updating (Perform Update). The database creates a new version of the data to ensure consistency.
5. Finally, both transactions end by calling “Commit Transaction” and the database saves the new version of the data.

Detailed process of inserting a record into the database

sequenceDiagram
    participant Client as "Client"
    participant MVCC as "MVCC"
    participant Database as "Database"

    Client->>+MVCC: Begin Transaction (Insert Record)
    MVCC->>+Database: Begin Transaction (Insert Record)
    Database-->>-MVCC: Response (Transaction Started)

    Client->>+MVCC: INSERT INTO Details (StudentID, CourseID, Score) VALUES (1, 101, 85)
    MVCC->>+Database: INSERT INTO Details (StudentID, CourseID, Score) VALUES (1, 101, 85)
    Database-->>-MVCC: Response (Details Record Added)

    Client->>+MVCC: End Transaction (Insert Record)
    MVCC->>+Database: End Transaction (Insert Record)
    Database-->>-MVCC: Response (Transaction Ended)

1. Client starts the transaction by sending a request to insert a record to MVCC.

2. MVCC receives the request from Client and proceeds by sending a request to insert a record to Database.

3. Database receives the request from MVCC and confirms that the transaction has started.

4. Client executes the INSERT INTO query to insert a record into the database. In this example, the record is inserted into the Details table with the values StudentID, CourseID, and Score being 1, 101, and 85 respectively.

5. MVCC receives the INSERT INTO query from Client and proceeds by sending it to Database.

6. Database performs the insertion of the record into the Details table and then sends a response to MVCC to inform that the Details record has been successfully added.

7. Client ends the transaction by sending a request to end the transaction to MVCC.

8. MVCC proceeds by sending the request to end the transaction to Database.

9. Database receives the request to end the transaction from MVCC and confirms that the transaction has ended.

Thus, the process of inserting a record into the Database via MVCC includes the steps of starting the transaction, executing the INSERT INTO query, and ending the transaction, ensuring concurrency and avoiding conflicts between different transactions.

Detailed process of deleting a record from the database

sequenceDiagram
    participant Client as "Client"
    participant MVCC as "MVCC"
    participant Database as "Database"

    Client->>+MVCC: Begin Transaction (Delete Record)
    MVCC->>+Database: Begin Transaction (Delete Record)
    Database-->>-MVCC: Response (Transaction Started)

    Client->>+MVCC: DELETE FROM Details WHERE ID = 1
    MVCC->>+Database: DELETE FROM Details WHERE ID = 1
    Database-->>-MVCC: Response (Details Record Marked for Deletion)

    Client->>+MVCC: End Transaction (Delete Record)
    MVCC->>+Database: End Transaction (Delete Record)
    Database-->>-MVCC: Response (Transaction Ended)

1. Client starts the transaction by sending a request to delete a record to MVCC.

2. MVCC receives the request from Client and proceeds by sending a request to delete a record to Database.

3. Database receives the request from MVCC and confirms that the transaction has started.

4. Client executes the DELETE FROM query to delete a record from the Details table based on the condition WHERE ID = 1. In this example, the record with ID 1 will be deleted.

5. MVCC receives the DELETE FROM query from Client and proceeds by sending it to Database.

6. The database performs the deletion of the record from the Details table based on the given condition and then sends a response to MVCC to inform that the record has been marked for deletion.

7. Client ends the transaction by sending a request to end the transaction to MVCC.

8. MVCC proceeds by sending the request to end the transaction to Database.

9. Database receives the request to end the transaction from MVCC and confirms that the transaction has ended.

Thus, the process of deleting a record from the Database via MVCC includes the steps of starting the transaction, executing the DELETE query, and ending the transaction, ensuring concurrency and avoiding conflicts between different transactions.

Detailed process of updating a record in the database

sequenceDiagram
    participant Client as "Client"
    participant MVCC as "MVCC"
    participant Database as "Database"

    Client->>+MVCC: Begin Transaction (Update Record)
    MVCC->>+Database: Begin Transaction (Update Record)
    Database-->>-MVCC: Response (Transaction Started)

    Client->>+MVCC: UPDATE Details SET Score = 90 WHERE ID = 1
    MVCC->>+Database: UPDATE Details SET Score = 90 WHERE ID = 1
    Database-->>-MVCC: Response (Details Record Updated)

    Client->>+MVCC: End Transaction (Update Record)
    MVCC->>+Database: End Transaction (Update Record)
    Database-->>-MVCC: Response (Transaction Ended)

1. Client starts the transaction by sending a request to update a record to MVCC.

2. MVCC receives the request from Client and proceeds by sending a request to update a record to Database.

3. Database receives the request from MVCC and confirms that the transaction has started.

4. Client executes the UPDATE query to update a record in the Details table. In this example, the record with ID 1 will be updated with a new value for the Score field of 90.

5. MVCC receives the UPDATE query from Client and proceeds by sending it to Database.

6. Database performs the update of the record in the Details table based on the given UPDATE query and then sends a response to MVCC to inform that the record has been updated.

7. Client ends the transaction by sending a request to end the transaction to MVCC.

8. MVCC proceeds by sending the request to end the transaction to Database.

9. Database receives the request to end the transaction from MVCC and confirms that the transaction has ended.

Thus, the process of updating a record in the Database via MVCC includes the steps of starting the transaction, executing the UPDATE query, and ending the transaction, ensuring concurrency and avoiding conflicts between different transactions.

Process of reading data from the database

sequenceDiagram
    participant Client as "Client"
    participant MVCC as "MVCC"
    participant Database as "Database"

    Client->>+MVCC: Begin Transaction (Read Data)
    MVCC->>+Database: Begin Transaction (Read Data)
    Database-->>-MVCC: Response (Read Transaction Started)

    Client->>+MVCC: SELECT * FROM Details WHERE StudentID = 1
    MVCC->>+Database: SELECT * FROM Details WHERE StudentID = 1
    Database-->>-MVCC: Response (Query Result)

    Client->>+MVCC: End Transaction (Read Data)
    MVCC->>+Database: End Transaction (Read Data)
    Database-->>-MVCC: Response (Read Transaction Ended)

1. Client starts a read data transaction and sends a request to MVCC.

2. MVCC continues by sending a read data request to Database.

3. Database processes the request and returns the result (data) to MVCC.

4. MVCC returns the read data result to Client.

5. Finally, Client ends the read data transaction.

In this process, the Client requests to read data from the database through MVCC, and then MVCC acts as an intermediary to send the request to the Database. The result of the query is data returned from the database through MVCC. After the read data transaction is complete, the Client ends the transaction.

Advantages of MVCC

1. Provides concurrency by allowing multiple transactions to occur simultaneously.
2. Each transaction is independent of others.
3. Creates multiple versions of the database, providing real-time data recovery capabilities.

Disadvantages of MVCC

1. MVCC requires more memory and can be more complex than regular databases.
2. They can be slower due to processing multiple queries at once.
3. Old versions of data may not be removed from the database, leading to an increase in database size.