4

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.

Cristian Diaconescu
  • 34,633
  • 32
  • 143
  • 233
  • Yes, they are, see this other related question http://stackoverflow.com/questions/1171749/what-does-a-transaction-around-a-single-statement-do – Giuseppe Romagnuolo Jun 20 '15 at 16:44
  • @GiuseppeR For me, a transaction means "allow rollback if an error happens". What I'm interested in is whether an INSERT operation can be observed half-way during its lifetime. Imagine inserting (or updating) a single row on a table with a million columns. This literally means 1M small write operations. My question is whether the first few writes can be observed by a reader before all 1M writes are complete. – Cristian Diaconescu Jun 20 '15 at 17:13
  • In case you haven't read this one yet, relevant: http://stackoverflow.com/questions/21468742/is-a-single-sql-server-statement-atomic-and-consistent – sstan Jun 20 '15 at 17:23
  • 2
    SQL Server doesn't allow a table to have any where near 1 million columns. Updating n columns doesn't mean n write operations. You might find the answer here relevant http://dba.stackexchange.com/a/95243/3690 – Martin Smith Jun 20 '15 at 17:30

2 Answers2

2

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • This is some scary stuff. As far as you know, is this SQL Server specific?, or can this happens with other RDBMS' that read data while in read uncommitted isolation level? And is there any documentation about this as OP is hoping for (and me too)? – sstan Jun 20 '15 at 17:21
  • Do you have a reference for this behavior? Your example suggests a violation of ACID properties, under normal usage. I understand how this could conceivably happen with certain hints, such as `READUNCOMMITTED`, but are indexes really not subject to transaction isolation level semantics? – Gordon Linoff Jun 20 '15 at 17:56
  • This is even possible under **repeatable read**. The read from index 1 would not see the data. The read from index 2 would wait for the transaction to commit and then see the data. Serializable, by definition, does not have this anomaly (it has none whatsoever). The reference for this behavior would be my explanation. If you don't agree I'd be happy to hear about it. – usr Jun 20 '15 at 19:39
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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786