Locks and Blocks

Hi all,
I don't understand this.
I had SPID 104 blocked by SPID 87. However, SPID 87 was sleeping. How
can that be?
Please advise.
Audrey
*** Sent via Developersdex http://www.codecomments.com ***A sleeping spid is simply one that isn't currently "doing" anything. It can
certainly be sitting there with a lock on a row/table from an update
statement. A bad example is beginning a transaction, doing an update and
then presenting a message box to a user and wait for them to click 'OK'.
What happens if they wander off for lunch? The resource is locked until
they return or a timeout occurs on the connection.
TheSQLGuru
President
Indicium Resources, Inc.
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:uUfKwJNjHHA.3472@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I don't understand this.
> I had SPID 104 blocked by SPID 87. However, SPID 87 was sleeping. How
> can that be?
> Please advise.
> Audrey
> *** Sent via Developersdex http://www.codecomments.com ***|||Fair statement.
Here's a situation:
I have a stored procedure that:
BEGIN TRAN
1) INSERT INTO
2) UPDATE
3) UPDATE
4) INSERT INTO
5) UPDATE
COMMIT TRAN
So, let's say there are SPID 1 and SPID 2 to run the procedure. Would I
be correct to say that SPID 1 needs to complete the transaction before
SPID 2 runs the procedure?
If so, what if SPID1 never makes it to the COMMIT TRAN?
More so, what are some reasons for SPID1 NEVER to make it to COMMIT
TRAN?...hence, causing a block.
*** Sent via Developersdex http://www.codecomments.com ***|||"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:%231MkKjOjHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Fair statement.
> Here's a situation:
> I have a stored procedure that:
> BEGIN TRAN
> 1) INSERT INTO
> 2) UPDATE
> 3) UPDATE
> 4) INSERT INTO
> 5) UPDATE
> COMMIT TRAN
> So, let's say there are SPID 1 and SPID 2 to run the procedure. Would I
> be correct to say that SPID 1 needs to complete the transaction before
> SPID 2 runs the procedure?
Not necessarily.
If the tables are properly indexed and SQL Server can determine that the the
rows affected by SPID 1 won't interact with SPID 2, they should run just
fine side-by-side.
If they need to update the same rows (or SQL Server can't determine for sure
which rows are involved) then yes, SPID 1 can block SPID 2.

> If so, what if SPID1 never makes it to the COMMIT TRAN?
SPID 2 can be blocked.

> More so, what are some reasons for SPID1 NEVER to make it to COMMIT
> TRAN?...hence, causing a block.
Generally, errors or the like. Or something else blocking the available
resources.

> *** Sent via Developersdex http://www.codecomments.com ***
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Also, with proper error handling, the sproc should ALWAYS either get to the
commit or do a rollback on error.
blocking (and even deadlocks) can occur given your example, especially if
the tables involved are used multiple times in the 5 steps listed.
TheSQLGuru
President
Indicium Resources, Inc.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u%23sOh1PjHHA.2552@.TK2MSFTNGP06.phx.gbl...
> "Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
> news:%231MkKjOjHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Not necessarily.
> If the tables are properly indexed and SQL Server can determine that the
> the rows affected by SPID 1 won't interact with SPID 2, they should run
> just fine side-by-side.
> If they need to update the same rows (or SQL Server can't determine for
> sure which rows are involved) then yes, SPID 1 can block SPID 2.
>
> SPID 2 can be blocked.
>
> Generally, errors or the like. Or something else blocking the available
> resources.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>