[DUG] MSSQL Question

[Kyley Harris]

Thanks. Its an issue when every database vendor does it wildly different..
sigh...I haven't been using MSSQL a long time. I like it.. but sometimes I
dont :)

On Tue, Dec 2, 2008 at 10:58 AM, Paul Heinz <paul at accredo.co.nz> wrote:

> 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 at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>



-- 
Kyley Harris
Harris Software
+64-21-671-821
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081202/c42376e8/attachment.html