Thanks everyone. <div><br></div><div>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.</div><div><br></div><div>
Should work :)<br><br><div class="gmail_quote">On Tue, Dec 2, 2008 at 11:03 AM, Kyley Harris <span dir="ltr"><<a href="mailto:kyleyharris@gmail.com">kyleyharris@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Thanks. Its an issue when every database vendor does it wildly different.. sigh...<div>I haven't been using MSSQL a long time. I like it.. but sometimes I dont :)<div><div></div><div class="Wj3C7c"><br><br><div class="gmail_quote">
On Tue, Dec 2, 2008 at 10:58 AM, Paul Heinz <span dir="ltr"><<a href="mailto:paul@accredo.co.nz" target="_blank">paul@accredo.co.nz</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>Kyley wrote:<br>
<br>
> I thought Snapshot isolation is best for<br>
> reporting with concurrent data.. Hmm in interbase it versions<br>
> off data without locking other transactions.. does MSSQL just block?<br>
<br>
</div>In versions prior to SQL Server 2005, yes. It used to just block writes<br>
to any unread row until it had been streamed to the requesting client.<br>
So, readers blocked writers and writers blocked readers. The worst of<br>
both worlds.<br>
<br>
With SQL 2005, they implemented a solution similar to Oracles rollback<br>
segments to support snapshot read isolation without blocking writes. In<br>
fact, SQL 2005 specifically addressed the prime concern that stopped<br>
sites migrating away from Oracle. Funny that :-)<br>
<br>
It's still not as 'nice' an implementation as<br>
Firebird/Interbase/Postgresql (i.e. proper MVCC). Oracle can exhaust<br>
it's rollback segments (the dreaded ORA-01555) and SQL Server can run<br>
out of TempDB space. This usually occurs in the face of long running<br>
reporting transactions against a database facing high insertion or<br>
mutation rates. In these cases, both Oracle and SQL Server abort the<br>
reporting transaction which is rather 'unhelpful' but given their<br>
implementation, they have no other option.<br>
<br>
Anyway, to avoid reference to prayer and to understand more of how this<br>
all works internally I recommend the late Jim Gray's book 'Transaction<br>
Processing: Concepts and Techniques'. It's pretty much the Bible for<br>
transaction processing IMO. It was invaluable when I was implementing<br>
client-side transaction processing in the Accredo database engine.<br>
<br>
TTFN,<br>
<font color="#888888"> Paul.<br>
</font><div><div></div><div><br>
<br>
<br>
<br>
_______________________________________________<br>
NZ Borland Developers Group - Delphi mailing list<br>
Post: <a href="mailto:delphi@delphi.org.nz" target="_blank">delphi@delphi.org.nz</a><br>
Admin: <a href="http://delphi.org.nz/mailman/listinfo/delphi" target="_blank">http://delphi.org.nz/mailman/listinfo/delphi</a><br>
Unsubscribe: send an email to <a href="mailto:delphi-request@delphi.org.nz" target="_blank">delphi-request@delphi.org.nz</a> with Subject: unsubscribe<br>
</div></div></blockquote></div><br><br clear="all"><br></div></div>-- <br><div class="Ih2E3d">Kyley Harris<br>Harris Software<br>+64-21-671-821<br>
</div></div>
</blockquote></div><br><br clear="all"><br>-- <br>Kyley Harris<br>Harris Software<br>+64-21-671-821<br>
</div>