[DUG] MSSQL Question

[Paul Heinz]

Kyley wrote: 

> I thought Snapshot isolation is best for 
> reporting with concurrent data.. Hmm in interbase it versions 
> off data without locking other transactions.. does MSSQL just block?

In versions prior to SQL Server 2005, yes. It used to just block writes
to any unread row until it had been streamed to the requesting client.
So, readers blocked writers and writers blocked readers. The worst of
both worlds.

With SQL 2005, they implemented a solution similar to Oracles rollback
segments to support snapshot read isolation without blocking writes. In
fact, SQL 2005 specifically addressed the prime concern that stopped
sites migrating away from Oracle. Funny that :-)

It's still not as 'nice' an implementation as
Firebird/Interbase/Postgresql (i.e. proper MVCC). Oracle can exhaust
it's rollback segments (the dreaded ORA-01555) and SQL Server can run
out of TempDB space. This usually occurs in the face of long running
reporting transactions against a database facing high insertion or
mutation rates. In these cases, both Oracle and SQL Server abort the
reporting transaction which is rather 'unhelpful' but given their
implementation, they have no other option.

Anyway, to avoid reference to prayer and to understand more of how this
all works internally I recommend the late Jim Gray's book 'Transaction
Processing: Concepts and Techniques'. It's pretty much the Bible for
transaction processing IMO. It was invaluable when I was implementing
client-side transaction processing in the Accredo database engine.

TTFN,
  Paul.