Re: transactions on reads?
From: Daniel Billingsley (dbillingsley_at_NO.durcon.SPAAMM.com)
Date: 04/22/04
- Next message: Jeff Duncan: "Re: If the else statement inside stored procedure"
- Previous message: Jacco Schalkwijk: "Re: Check Constraint ##-##-##"
- In reply to: Daniel Billingsley: "transactions on reads?"
- Next in thread: Tibor Karaszi: "Re: transactions on reads?"
- Reply: Tibor Karaszi: "Re: transactions on reads?"
- Reply: Michael, Cheng [MSFT]: "Re: transactions on reads?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Apr 2004 10:49:39 -0400
Wait a minute... in the Books Online under the topic "Cursor Transaction
Isolation Levels" (under "isolation levels" then "cursors" in the index) it
seems to imply that by default selects have Read Committed isolation. That
is the cursor itself has that isolation even without the query being
executed within an explicit transaction. In other words it would be simply
redundant to place a select query within a transaction that had Read
Committed isolation. Am I understanding that right?
"Daniel Billingsley" <dbillingsley@NO.durcon.SPAAMM.com> wrote in message
news:uf6TcWHKEHA.892@TK2MSFTNGP09.phx.gbl...
> I'm trying to fully understand the concept of using a transaction with
Read
> Committed isolation for a select query.
>
> I think I understand the basics of how it protects you from dirty reads.
> However, I'm wondering about the practical reality.
>
> *The base question is do I really want to slow down all my select queries
> with transactions?
>
> I'll toss out some thoughts that lead me to think the answer is "no" and
get
> some feedback.
>
> 1) I'm assuming there is significant overhead to wrapping the select in a
> transaction.
>
> 2) In a properly normalized database, a master-detail table relationship
is
> always "in sync" by definition, isn't it? By that I mean, for example,
the
> Invoice total is defined as the total of the InvoiceLine records and not a
> column in the Invoice table, so there will never be any logical issue
there.
>
> 3) The instant any given query is completed the data returned may be
> obsolete or "dirty". That is, one of the InvoiceLine records could be
> deleted immediately after I retrieve the Invoice and InvoiceLines for a
> report, and I'll never know unless I run the report again. We all live
with
> that kind of dirty data.
>
> 4) The potential for dirty data that this will protect me from exists
only
> in the time span between a change being updated and it being committed.
For
> a simple update, this is presumably extremely miniscule, isn't it?. Given
> #2 and #3 above are true anyway, this seems like a very low risk of
anything
> really nasty. However, I would expect a fairly long-running series of
> updates that are all wrapped in a transaction could possibly result in
that
> time gap being significant. So, it seems to me that a big part of
answering
> this question is analysis of the type of application and how many of what
> types of update queries using transactions it will be running.
>
> Any thoughts or comments are appreciated, as well as any good links on the
> subject.
>
>
- Next message: Jeff Duncan: "Re: If the else statement inside stored procedure"
- Previous message: Jacco Schalkwijk: "Re: Check Constraint ##-##-##"
- In reply to: Daniel Billingsley: "transactions on reads?"
- Next in thread: Tibor Karaszi: "Re: transactions on reads?"
- Reply: Tibor Karaszi: "Re: transactions on reads?"
- Reply: Michael, Cheng [MSFT]: "Re: transactions on reads?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
