Is a single row INSERT atomic? E.g. on a table with 1M columns?

Question

Is a single row INSERT atomic (for an external reader)? Imagine it happens on a table with 1M columns.

While executing a single INSERT statement (namely, the "single row" kind), is it possible for a read operation (maybe using the 'Read uncommitted' isolation level) occurring at the same time to only read some of the values (columns) ?

I'm particularly interested in MS SQL Server's behaviour, although I assume this is similar for all major vendors.

Bonus cred points for a link to official documentation on the matter.

Answer 1

only read some of the values ?

Yes, that can happen. DML is atomic in the sense that the writes are persisted all or nothing but concurrent reads can observe partial writes in very weird ways. For multi-row writes this is clearly possible but even single row writes are not atomic to readers.

For example you might find that the insert is completed for one non-clustered index but not for the other. This is perceivable for readers in rare, contrived cases. For example if you say:

select *
from T t1 with (index(1))
full outer join T t2 with (index(2)) on (someUniqueCondition)

You effectively get the difference between two indexes which can be non-zero. Index hints are not necessary to achieve this demo.

Answer 2

As mentioned in a comment, SQL Server does not support one million columns. However, you should be able to observe the problem (under the right circumstances) with just two columns.

The answer is "yes", when using READUNCOMMITTED (or NOLOCK). Finding a reference in the documentation is quite difficult, but here is a blog that talks about it.

The key issue are rows that span multiple pages. This is the case whenever you have a LOB or varchar(max) or similar type of column. If another transaction is updating pages containing values for the large object, then a concurrent READUNCOMMITTED query could read partial values. This is also true if you hae multiple large objects; some might have new values and some might have old values. So, you can get inconsistent results within a single record.

I don't think the same thing would happen (at least in practice) for a record stored on a single page. It might happen if you have a multi-statement transaction that updates different fields at different times, but not for a single statement.

Also, this should not happen with higher isolation levels. In effect, READ_UNCOMMITTED is a way to by-pass some of the database integrity checks for performance -- which entails a necessary relaxation of some of the ACID properties.