Blocks and Locks in SQL

If you have an job interview question of how do handle SQL locks and blocks
whats your answer?"Lontae Jones" wrote:
> If you have an job interview question of how do handle SQL locks and block
s
> whats your answer?
I would talk about my experiences using Profiler, server-side traces, and
the really slick sp_blocker_pss80 lock / block analysis tool. I'd then ask
for them to put more context around the issues; for example: if the locking
condition was readily repeatable, I'd try to catch the lead blocker with
sp_who2, and run the DBCC INPUTBUFFER on the blocked/blocking SPID's to try
to get a handle on the situation causing the blocking. If the situation was
occurring in a distributed app and the sp_blocker_pss80 stored procedure was
reporting nested transactions, I'd start looking into how the app made DB
calls (i.e. - if they are asynchronous and nesting transactions, they may be
causing client app blocking issues that could be resolved by going a serial
route instead).
When I'm asked very broad questions I usually take the opportunity to flip
the situation on them and grill for specifics. The devil is always in the
details.|||Talk about transaction isolation level and NOLOCK Hints as well. I'm sure
that is what they're fishing for.
Greg Jackson
PDX, Oregon|||Look up Understanding and Avoiding Blocking in Books OnLine.
Here are a few suggestions from BOL to avoid blocking:
-Do not use or design an application that allows users to fill in edit boxes
that generate a long-running query. For example, do not use or design an
application that prompts the user for inputs but rather allows certain
fields to be left blank or a wildcard to be entered. This may cause the
application to submit a query with an excessive running time, thereby
causing a blocking problem.
-Do not use or design an application that allows user input within a
transaction.
-Allow for query cancellation.
-Use a query or lock time out to prevent a runaway query and avoid
distributed deadlocks.
-Immediately fetch all result rows to completion.
-Keep transactions as short as possible and in one batch.
-Explicitly control connection management.
-Stress test the application at the full projected concurrent user load.
Other suggestions:
-A query time-out for each query.
-Lock time-out for each query.
-Use a low isolation level.
-Use bound connections.
-Avoid using cursors
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:0E46E9EA-4975-4FC5-BAE3-6C1AE7D87D9C@.microsoft.com...
> If you have an job interview question of how do handle SQL locks and
> blocks
> whats your answer?