Re: Transaction around a SELECT statement

From: Steve Kass (skass_at_drew.edu)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 16:25:46 -0400

Craig,

  I think I addressed this. From my post:

"If you want to protect against phantom reads (from inserts) as well as
dirty reads (from updates), you can use SERIALIZABLE as the transaction
isolation level, but with a greater risk of deadlocks."

In case I wasn't clear, REPEATABLE READ blocks UPDATEs, and SERIALIZABLE
blocks both UPDATEs and INSERTs.

Steve

"Craig Kenisston" <craigkenisston@hotmail.com> wrote in message
news:uY%23kHCbmEHA.1672@TK2MSFTNGP14.phx.gbl...
>
> Steve :
>
> In your sample code, another connection is still being able to insert into
> table T.
> I think in your code you meant "Serializable", instead of "Repeatable
> Read".
>
>
> Regards,
>
>
>
>
>
> "Steve Kass" <skass@drew.edu> wrote in message
> news:uZj8g6ZmEHA.3428@TK2MSFTNGP14.phx.gbl...
>> Rachel,
>>
>> While I can't think of why you'd need to put a *single* SELECT
>> statement into a transaction, you might want to put a block including
>> more than one SELECT statement into a transaction, even if there are no
>> inserts or updates. Consider this example:
>>
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> GO
>>
>> CREATE TABLE T (
>> i int
>> )
>> GO
>> INSERT INTO T VALUES (1)
>> GO
>>
>> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
>> GO
>> BEGIN TRANSACTION
>> SELECT * FROM T
>> WAITFOR DELAY '00:00:08'
>> -- during this delay, an update is issued against table T
>> -- by another connection to change i to -1
>> SELECT * FROM T
>> COMMIT TRANSACTION
>> GO
>>
>> DROP TABLE T
>>
>> With the BEGIN TRANSACTION .. COMMIT TRANSACTION block in place, the
>> out-of-transaction update will be blocked until this transaction ends,
>> and the the two SELECT statements will agree. Without the transaction
>> block, the update will happen immediately when issued, and the two
>> SELECT statements will disagree.
>>
>> If you want to protect against phantom reads (from inserts) as well as
>> dirty reads (from updates), you can use SERIALIZABLE as the transaction
>> isolation level, but with a greater risk of deadlocks.
>>
>> While this example probably doesn't look like useful code, multiple
>> selects to accomplish one task are conceivable. You may need to select
>> from a table, make some calculations or verify some things, then select
>> again, based on what you find from first selection. In order for the
>> isolation level to apply between the separate queries, you must
>> enclosing them in a single transaction.
>>
>> Steve Kass
>> Drew University
>>
>> Rachel K wrote:
>>
>> >I am working with several developers who feel that it is
>> >necessary to use a transaction around a select statement
>> >in order to prevent 'dirty reads'. I believe this is
>> >handled with the isolation level, but that a transaction
>> >around a select is unnessecary. Anyone have any thoughts
>> >or proof?
>> >
>> >
>
>

Relevant Pages

  • RE: Master/Detail Transactions
    ... If any of the updates fails, ... pending parent and child rows within the scope of a transaction, ... cascade newly retrieved parent identity values down to pending child rows ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I absolutely think that in extremely large jobs, ... I know, I could send a single transaction containing 10,000 records but it ... > physical memory to accommodate the TWO sets of 100,000 records: ... >> The statement you make about the individual updates not hitting the ...
    (microsoft.public.data.ado)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... The statement you make about the individual updates not hitting the database ... to do with the updates being in a transaction but rather on whether you specify ... spill over and the memory starts paging out to disk that concerns me). ...
    (microsoft.public.data.ado)
  • Re: Using transactions to insert into to a table while allowing read access to existing data
    ... purpose but to roll back the transaction. ... rows with uncommitted updates are marked with locks. ... isolation level is READ COMMITTED, ... then finally committing the transaction. ...
    (microsoft.public.sqlserver.programming)
  • Master/Detail Transactions
    ... If any of the updates fails, ... attempt to run the transaction as an ADO.net SqlTransaction, ... just don't know how to deal with the Master/Detail relationship. ... accomplish this with an Identity Field? ...
    (microsoft.public.dotnet.framework.adonet)
Loading