MVCC Conflict Errors

Nick Lamb -

An MVCC conflict error will arise when overlapping transactions create a conflict that can only be resolved by killing one of the two transactions. The best example of this is when two transactions try to modify the same row(s). Overlapping transactions affecting different rows of the same table will not cause an MVCC error. It is best practices to insert retry logic for errors of this type into the application.

For most workloads, the primary reason for seeing the "Container MVCC conflict" error is that a later transaction has committed work in the same place. The most common reason for this is an INSERT statement, which does not acquire locks to do work, has completed a transaction while the earlier statement prepares to commit.

The "MVCC serializable scheduler conflict" error, on the other hand, arises when a user transaction conflicts with some system transaction (only system transactions use serializable isolation), such as a rebalancer action. This error can also result from writing to a relation during an ALTER. 

To learn more about MVCC, please see our documentation: Concurrency Control

Have more questions? Submit a request

1 Comments

  • 0
    Avatar
    Scott Sullivan

    We keep getting questions about this so I thought I'd provide another example. In many cases where transactions are contending over the same row(s), our MVCC implementation does the correct thing. For instance, two transactions updating the same row get applied in the order of their transactions. While inside their transactions they will even see the state relevant to their transaction start time.

    However, in the case of inserts with a declarative primary key, we can get into trouble. For example, the following starts two transactions which attempt to explicitly insert a row with the primary key set to '3'. Since both transactions start around the same time, they both see the table as having only two rows. Application logic may assume primary key 3 is valid. But MVCC knows this is a problem and errors on the second attempt.

    TRX-A> show create table foo;
    +-------+---------------------------------------------------------------------+
    | Table | Create Table |
    +-------+---------------------------------------------------------------------+
    | foo | CREATE TABLE foo ( |
    | | id int(11) not null AUTO_INCREMENT, |
    | | value varchar(16) CHARACTER SET utf8, |
    | | PRIMARY KEY (id) /*$ DISTRIBUTE=1 */ |
    | | ) AUTO_INCREMENT=3 CHARACTER SET utf8 /*$ REPLICAS=2 SLICES=4 */ |
    +-------+---------------------------------------------------------------------+
    1 row in set (0.01 sec)

    TRX-A> begin;
    Query OK, 0 rows affected (0.01 sec)

    TRX-A> select * from foo;
    +----+-------+
    | id | value |
    +----+-------+
    | 1 | hello |
    | 2 | world |
    +----+-------+
    2 rows in set (0.00 sec)

    TRX-B> begin;
    Query OK, 0 rows affected (0.00 sec)

    TRX-A> insert into foo (id, value) values (3,'first');
    Query OK, 1 row affected (0.00 sec)

    TRX-B> select * from foo;
    +----+-------+
    | id | value |
    +----+-------+
    | 2 | world |
    | 1 | hello |
    +----+-------+
    2 rows in set (0.01 sec)

    TRX-B> insert into foo (id, value) values (3,'second');
    ERROR 1 (HY000): [5122] Container MVCC conflict: foo.__idx_foo__PRIMARY (3)
    TRX-B>

    TRX-A> commit;
    Query OK, 0 rows affected (0.00 sec)

    TRX-A> select * from foo;
    +----+-------+
    | id | value |
    +----+-------+
    | 1 | hello |
    | 2 | world |
    | 3 | first |
    +----+-------+
    3 rows in set (0.00 sec)

    TRX-A>

    Interestingly, if you try this without declaring the PK, it works out fine. The underlying auto-inc counter knows what's up and gives the second row the id = 4 value.

    This is different than merely inserting into a table with a duplicate Primary Key: 

    TRX-A> insert into foo (id, value) values (3,'another first');
    ERROR 1062 (23000): [5120] Duplicate key in container: foo.__idx_foo__PRIMARY (3)
    TRX-A>

    In the MVCC conflict case, it's a duplicate key, but in an MVCC sequence conflict. It's even possible to get the first transaction killed if the second transaction performed the insert chronologically prior to the first. ClustrixDB knows it's a duplicate key problem, but in the MVCC context, we just have to kill one of the transactions.

    The way to handle this in your application is to first try to avoid specifying primary keys and second, program your application to retry in the event of such an error. Unless the first transaction is held open for abnormally long times, a subsequent attempt will get a clean view of the data and proceed unimpeded.

Please sign in to leave a comment.
Powered by Zendesk