CODEWITHSUNDEEP
sqlsql-serversql-server-2008transactionsisolation-level
George2
George2

Reputation: 45801

SQL Server snapshot isolation level issue

I am studying snapshot isolation level of SQL Server 2008 from the below link. My confusion is,

http://msdn.microsoft.com/en-us/library/ms173763.aspx

  1. It is mentioned "Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction." -- seems data committed by other transactions are not visible to the current snapshot isolation level transaction;

  2. It is mentioned "A transaction running under SNAPSHOT isolation level can view changes made by that transaction." -- seems data committed by other transactions are visible to the current snapshot isolation level transaction.

Seems 1 and 2 are conflicting? Any comments?

thanks in advance, George

Upvotes: 3

Views: 3838

Answers (3)

user3865096
user3865096

Reputation: 29

You have to think in big picture. READ_COMMITTED is only valid / useful. what will be use of READ_UNCOMMITTED? For what business will need dirty read? read serial ? Why any business requirement will force order on reading data. SQL server designer did not bother think about simplifying. Oracle chose to support only one and it works. Snapshot is how db should resolve this, so we shouldn't even know. With left brain, we should work on how to support business logic, not DB itself.

Upvotes: 0

A-K
A-K

Reputation: 17090

You also need to know the difference between SNAPSHOT and READ COMMITTED SNAPSHOT - for the latter, you need to modify your quote from BOL as follows:

"Data modifications made by other transactions after the start of the current STATEMENT (not transaction!) are not visible to statements executing in the current transaction."

An example of a case when it makes a big difference: When Snapshot Isolation Helps and When It Hurts

Upvotes: 1

gbn
gbn

Reputation: 432431

Number 2 means "I can see my own changes; I can't see other changes"

So if I start a transaction and I make changes, I can see them. Other transactions for other sessions/connections started after my TXN can not see my changes

Upvotes: 8

Related Questions