Gili Gili - 2 months ago 7x
SQL Question

Do MVCC databases see inserted rows in mid-transaction?

Does MVCC database isolation mode allow in-progress transactions to see rows inserted (and committed) by other transactions?

For example, given:

  • Table
    names[id BIGINT NOT NULL, name VARCHAR(30), PRIMARY KEY(id), UNIQUE(name)]

  • Transactions T1 and T2,

T1: open transaction
T2: open transaction
T1: select * from names;
insert into names(name) values("John");
// do something
T2: select * from names;
insert into names values("John");
// do something

When does T2 first become aware of the new row? At
time? At
time? Or at

mvp mvp

Answer really depends on server implementation and whether unique constraint is marked deferrable or not.

I have not tested it for other databases, but in PostgreSQL (as one of most prominent open-source MVCC databases) in my test replicating your setup T2 fails on INSERT. However, T2 cannot see any changes made by T1 by using SELECT.

I have executed following statements almost at the same time in 2 separate SQL connections:

SELECT * FROM names;
SELECT pg_sleep(10);
INSERT INTO names values('john');
SELECT pg_sleep(10);

One succeeded, but another failed after 10 seconds with:

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name)=(john) already exists.

This makes sense, because documentation says:

If a conflicting row has been inserted by an as-yet-uncommitted transaction, the would-be inserter must wait to see if that transaction commits. If it rolls back then there is no conflict. If it commits without deleting the conflicting row again, there is a uniqueness violation.

If, however, unique constraint was marked deferrable, uniqueness will be checked at COMMIT time:

If the unique constraint is deferrable, there is additional complexity: we need to be able to insert an index entry for a new row, but defer any uniqueness-violation error until end of statement or even later.