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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy