Thanks everyone. I've decided to leave the DB on Read/Commited, Reports on Repeatable Read, and as neven said Serializable in the single stored proc and remove the lock hints.
Should work :) On Tue, Dec 2, 2008 at 11:03 AM, Kyley Harris <[EMAIL PROTECTED]> wrote: > 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 <[EMAIL PROTECTED]> 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@delphi.org.nz >> Admin: http://delphi.org.nz/mailman/listinfo/delphi >> Unsubscribe: send an email to [EMAIL PROTECTED] with Subject: >> unsubscribe >> > > > > -- > Kyley Harris > Harris Software > +64-21-671-821 > -- Kyley Harris Harris Software +64-21-671-821
_______________________________________________ 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
