SELECT statement blocking UPDATE statement on a different table

  • Hi All,

    Working on SQL Server 2008 R2.

    I've starting noticing a straight forward select statement (select field1,field2 from table1 where field1 = variable1) cause a block against a UPDATE statement on a totally different tble (update table2 set field1 = variable1 where field2 = variable2)

    Also there are no triggers on either off those tables.

    Any help as to where I can starting looking to resolve this...or explain this behaviour.

    Thanks

    Denesh

  • Are you talking about Shared Locks?

    Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

    Please don't forget to check the Isolation Levels. It can change default query / lock behavior.

    Isolation Levels in the Database Engine

    http://msdn.microsoft.com/en-us/library/ms189122.aspx

  • I don't think select on a table blocks update on a different table.

    How did you notice this or come to this conclusion.

  • Is the update on table 2 part of a transaction that also looks at table1?

  • Hi All,

    I see this when I run sp_who2 'active'

    I see a SPID doing an UPDATE...and it shows it's being blocked by another SPID. If I then do a DBCC inputbuffer on that SPID...I can see it's doing a select on a totally different table

  • Denesh Naidoo (11/29/2011)


    Hi All,

    I see this when I run sp_who2 'active'

    I see a SPID doing an UPDATE...and it shows it's being blocked by another SPID. If I then do a DBCC inputbuffer on that SPID...I can see it's doing a select on a totally different table

    What do you see in status column?

    Process status. The possible values are:

    dormant. SQL Server is resetting the session.

    running. The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).

    background. The session is running a background task, such as deadlock detection.

    rollback. The session has a transaction rollback in process.

    pending. The session is waiting for a worker thread to become available.

    runnable. The session's task is in the runnable queue of a scheduler while waiting to get a time quantum.

    spinloop. The session's task is waiting for a spinlock to become free.

    suspended. The session is waiting for an event, such as I/O, to complete.

  • The status for the UPDATE spid shows SUSPENDED

    While the status for the SELECT spid (that is blocking the UPDATE spid above) showws RUNNABLE

  • Please post your SQL query.

    Generally, how much time it takes to execute?

  • dbcc inputbuffer won't neccessarily tell you the actual sql running at the time you run it, try this query:

    --How to isolate the current running commands in SQL Server. This query isolates the SQL in the batch

    -- actually running at this point rather than the last command to execute

    SELECT SDER.[session_id], SDER.[request_id],SDER.[statement_start_offset],

    SDER.[statement_end_offset],

    CASE

    WHEN SDER.[statement_start_offset] > 0 THEN

    --The start of the active command is not at the beginning of the full command text

    CASE SDER.[statement_end_offset]

    WHEN -1 THEN

    --The end of the full command is also the end of the active statement

    SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)

    ELSE

    --The end of the active statement is not at the end of the full command

    SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)

    END

    ELSE

    --1st part of full command is running

    CASE SDER.[statement_end_offset]

    WHEN -1 THEN

    --The end of the full command is also the end of the active statement

    RTRIM(LTRIM(DEST.[text]))

    ELSE

    --The end of the active statement is not at the end of the full command

    LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)

    END

    END AS [executing statement],

    DEST.[text] AS [full statement code]

    FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST

    WHERE SDER.session_id > 50

    ORDER BY SDER.[session_id], SDER.[request_id]

    ---------------------------------------------------------------------

  • Below is the SELECT query

    )SELECT PaymentHeaderRefNo, Queue, System FROM PaymentHistoryHeader WITH(NOLOCK) WHERE SourceReferenceKey = @P

    Below is the UPDATE that gets blocked. The SELECT completes quickly...within a second or 2. But I'm just confused as to why I'm seeing this behaviour

    )UPDATE AML

    SET AMLRefNo = @P1 ,TransactionDate = @P2 ,TransactionType = @P3 ,TransactionID = @P4 ,Amount = @P5

    ,OriginatingBankID = @P6 ,OriginatingAccountNumber = @P7,OriginatingAccountName = @P8,

    OriginatingBankCountryID = @P9 ,RecipientBankID = @P10

    , RecipientBankCountryID = @P11,

    FirstIntermediaryBankID = @P12 ,

    FirstIntermediaryCountryID = @P13,

    SecondIntermediaryBankID = @P14,

    SecondIntermediaryCountryID = @P15,

    BeneficiaryAccountNumber = @P16 ,

    BeneficiaryAccountName = @P17,

    AdditionalInformation = @P18,

    SourceSystemDate = @P19 ,

    ISOCurrencyCode = @P20 ,

    UnsignedOrderingAmount = @P21 ,

    ReceivingISOCurrencyCode = @P22,

    UnsignedReceivingAmount = @P23 ,

    OriginatingBankText = @P24 ,

    RecipientBankText = @P25

    WHERE AMLRefNo = @P26

  • Hi George

    I ran your query and it returns the 2 queries I've been speaking about in this post.

  • Is 'PaymentHistoryHeader' a View?

  • Hi Dev,

    No...it's a table...and so is AML

  • Denesh Naidoo (11/29/2011)


    Hi Dev,

    No...it's a table...and so is AML

    I don't find any link between these two code segments. I am not sure how they are connected?

  • That is what's got me so confused. There is no link at all between these 2 tables...I do not understand the reason for the SELECT to block an UPDATE on a totally different table. As I mentioned there are no triggers on either of these tables. I'm not sure what else to look at.

    This is not causing any problems...but I want to understand the reason for it.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply