单行INSERT是原子的吗?例如.在具有1M列的表上? [英] Is a single row INSERT atomic? E.g. on a table with 1M columns?

问题描述

是单行INSERT原子(用于外部读取器)吗?想象一下，它发生在具有1M列的表上.

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

在执行单个 INSERT 语句(即单行"类型)时，是否可能同时发生读操作(可能使用读未提交"隔离级别)只能读取一些的值(列)?

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) ?

我对MS SQL Server的行为特别感兴趣，尽管我认为所有主要供应商的行为都相似.

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.

推荐答案

如注释中所述，SQL Server不支持一百万列.但是，您应该只用两列就可以观察到该问题(在适当的情况下).

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.

使用 READUNCOMMITTED (或 NOLOCK )时，答案为是".在文档中查找参考非常困难，但是这里是谈论它的博客.

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.

关键问题是跨越多个页面的行.只要您有LOB或 varchar(max)或类似类型的列，便会出现这种情况.如果另一个事务正在更新包含大对象的值的页面，则并发的 READUNCOMMITTED 查询可以读取部分值.如果您有多个大对象，也是如此.有些可能具有新值，有些可能具有旧值.因此，您可以在一条记录中获得不一致的结果.

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.

此外，在较高的隔离级别下也不应该发生这种情况.实际上， READ_UNCOMMITTED 是一种绕过某些数据库完整性检查以提高性能的方法，这需要对某些ACID属性进行必要的放宽.

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.

这篇关于单行INSERT是原子的吗?例如.在具有1M列的表上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！