> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/gridgain/gridgain-8/architecture/mvcc.md).

# Multiversion Concurrency Control

Multiversion concurrency control (MVCC) in GridGain 8, enabled through the TRANSACTIONAL\_SNAPSHOT atomicity mode, its limitations, and how to use it.

{% hint style="warning" %}
The `TRANSACTIONAL_SNAPSHOT` atomicity mode, which enables MVCC, is deprecated. It remains in beta and is not recommended for production use.
{% endhint %}

## Overview

Caches with the `TRANSACTIONAL_SNAPSHOT` atomicity mode support SQL transactions as well as [key-value transactions](/docs/gridgain/gridgain-8/gridgain8-development/accessing-data/transactions.md) and enable multiversion concurrency control (MVCC) for both types of transactions.

By default, SQL operations in GridGain 8 are not transactional, so [concurrent queries](/docs/gridgain/gridgain-8/gridgain8-development/accessing-data/sql/sql-introduction.md#concurrent-queries) may read partially committed data from other operations. This beta implementation of MVCC aims to solve this by creating consistent data snapshots for each transaction. Current MVCC implementation is not recommended for production environments.

{% hint style="info" %}
In [GridGain 9](https://mariadb.com/docs/gridgain/gridgain-9/), all SQL operations are transactional by default.
{% endhint %}

## Limitations

### Cross-Cache Transactions

The `TRANSACTIONAL_SNAPSHOT` mode is enabled per cache and does not permit caches with different atomicity modes within the same transaction. As a consequence, if you want to cover multiple tables in one SQL transaction, all tables must be created with the `TRANSACTIONAL_SNAPSHOT` mode.

### Nested Transactions

GridGain supports three modes of handling nested SQL transactions. They can be enabled via a JDBC/ODBC connection parameter.

```bash
jdbc:ignite:thin://127.0.0.1/?nestedTransactionsMode=COMMIT
```

When a nested transaction occurs within another transaction, the `nestedTransactionsMode` parameter dictates the system behavior:

* `ERROR` — When the nested transaction is encountered, an error is thrown and the enclosing transaction is rolled back. This is the default behavior.
* `COMMIT` — The enclosing transaction is committed; the nested transaction starts and is committed when its COMMIT statement is encountered. The rest of the statements in the enclosing transaction are executed as implicit transactions.
* `IGNORE` — DO NOT USE THIS MODE. The beginning of the nested transaction is ignored, statements within the nested transaction will be executed as part of the enclosing transaction, and all changes will be committed with the commit of the nested transaction. The subsequent statements of the enclosing transaction will be executed as implicit transactions.

### Continuous Queries

If you use [Continuous Queries](/docs/gridgain/gridgain-8/gridgain8-development/accessing-data/continuous-queries.md) with an MVCC-enabled cache, there are several limitations that you should be aware of:

* When an update event is received, subsequent reads of the updated key may return the old value for a period of time before the MVCC-coordinator learns of the update. This is because the update event is sent from the node where the key is updated, as soon as it is updated. In such a case, the MVCC-coordinator may not be immediately aware of that update, and therefore, subsequent reads may return outdated information during that period of time.
* There is a limit on the number of keys per node a single transaction can update when continuous queries are used. The updated values are kept in memory, and if there are too many updates, the node might not have enough RAM to keep all the objects. To avoid OutOfMemory errors, each transaction is allowed to update at most 20,000 keys (the default value) on a single node. If this value is exceeded, the transaction will throw an exception and will be rolled back. This number can be changed by specifying the `IGNITE_MVCC_TX_SIZE_CACHING_THRESHOLD` system property.

### Other Limitations

The following features are not supported for the MVCC-enabled caches. These limitations may be addressed in future releases.

* [Near Caches](/docs/gridgain/gridgain-8/gridgain8-development/caches-and-memory/near-caches.md)
* [Expiry Policies](/docs/gridgain/gridgain-8/gridgain8-development/caches-and-memory/configuring-caches/expiry-policies.md)
* [Events](/docs/gridgain/gridgain-8/gridgain8-development/messaging-and-events/events/listening-to-events.md)
* [Cache Interceptors](https://www.gridgain.com/sdk/8.9.38/javadoc/org/apache/ignite/cache/CacheInterceptor.html)
* [External Storage](/docs/gridgain/gridgain-8/gridgain8-development/caches-and-memory/persistence/external-storage.md)
* [On-Heap Caching](/docs/gridgain/gridgain-8/gridgain8-development/caches-and-memory/configuring-caches/on-heap-caching.md)
* [Explicit Locks](https://www.gridgain.com/sdk/8.9.38/javadoc/org/apache/ignite/IgniteCache.html#lock-K-)
* The [localEvict()](https://www.gridgain.com/sdk/8.9.38/javadoc/org/apache/ignite/IgniteCache.html#localEvict-java.util.Collection-) and [localPeek()](https://www.gridgain.com/sdk/8.9.38/javadoc/org/apache/ignite/IgniteCache.html#localPeek-K-org.apache.ignite.cache.CachePeekMode...-) methods

## Multiversion Concurrency Control

Multiversion Concurrency Control (MVCC) is a method of controlling the consistency of data accessed by multiple users concurrently. MVCC implements the [snapshot isolation](https://en.wikipedia.org/wiki/Snapshot_isolation) guarantee which ensures that each transaction always sees a consistent snapshot of data.

Each transaction obtains a consistent snapshot of data when it starts and can only view and modify data in this snapshot. When the transaction updates an entry, GridGain verifies that the entry has not been updated by other transactions and creates a new version of the entry. The new version becomes visible to other transactions only when and if this transaction commits successfully. If the entry has been updated, the current transaction fails with an exception (see the [Concurrent Updates](#concurrent-updates) section for the information on how to handle update conflicts).

The snapshots are not physical snapshots but logical snapshots that are generated by the MVCC-coordinator: a cluster node that coordinates transactional activity in the cluster. The coordinator keeps track of all active transactions and is notified when each transaction finishes. All operations with an MVCC-enabled cache request a snapshot of data from the coordinator.

## Enabling MVCC

To enable MVCC for a cache, use the `TRANSACTIONAL_SNAPSHOT` atomicity mode in the cache configuration. If you create a table with the `CREATE TABLE` command, specify the atomicity mode as a parameter in the `WITH` part of the command:

{% tabs %}
{% tab title="XML" %}

```xml
<bean class="org.apache.ignite.configuration.IgniteConfiguration">

    <property name="cacheConfiguration">
        <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name="name" value="myCache"/>
            <property name="atomicityMode" value="TRANSACTIONAL_SNAPSHOT"/>
        </bean>
    </property>

</bean>
```

{% endtab %}

{% tab title="SQL" %}

```sql
CREATE TABLE Person WITH "ATOMICITY=TRANSACTIONAL_SNAPSHOT"
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
The `TRANSACTIONAL_SNAPSHOT` mode only supports the default concurrency mode (`PESSIMISTIC`) and default isolation level (`REPEATABLE_READ`). See [Concurrency modes and isolation levels](/docs/gridgain/gridgain-8/gridgain8-development/accessing-data/transactions.md#concurrency-modes-and-isolation-levels) for details.
{% endhint %}

## Concurrent Updates

If an entry is read and then updated within a single transaction, it is possible that another transaction could be processed in between the two operations and update the entry first. In this case, an exception is thrown when the first transaction attempts to update the entry and the transaction is marked as "rollback only". You have to retry the transaction.

This is how to tell that an update conflict has occurred:

* When Java transaction API is used, a `CacheException` is thrown with the message `Cannot serialize transaction due to write conflict (transaction is marked for rollback)` and the `Transaction.rollbackOnly` flag is set to `true`.
* When SQL transactions are executed through the JDBC or ODBC driver, the `SQLSTATE:40001` error code is returned.

{% tabs %}
{% tab title="Ignite Java" %}

```java
for(int i = 1; i <=5 ; i++) {
    try (Transaction tx = Ignition.ignite().transactions().txStart()) {
        System.out.println("attempt #" + i + ", value: " + cache.get(1));
        try {
            cache.put(1, "new value");
            tx.commit();
            System.out.println("attempt #" + i + " succeeded");
            break;
        } catch (CacheException e) {
            if (!tx.isRollbackOnly()) {
              // Transaction was not marked as "rollback only",
              // so it's not a concurrent update issue.
              // Process the exception here.
                break;
            }
        }
    }
}
```

{% endtab %}

{% tab title="JDBC" %}

```java
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1");

PreparedStatement updateStmt = null;
PreparedStatement selectStmt = null;

try {
    // starting a transaction
    conn.setAutoCommit(false);

    selectStmt = conn.prepareStatement("select name from Person where id = 1");
    selectStmt.setInt(1, 1);
    ResultSet rs = selectStmt.executeQuery();

    if (rs.next())
        System.out.println("name = " + rs.getString("name"));

    updateStmt = conn.prepareStatement("update Person set name = ? where id = ? ");

    updateStmt.setString(1, "New Name");
    updateStmt.setInt(2, 1);
    updateStmt.executeUpdate();

    // committing the transaction
    conn.commit();
} catch (SQLException e) {
    if ("40001".equals(e.getSQLState())) {
        // retry the transaction
    } else {
        // process the exception
    }
} finally {
    if (updateStmt != null) updateStmt.close();
    if (selectStmt != null) selectStmt.close();
}
```

{% endtab %}

{% tab title="C#/.NET" %}

```csharp
for (var i = 1; i <= 5; i++)
{
    using (var tx = ignite.GetTransactions().TxStart())
    {
        Console.WriteLine($"attempt #{i}, value: {cache.Get(1)}");
        try
        {
            cache.Put(1, "new value");
            tx.Commit();
            Console.WriteLine($"attempt #{i} succeeded");
            break;
        }
        catch (CacheException)
        {
            if (!tx.IsRollbackOnly)
            {
                // Transaction was not marked as "rollback only",
                // so it's not a concurrent update issue.
                // Process the exception here.
                break;
            }
        }
    }
}
```

{% endtab %}

{% tab title="C++" %}

```cpp
for (int i = 1; i <= 5; i++)
{
    Transaction tx = ignite.GetTransactions().TxStart();
    std::cout << "attempt #" << i << ", value: " << cache.Get(1) << std::endl;
    try {
        cache.Put(1, "new value");
        tx.Commit();
        std::cout << "attempt #" << i << " succeeded" << std::endl;
        break;
    }
    catch (IgniteError e)
    {
        if (!tx.IsRollbackOnly())
        {
            // Transaction was not marked as "rollback only",
            // so it's not a concurrent update issue.
            // Process the exception here.
            break;
        }
    }
}
```

{% endtab %}
{% endtabs %}