Re: Transaction handling - Read Committed

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 13:29:57 -0400

Mani,

Yes this behavior is due to the Read Committed Isolation level requirements
and has nothing really to do with SQL Server. As Mark pointed out you can
use ReadPast or NOLOCK to complete the read but you must understand the
implications of each to determine if it is the correct approach. As Mark
also pointed out the next release of SQL Server will in fact provide the
behavior of being able to read the "before" values of locked rows. It is
called the SnapShot Isolation level. 

-- 
Andrew J. Kelly  SQL MVP
"Mani" <Mani@discussions.microsoft.com> wrote in message 
news:65B92718-E47D-48EE-B806-8D64FCA92DF3@microsoft.com...
> Hi Mark,
>
>  It's nice to know that there is a hint called READPAST that will skip the
> locked record. Thank you for responding.
>
> But, I think its a SQL server thing, where the record's previous state is
> not used to answer queries, if the record is in a newer state.
>
>  Although, I dont think the behavior is good enough, I was able to see why
> it behaves that way. The (READ COMITTED) read needs a shared lock on the 
> row
> and the update has an exclusive lock on the row till the transaction
> completes. Hence the read has to wait till the transaction completes.
>
>  So, I am still wondering if there is some hint in SQL server that will
> provide the feature I am looking for.
>
> Regards,
> Mani.
>
>
> "Mark Wilden" wrote:
>
>> "Mani" <Mani@discussions.microsoft.com> wrote in message
>> news:9F5C9EFE-9DD2-47B2-8F33-7ABB70FC4C50@microsoft.com...
>> >
>> > Basically, I update a row in a table and run a loop to create a delay,
>> > within a transaction.
>> >
>> > On another Query Analyser window i do the following, after running the
>> above
>> > script on the first window
>> >
>> > SET TRANSACTION ISOLATION LEVEL READ COMMITTED
>> > select * from Terms
>> >
>> > This query does not return with results till query 1 completes. 
>> > Shouldnt
>> > this query bring back the committed data that is available (whatever 
>> > the
>> > state of term record was before the txn in query1 updated it)
>>
>> The READPAST lock hint on the second query would skip the locked record 
>> (and
>> hence give "incorrect" results).
>>
>>
>>

Relevant Pages

  • Re: long running queries
    ... Mark is right -- you don't have much of an option with SQL Server 2000. ... >> that would result in a large data set and complex query, ... > Some of the client tools will warn you if the query looks like its going ...
    (microsoft.public.sqlserver.olap)
  • Re: Transaction handling - Read Committed
    ... > Yes this behavior is due to the Read Committed Isolation level requirements ... > and has nothing really to do with SQL Server. ... As Mark pointed out you can ... > Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)