473,737 Members | 1,913 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Force Row Level locking in SQLServer 2000 ?

Hi

Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.

Best regards

Aarno
Jul 20 '05 #1
1 5451
Arska (aa*********@bo f.fi) writes:
Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.


You can add a locking hint

SELECT * FROM tbl (ROWLOCK) WHERE col = 32

However, SQL Server may disregard that hint if row locks are possible
to achieve.

You may need to review you indexing strategy. For instance, in the example
above, I would not expect the hint to help if there is no index on col.
SQL Server will have to scan the entire table, so a tablock is called for.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

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

Similar topics

5
10400
by: Steve | last post by:
Hi; I went to the microsoft site to try to find a guide to the error messages that the jdbc drivers give ( for sqlserver 2000 ). I had no luck. Does anyone know if there is such a guide? I got this mysterious error message below in my logs ( nothing visible
12
9573
by: Puvendran | last post by:
Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category Value TRADE_NO 1456 JOB_NO 267 .....
16
8928
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
0
1276
by: Deano | last post by:
I use the Access 2000 MSI Wizard from Sagekey and they don't know if the bug documented on page 32 of the Access 2000 Developer`s Handbook Volume 2: Enterprise Edition still affects the runtime. Apparently page locking is invoked when using the Access run-time record than record-level locking. This also affects Access 2000 but if you patch Access the bug is fixed. I really don't know about the runtime though. Does anyone know if...
2
2218
by: Deano | last post by:
I use the Access 2000 MSI Wizard from Sagekey and they don't know if the bug documented on page 32 of the Access 2000 Developer`s Handbook Volume 2: Enterprise Edition still affects the runtime. Apparently page locking is invoked when using the Access run-time record than record-level locking. This also affects Access 2000 but if you patch Access the bug is fixed. I really don't know about the runtime though. Does anyone know if...
2
8013
by: Larry Bertolini | last post by:
Is there a way to read data from a linked server, within a transaction, without using DTC? The data on the linked server is static, therefore there is no need for two-phase commit. There is no need for locking data on the linked server, because it is not being updated (either from the remote server, or from the local server). I don't want to run DTC because:
2
1688
by: =?Utf-8?B?SmVmZnJleQ==?= | last post by:
I have some old ASP programs w/ SQLserver 2000 databases. Now I am developing ASP.NET projects using VB 2005 and SQLserver 2005. What are the best procedures to develop and test the ASP.NET programs? My main concerns are: 1. What do I need to test? VB 2005/ASP.NET with 2000 data? Migration of 2000 data to SQLserver 2005? VB 2005/ASP.NET with 2005 data? 2. How to minimize the transition time? What is the critical change for the...
1
2226
by: Paul H | last post by:
I have an Employees table with the following fields: EmployeeID SupervisorID Fred Bob Bob John Bob Mary Bill Bill I have created a self join in the relationships window, with
9
2144
by: Yashgt | last post by:
I am facing a wierd problem with SQL Server standard edition SP2. I have a statement like SELECT A,B,C, ...., dbo.TEST_GETATTENDEEDETAILS(CI.SI_ID) AS SI_ATTENDEENAME, X,Y,Z from ..... The function emits a CSV string. When I run the above command, I get an error that says:
0
8966
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9331
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9204
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8203
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6749
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6052
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4567
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3278
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.