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?

Blocking process

I just found an interesting thing in a database.
I have a lot of locks and all locked processes are locked by SPID -2. The
information I get in Enterprise manager and Query analyzer is that the
proceses are blocked by -2.
I can't find any information of what SPID -2 is. The only thing I see is
when I run sp_lock, then I get a list with over 3000 rows of locks which are
held by -2.
Does anybody know what a negatvie SPID is and what I can do to resolve this?If you run a sp_who '-2'
What does this bring back?
Dylan kruger
"Jaana" <jaana.lehtonen@.banverket.se> wrote in message
news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> I just found an interesting thing in a database.
> I have a lot of locks and all locked processes are locked by SPID -2. The
> information I get in Enterprise manager and Query analyzer is that the
> proceses are blocked by -2.
> I can't find any information of what SPID -2 is. The only thing I see is
> when I run sp_lock, then I get a list with over 3000 rows of locks which
are
> held by -2.
> Does anybody know what a negatvie SPID is and what I can do to resolve
this?
>|||sp_who -2 gives the result The login '-2' does not exist.
"Dylan Kruger[MS]" <carmellobear1@.hotmail.com> skrev i meddelandet
news:OhcrsG8kDHA.2500@.TK2MSFTNGP10.phx.gbl...
> If you run a sp_who '-2'
> What does this bring back?
> Dylan kruger
> "Jaana" <jaana.lehtonen@.banverket.se> wrote in message
> news:ux8FEk7kDHA.2528@.TK2MSFTNGP10.phx.gbl...
> > I just found an interesting thing in a database.
> > I have a lot of locks and all locked processes are locked by SPID -2.
The
> > information I get in Enterprise manager and Query analyzer is that the
> > proceses are blocked by -2.
> > I can't find any information of what SPID -2 is. The only thing I see is
> > when I run sp_lock, then I get a list with over 3000 rows of locks which
> are
> > held by -2.
> > Does anybody know what a negatvie SPID is and what I can do to resolve
> this?
> >
> >
>

Blocking

hi,

I am using SQL SERVER 2000.

Problem that i am facing is when ever I check locks in Enterprise
Manager I find
following blocking - :
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1

We dont have any control over application(source code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks
Richard wrote:
> hi,
> I am using SQL SERVER 2000.
> Problem that i am facing is when ever I check locks in Enterprise
> Manager I find
> following blocking - :
> 1) sp_cursoropen;1
> 2) sp_cursorclose;1
> 3) sp_cursorfetch;1
> We dont have any control over application(source code).Whatever
> we have to do, has to done from database side.
> can anyone guide me to solve this problem.
> Thanks

Richard,

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

If you just find you have got some generic slowness regarding the
applications, go set up some profiles focusing on long running
transactions.

Generally, locks are always needed in a database system when concurrent
users are assessing (in different ways) the same resource. MSSQL
handles locks internally, and my view is not to mess with them. Of
course you can use there are certain guidelines for the DBAs and
developers to utilise locks more properly as scarce resource.

Oracle guys might laugh over us on this one since in Oracle locks are
not scarce resource.

Hope this can help.

Cheers,

Yak|||hi yak,

Thank u for the reply
> I am not sure about what you want to achieve by checking lock
> information in EM. Locks dynamically occur all the time and not
> necessarily bad things. Any database system won't work without some
> kind of lock mechanism, the key is how it is utilised.
> If you suspect some dead locks are happening, go check out the process
> info. You might be able to find out which process is blocking others.

I will be more specific ,when i checked my EM for blocking .I found out
their were 3 SPID with blocking remark,when i checked its property i
found the following
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1
and this blocking is effecting my users who are connected to my server.
I googled a lot for the solution but their was no result.I want to
solve this problem for ever(if possible).

:)
Richard

