Does MVCC database isolation mode allow in-progress transactions to see rows inserted (and committed) by other transactions?
For example, given:
names[id BIGINT NOT NULL, name VARCHAR(30), PRIMARY KEY(id), UNIQUE(name)]
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
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
I have executed following statements almost at the same time in 2 separate SQL connections:
BEGIN; SELECT * FROM names; SELECT pg_sleep(10); INSERT INTO names values('john'); SELECT pg_sleep(10); COMMIT;
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.