Can sleeping connections hold locks

To clarify a little more - I am looking at the results of sp_who2 and it is
the connections that are coming from query analyzer I am curious about.
The user in this case had executed and completed their query and had not
specified then transaction isolation level, yet there was an update from
another connection that would just not complete - when I killed the
connection of the user running the query the update completed. It could
just be a coincidence and the sleeping connection may have had nothing to do
with it but it just got me wondering if it is normal SQL behavior to hold a
lock if a query (where the isolation level is not specified) has completed
running.
thanks
Meenal
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>
|||Better yet, that was too explicit. What I typically find is that the client
API makes calls that the end-user is unaware of that can block resrouces.
Most notibably the use of COM objects on web servers.
By default COM uses the SERIALIZABLE transaction isolation level. Then, if
they use IMPLICIT_TRANSACTIONS, you can be in a world of hurt. Add to that,
connection pooling, and you can bring a web site to its knees in just
minutes of light activity. All the while, the DBMS sit peacefully silent
AWAITING COMMAND.
Try this simulation, which occurs, implicitly in many scenarios.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
SELECT *
FROM SomeBigTable
GO
--Leave the connection open.
In another connection run this:
SELECT *
FROM master.dbo.sysprocesses WITH(NOLOCK)
SELECT *
FROM master.dbo.syslockinfo WITH(NOLOCK)
You could narrow down both of these queries by specifying the particular
spid you ran the first under; regardless, you sould see that that SELECT
statement is holding all kinds of locks. And, if this is a busy system,
will be blocking quite a few other users.
I would recommend doing this on test.
Sincerely,
Anthony Thomas
"Barry McAuslin" <barry@.nospam.com> wrote in message
news:%236Q%233DAOFHA.2252@.TK2MSFTNGP15.phx.gbl...
Meenal
Yes they can
If you try the following
BEGIN TRAN
UPDATE TABLE
SET x = y
-- COMMIT TRAN
Leaving out the commit tran will leave locks on a sleeping connection.
Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
www.sqlfe.com
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eDCulE$NFHA.2144@.TK2MSFTNGP09.phx.gbl...
> To clarify a little more - I am looking at the results of sp_who2 and it
> is
> the connections that are coming from query analyzer I am curious about.
> The user in this case had executed and completed their query and had not
> specified then transaction isolation level, yet there was an update from
> another connection that would just not complete - when I killed the
> connection of the user running the query the update completed. It could
> just be a coincidence and the sleeping connection may have had nothing to
> do
> with it but it just got me wondering if it is normal SQL behavior to hold
> a
> lock if a query (where the isolation level is not specified) has
> completed
> running.
> thanks
> Meenal
>