[DUG] MSSQL Question

[Kyley Harris]

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 <kyleyharris at gmail.com> 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 <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
>



-- 
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/78a8ead9/attachment.html