Saturday, February 25, 2012
My SPID is sleeping but in a transaction holding resources!
Summary:
Q224453 doesn't help me resolve my sleeping SPID that's in a transaction and holding a resource, but with no wait type or wait time.
Details:
I come to this list after much investigation into a blocking problem I have been experiencing. After reading many of the posts here I'm optomistic of finding a lead to a resolution.
My investigations lead me to Q224453 (INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems) which was of great benefit, but did not help me fix my problem.
I am experiencing a Q224453 Scenario 2, or 'Blocking Caused by a Sleeping SPID That Has Lost Track of the Transaction Nesting Level'. In other words, I'm experiencing a spid in a transaction (trans_count>0) holding onto a resource, with no wait type (0x0000) or wait time(0). So it just sits there, SLEEPING, and majorly blocking other transactions.
When this block occurrs with no timeout values set, SQL Server does not resolve the block. No deadlock is found. (Possibly a Q239753 BUG: 1205 - Deadlock Situation Not Detected by SQL Server ? )
Q224453 says that in this case the cause is one of two things. Firstly, that the controlling app has lost track of the transaction after possibly, an unhandled error. I'm using COM+ to manage my transactions, and I have checked with caution the error handling in those components. (See platform summary below)
Secondly, a performance issue - a slow running query holding on to locks a little too long? When I look for slow performing queries using SQL trace, I find that the longest query takes 1140 ms. I'm pretty sure performance is not the issue. This said, I do notice that one of the SPID's within a blocked transaction (not the blocking one) may occasionally timeout (EventClass +Lock:Timeout). This occurs even when I have not set any timeout value on the connection, in the stored procedure or on the COM+ component.
Does anyone have any ideas where I should look next?
I'm curious to see if it's a Q239753 (BUG: 1205 - Deadlock Situation Not Detected by SQL Server) as I know that COM+ is using multiple SPIDs within a transaction boundary.
I have also noticed that occasionally I get an indication that the last wait type on the blocking SPID was a transaction control block (XCB). I dont know if this has implications or not.
Any help or suggestions would be greatly appreciated. Im happy to go and look again at anything.
Many thanks in advance,
James.
Quick Platform summary:
Windows 2000
VB6
Sql Server 7 sp4
COM+ handles transactions. Components call parametrized stored procedures.
mdac2.5sp1
One physical multi-processor server (2GB RAM) with transactions spanning two databases on the server, occasionally a transactable MSMQ takes part in a transaction.Hi, Sometimes a process will hang if the calling application is waiting for a response like a click to a dialog box. Perhaps the COM object has an unhandled error of some sore. If this is the case, the transaction is hanging waiting for an error confirmation from the caller.
Have fun,
LM:p|||Thanks LM.
I capture and log all errors that occur in COM+ so I'm pretty sure that that's not the issue though I'll triple check. I've even tried setting the components to run with unattended execution - meaning that all the errors would be written to a log and would not wait for user input.
The more peole I talk to about this the more I hear
"Gee, COM+ is clunky isn't it"
or
"We just moved all our transactions out of COM+ and into stored procedures".
Has anyone else had problem with transaction implemented in COM+? I've successfully written 2 apps which use COM+ with no problem, but this particular app requires much more server resources than the others...
Perhaps this note belongs on a number of lists?
Thanks all!
Monday, February 20, 2012
My guess is stored procedures
I just started playing with MS SQL and was wondering how i could
possibly do the following for example.
I do a 'SELECT source FROM history' and that gives me a list of
sources. Now i want to run a SELECT for each source i received from
the previous SELECT.
For further clarity, in programming terms, it would be just like
looping through an array.
Thanks Guys,
PatricePlease define what you mean by "run a SELECT for each source". It might help
to post the DDL (CREATE TABLE statements) and some sample data (INSERT
statements) for your table(s). Also, show the end-result you would want to
get from that sample data.
In general in SQL you should try to minimise procedural code such as loops.
SQLServer is optimised for set-based queries. If you want to SELECT a set of
rows for each "source" then you can probably write that query as a JOIN to
one or more tables.
For example (from the Pubs database). To retrieve all the Titles for each
Author:
SELECT A.au_fname+' '+A.au_lname AS author, T.title
FROM Authors AS A
JOIN TitleAuthor AS B
ON A.au_id = B.au_id
JOIN Titles AS T
ON B.title_id = T.title_id
ORDER BY A.au_lname, A.au_fname
--
David Portas
SQL Server MVP
--|||Hi David,
Sorry for the lack of clarity.
My plan is to detect if any users login from more than 3 sources.
I have a history table
CREATE TABLE history(client_id int, when datetime, source varchar(20))
After some inserts a SELECT * FROM history looks like
-------------
|client_id | when | source |
-------------
|1010 | <date> | 202.22.21.33 |
-------------
|1818 | <date> | 192.10.22.31 |
-------------
|1542 | <date> | 222.452.1.36 |
-------------
|1010 | <date> | 192.22.21.33 |
-------------
|1010 | <date> | 242.22.21.33 |
-------------
|1010 | <date> | 256.22.21.33 |
-------------
so from this table i would to a
SELECT DISTINCT client_id from history (if thats the syntax) this would
return
----
|client_id |
----
|1010 |
----
|1818 |
----
|1542 |
----
Now from these values i want do a
SELECT count(DISTINCT source) from history where client_id = 1010
this would return 4. Since client_id 1010 logged in from 4 different
sources.
Then i want to run the same query with client_id = 1818 and so on. I.e
loop through the client ids.
How can i do this in a stored proc. My guess is cursors. Any help?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No need for cursors or loops. This should give the result you want:
SELECT client_id, COUNT(DISTINCT source)
FROM History
GROUP BY client_id
--
David Portas
SQL Server MVP
--|||
Dave, you're a champ!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!