Yak wrote:
> Richard wrote:
> > hi,
> > I am using SQL SERVER 2000.
> > Problem that i am facing is when ever I check locks in Enterprise
> > Manager I find
> > following blocking - :
> > 1) sp_cursoropen;1
> > 2) sp_cursorclose;1
> > 3) sp_cursorfetch;1
> > We dont have any control over application(source code).Whatever
> > we have to do, has to done from database side.
> > can anyone guide me to solve this problem.
> > Thanks
>
> Richard,
> I am not sure about what you want to achieve by checking lock
> information in EM. Locks dynamically occur all the time and not
> necessarily bad things. Any database system won't work without some
> kind of lock mechanism, the key is how it is utilised.
> If you suspect some dead locks are happening, go check out the process
> info. You might be able to find out which process is blocking others.
> If you just find you have got some generic slowness regarding the
> applications, go set up some profiles focusing on long running
> transactions.
> Generally, locks are always needed in a database system when concurrent
> users are assessing (in different ways) the same resource. MSSQL
> handles locks internally, and my view is not to mess with them. Of
> course you can use there are certain guidelines for the DBAs and
> developers to utilise locks more properly as scarce resource.
> Oracle guys might laugh over us on this one since in Oracle locks are
> not scarce resource.
> Hope this can help.
> Cheers,
> Yak|||
Richard wrote:
> hi yak,
> Thank u for the reply
> > I am not sure about what you want to achieve by checking lock
> > information in EM. Locks dynamically occur all the time and not
> > necessarily bad things. Any database system won't work without some
> > kind of lock mechanism, the key is how it is utilised.
> > If you suspect some dead locks are happening, go check out the process
> > info. You might be able to find out which process is blocking others.
> I will be more specific ,when i checked my EM for blocking .I found out
> their were 3 SPID with blocking remark,when i checked its property i
> found the following
> 1) sp_cursoropen;1
> 2) sp_cursorclose;1
> 3) sp_cursorfetch;1
> and this blocking is effecting my users who are connected to my server.
> I googled a lot for the solution but their was no result.I want to
> solve this problem for ever(if possible).
> :)
> Richard
Richard,

If you just want to kill the original culprit process, then go "process
info" to find where the locks came from. You should be able to figure
it out using "blocking" and "blockedby". Once you find it you can kill
it from EM.

To solve the problem "forever", I reckon you need to find out the
reason for these locks. Usually locks will be released after the
resource contention is resolved over times unlesss you have deadlocks
there. (if you do, you can also find out using the solution as above)

The easiest way is to monitor the application using profiles focusing
on long running transactions (there must be some there if you always
have locks not released for long time). You can find out the script the
application uses which cause serious resource contention.

Then talk to the programmer.

At the moment, all you can do is to find out the problematic process
and kill it.

Cheers,

Yak
> Yak wrote:
> > Richard wrote:
> > > hi,
> > > > I am using SQL SERVER 2000.
> > > > Problem that i am facing is when ever I check locks in Enterprise
> > > Manager I find
> > > following blocking - :
> > > 1) sp_cursoropen;1
> > > 2) sp_cursorclose;1
> > > 3) sp_cursorfetch;1
> > > > We dont have any control over application(source code).Whatever
> > > we have to do, has to done from database side.
> > > > can anyone guide me to solve this problem.
> > > > Thanks
> > Richard,
> > I am not sure about what you want to achieve by checking lock
> > information in EM. Locks dynamically occur all the time and not
> > necessarily bad things. Any database system won't work without some
> > kind of lock mechanism, the key is how it is utilised.
> > If you suspect some dead locks are happening, go check out the process
> > info. You might be able to find out which process is blocking others.
> > If you just find you have got some generic slowness regarding the
> > applications, go set up some profiles focusing on long running
> > transactions.
> > Generally, locks are always needed in a database system when concurrent
> > users are assessing (in different ways) the same resource. MSSQL
> > handles locks internally, and my view is not to mess with them. Of
> > course you can use there are certain guidelines for the DBAs and
> > developers to utilise locks more properly as scarce resource.
> > Oracle guys might laugh over us on this one since in Oracle locks are
> > not scarce resource.
> > Hope this can help.
> > Cheers,
> > Yak|||hi yak,

thank you.

:)Richard

