November 29, 2011 at 4:37 am
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
November 29, 2011 at 4:48 am
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
November 29, 2011 at 4:52 am
I don't think select on a table blocks update on a different table.
How did you notice this or come to this conclusion.
November 29, 2011 at 4:54 am
Is the update on table 2 part of a transaction that also looks at table1?
November 29, 2011 at 5:05 am
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
November 29, 2011 at 5:13 am
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.
November 29, 2011 at 5:24 am
The status for the UPDATE spid shows SUSPENDED
While the status for the SELECT spid (that is blocking the UPDATE spid above) showws RUNNABLE
November 29, 2011 at 5:30 am
Please post your SQL query.
Generally, how much time it takes to execute?
November 29, 2011 at 5:36 am
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]
---------------------------------------------------------------------
November 29, 2011 at 5:58 am
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
November 29, 2011 at 6:01 am
Hi George
I ran your query and it returns the 2 queries I've been speaking about in this post.
November 29, 2011 at 6:06 am
Is 'PaymentHistoryHeader' a View?
November 29, 2011 at 6:09 am
Hi Dev,
No...it's a table...and so is AML
November 29, 2011 at 6:16 am
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?
November 29, 2011 at 6:19 am
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