Re: changing the isolation level

Frank,

I disagree with the notion that Read Uncommitted is unnecessary. It is all based upon the business requirement. There are business requirements that require only approximations and do not need to be audited. Below is a real life scenario that would require substantial increase in hardware as well as an increase in the development and maintenance functions if it weren't for Read Uncommitted.

Environment:
200 million micro-transactions per day.
Real-time querying of the transactions to keep operational metrics for alerts, notifications, etc.
Single row transactions in three tables from 60 web servers.

Requirement:
Run a count every five minutes to factor transactions/minute (150K transactions per minute at peak). Raise an alert if the count is plus or minus 1.5 standard deviations from the norm at that time of the day.

If I am counting 750K transactions over a five minute period, of what consequence is it if I overcount even a few hundred because of transaction rollback?

Of course, my application logs the failed transactions to fix the application and eliminate the rollback in the future. And, there is another near real time metric that counts transaction failures.

It would double, triple, quadruple? the harware and licensing costs to increase the processing capacity to allow the above query to run without lock contention affecting the transations. My CEO is fine with this approximation.

• Bill

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:eogokt$62e$1_at_news1.zwoll1.ov.home.nl...
> Marten Lehmann schreef:
> [snip]
>> Think of a script, that generates invoices montly. Lets say you have a
>> average volume of $1000 dollar. So if a run of the script in the next
>> month just returns a volume of $200 you can be pretty sure, that
>> something has gone wrong. But if you cannot see this until you confirmed
>> all the stuff, then it is too late to revoke it.
> [snip]
>
> I did when I replied the concept of transaction was misunderstood.
> Take your example:
> Step 1): check if account_from has enough $$ (account_from - amount >0)
> Step 2): transfer the amount to account_to (which happens to be mine...)
> Step 3): repeat steps 1 & 2 x times; different amounts, same account,
> of course :), until step1 fails.
> Step 4) rollback.
>
> Now - what would you like to be the outcome of that? I would very
> much like to see my transfers failed due to insufficient funding, and
> my balance to be untouched.
>
> In your scenario, after step 2 I have your money available! I can
> swiftly transfer that to my secret swiss account (so secret even I
> forgot about it).
>
> Who's gonna end up paying for that?
>
> Bottom line: your application needs a major rewrite. Or stick with
> bye-bye-Base
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
Received on Tue Jan 16 2007 - 14:30:28 CST