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. _______________________________________________ NZ Borland Developers Group - Delphi mailing list Post: delphi@delphi.org.nz Admin: http://delphi.org.nz/mailman/listinfo/delphi Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: unsubscribe
