cross-column & cross-row consistency Sql server 2005 sp2.

(The previous edition of this post has some written errors
which can make what I say unclear .Now those written errors are correctd.)

[cross-column & cross-row consistency]
Transaction Consistency In Sql server 2005 sp2.

How are cross-column and cross-row consistency in SQL SERVER 2000/2005?

Now there is a table named T,and its structure is as follow:
T(value , twice_value),
which means [twice_value] is twice of [value].
I filled it with some rows ,then it looks as follow:
--
value |twice_value
--
1 |2
32 |64
78 |156
--
Then I run the statement:
-
Update T
set
[value] = [value] * 2 ,
[twice_value] = [twice_value] * 2
--(In the previous edition of this post ,here the " * 2 " was fogetten)
-
At this point , towpuzzles arise into my brain:
[1].Is there some isolation leve in which
[VALUES 1] can be read possibly in some cases?

[VALUES 1]
--
value |twice_value
--
1 |2
64 |128
78 |156
--
(Note: every row is consistent,
but the table[64|128] is not consistent against the above "upate".)

[2].Is there some isolation leve in which
[VALUED 2] can be read possibly in some cases?

[VALUED 2]
--
value |twice_value
--
2 |2
32 |64
78 |156
--
(Note:the first row[2|2] it self is not consistent)

What I can make sure is that
[VALUED 1] can be read possibly in some cases when
Table T is updated by the following statement.
-
Update T
set
[value] = [value] * 2 ,
[twice_value] = [twice_value] * 2
--(In the previous edition of this post ,here the " * 2 " was fogetten)
where [value] = 32
Update T
set
[value] = [value] * 2 ,
[twice_value] = [twice_value] * 2
--(In the previous edition of this post ,here the " * 2 " was fogetten)
where not([value] = 32)
-

by the way ,

Is there Anyone that knows available e-paper about "log storage and data storage"?

|||SO EARGERLY FOR SOME ANSWERS.|||MARK|||up|||First, the update command you have listed above cannot create the values you show, since you set [twice_value]=[twice_value], unless you have a trigger setting the twice_value.

Second, the UPDATE statement creates an implicit transaction. Which means either all the records update or none of the records update and if there is a lock, it will wait for that lock to release. So there should be no possible way to create the results you describe after the query succeeds. Unless, you have something else changing the data, like a trigger.

If the isolation level was set to "READ UNCOMMITTED", a query could return what you describe, while the update was running. This is not unique to SP2.

With my corrected UPDATE statement, I cannot duplicate what you describe under any of the 3 versions of 2005, including SP2, I have or 2000 SP4, after the query has finished successfully.
Please post a duplication script of your problem.

|||

As Tom Phillips already mentioned your actual scenario is not really clear. But assuming that Tom's interpretation of your question is correct (except in the part that he as far as I can see implicitly supposes that you never run the queries that observe the data in the table concurrently with the updates – but I’m not 100% sure if this time I interpret him correctly), the answers are the following:

In general when reading the row using the interfaces available through T-SQL the transactional cross-column consistency for a single row is guaranteed by the engine for all isolation levels except the READ UNCOMMITTED. One example when the cross-column consistency could be violated when using dirty reads is when certain columns are not physically stored in the row – those include off-row LOB values or the variable length non-LOB columns that were pushed off-row because they don’t fit the data page.

The cross-row consistency is a different story and in order to be absolutely certain that an arbitrary DML statement will never see inconsistent results, one should use the SERAILIZABLE isolation level. For the queries is could also be guaranteed by the SNAPSHOT isolation level. However, in your particular example of the UPDATE statement where no rows are added or deleted, the REPEATABLE READ would also guarantee both the cross-column for a single row, and the cross-row transactional consistency for the entire table for any kind of query.

|||up|||UP|||UP