Yak wrote:
> Richard wrote:
> > hi yak,
> > Thank u for the reply
> > > I am not sure about what you want to achieve by checking lock
> > > information in EM. Locks dynamically occur all the time and not
> > > necessarily bad things. Any database system won't work without some
> > > kind of lock mechanism, the key is how it is utilised.
> > > > If you suspect some dead locks are happening, go check out the process
> > > info. You might be able to find out which process is blocking others.
> > > I will be more specific ,when i checked my EM for blocking .I found out
> > their were 3 SPID with blocking remark,when i checked its property i
> > found the following
> > 1) sp_cursoropen;1
> > 2) sp_cursorclose;1
> > 3) sp_cursorfetch;1
> > and this blocking is effecting my users who are connected to my server.
> > I googled a lot for the solution but their was no result.I want to
> > solve this problem for ever(if possible).
> > :)
> > Richard
> Richard,
> If you just want to kill the original culprit process, then go "process
> info" to find where the locks came from. You should be able to figure
> it out using "blocking" and "blockedby". Once you find it you can kill
> it from EM.
> To solve the problem "forever", I reckon you need to find out the
> reason for these locks. Usually locks will be released after the
> resource contention is resolved over times unlesss you have deadlocks
> there. (if you do, you can also find out using the solution as above)
> The easiest way is to monitor the application using profiles focusing
> on long running transactions (there must be some there if you always
> have locks not released for long time). You can find out the script the
> application uses which cause serious resource contention.
> Then talk to the programmer.
> At the moment, all you can do is to find out the problematic process
> and kill it.
> Cheers,
> Yak
> > Yak wrote:
> > > Richard wrote:
> > > > hi,
> > > > > > I am using SQL SERVER 2000.
> > > > > > Problem that i am facing is when ever I check locks in Enterprise
> > > > Manager I find
> > > > following blocking - :
> > > > 1) sp_cursoropen;1
> > > > 2) sp_cursorclose;1
> > > > 3) sp_cursorfetch;1
> > > > > > We dont have any control over application(source code).Whatever
> > > > we have to do, has to done from database side.
> > > > > > can anyone guide me to solve this problem.
> > > > > > Thanks
> > > > > Richard,
> > > > I am not sure about what you want to achieve by checking lock
> > > information in EM. Locks dynamically occur all the time and not
> > > necessarily bad things. Any database system won't work without some
> > > kind of lock mechanism, the key is how it is utilised.
> > > > If you suspect some dead locks are happening, go check out the process
> > > info. You might be able to find out which process is blocking others.
> > > > If you just find you have got some generic slowness regarding the
> > > applications, go set up some profiles focusing on long running
> > > transactions.
> > > > Generally, locks are always needed in a database system when concurrent
> > > users are assessing (in different ways) the same resource. MSSQL
> > > handles locks internally, and my view is not to mess with them. Of
> > > course you can use there are certain guidelines for the DBAs and
> > > developers to utilise locks more properly as scarce resource.
> > > > Oracle guys might laugh over us on this one since in Oracle locks are
> > > not scarce resource.
> > > > Hope this can help.
> > > > Cheers,
> > > > Yak

Blocked transaction problem

Hello,

I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.

SERVER2 is a linked server.

BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN

I have same configuration in other 2 computers and it works ok.

What is the problem?

Thank you!!

This should not lock your transaction. But depending on the server you are using it can be that all data will retrieved from the linked server first to do a filter on the calling server. That will take more time than just doing the Select on the remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

there are only 3 rows on remote server.

Both servers are SQL Server 2000.

|||What about if you run the thing without any transaction ? Is it the same problem ? Do you see any locks on the table during the stale time ?

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello,

if I run it without any transaction it finishes very fast.

But I want to know the reason of the problem, because I have one place that runs it this way, and I can not install a new one with same queries.

Thank you.

|||See which locks are allocated when using the command with transactions to see where the blocking is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You are essentially doing a distributed transaction because of the use of DML with linked server table. So the delay might be attributed to network issues. Check the MSDTC configuration including network configuration to make sure everything is fine. You can also see the transaction statistics in the MSDTC console (Component Services applet) to see the average response time. If that is close to your execution time then the problem is external to SQL Server and you should ask in the Windows newsgroups or search MSKB for optimizing MSDTC setup.

If the problem is not related to MSDTC, then you can determine the wait types in SQL and troubleshoot the issue. Run following:

1. DBCC SQLPERF(WAITSTATS, 'CLEAR') WITH NO_INFOMSGS

2. your current batch

3. DBCC SQLPERF(WAITSTATS) WITH NO_INFOMSGS

Now, based on the wait type you can figure out what is the cause of the delay. Search MSKB articles for more details on using DBCC SQLPERF.

|||

Hello,

I can not see anything because the execution of the query never ends.
I leave up to 30 minutes and it does not end.

I can execute the query without a transaction and it finishes in a few miliseconds. So, it is posible to query other server.

Could it be a problem with server names?
I mean, the name registered at SQL Server Enterprise Manager, the one at Linked Server and aliases (SQL Server Client Network Utility).

Currently all of them are the same, but at SQL Server installation time the server name was a diferent one.

Thanks!!

|||

Hi!

I have the same problem!!

A simple select againts a second server with a TRAN

BEGIN TRAN
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2
COMMIT TRAN

execution of the query never ends

Without TRAN it finishes in a few miliseconds

someone have any ideas to resolve this problem?

thanks in advance.