473,405 Members | 2,262 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?
Regards

Bjørn

Jul 6 '07 #1
1 1307
(bj***************@gmail.com) writes:
I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?
I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 6 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: jkelty | last post by:
Hello, I just have a question about the table locking in 3.23.56. If I am inserting a row into a table that is MyISAM type, is the entire table locked? And, if so, what does this mean for...
9
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to...
16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
6
by: Evan David Light | last post by:
After agonizing over this problem for a few days, I've decided to seek help. No, not the variety that involes a jacket that zips up the back but this august body of intrepid individuals. I've...
4
by: Prabhat | last post by:
How do I lock a particular record that one user has opened for editing? If I use the pessimistic type, can other users view the record (but not edit it) and return a message telling that another...
8
by: Allan Ebdrup | last post by:
I've implemented transactions in my dotNet 2.0 project using System.Transactions and TransactionScope. --- TransactionOptions options = new TransactionOptions(); options.IsolationLevel =...
3
by: paulus4605 | last post by:
dears since I'm new to ruby I try to create a form within ruby on rails. this is my controller require "player" class SpelersController < ApplicationController def index @speler =...
5
by: jbenner | last post by:
I have opened a PMR for this with IBM, and am not asking for advice from the DB2 DBA community. I am posting this as an FYI that DB2 Health Monitor, even at the latest version of DB2, still can cause...
3
by: Brad Pears | last post by:
I have a vb.net 2005 app that asks for a job number. After the job number has been entered, I run an SQL 2000 stored procedure that selects the appropriate row from the Jobs table. I do not want to...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.