Solved

Row level locking in SQL Server 2005

Posted on 2006-11-16
9
1,062 Views
Last Modified: 2008-01-09
Hi Expert,
How can I configure a table such that it allows only row level locking?
regards
0
Comment
Question by:novknow
  • 5
  • 4
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17955015
you cannot configure that.
you can only put proper indexes for the queries so for single-row queries only single row-locks will be done.
0
 

Author Comment

by:novknow
ID: 17955093
Hi angelIII,
Would appreciate if you can explain more. I have increased the number of points.
regards
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17955456
depending on the number of rows your query will have to "lock", sql server will decide if it locks the rows individually, or, if there are many rows to be locked, to put a lock on the page or even on the entire table.
hence, if you are scanning the entire table (due to "missing" index for the query), sql server will rather put a lock on the table than putting a lock on each single row.
this process is called lock escalation, and is to ensure best performance for the locking process.

can you explain why you want only row level locking?
0
 

Author Comment

by:novknow
ID: 17955599
Hi angelIII,

Thanks for your reply.

I have encountered strange behaviour in my enviroment and I have post a question Q_22061094 in SQL channel and Q_22061103 in C# channel. Till now no solution.

I occasionally hit these error messages:
1) OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
2) Timeout expired.
It happened when we need to re-generate large amount of data. Problem is it occurs randomly - no pattern.
It does happen on normal day but less frequent.

I suspect it could be due to locking issues and would like to use row level locking to reduce or eliminate this error.

I also intend to post another question to ask if there are such setting to increase timeout duration of a update/insert
query because besides the above error message, I also receive "Timeout expired" message.

So, from your previous post, it looks like row level locking is not going to help me.

regards
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17955623
errors like "SQL Server does not exist or access denied."  mean that the connectivity is not properly working.
this could be a number of issues, but for sure not row level locking.

>It happened when we need to re-generate large amount of data.
can you explain that a bit closer?
0
 

Author Comment

by:novknow
ID: 17955832
This piece of application will compute and generate indices data from a few base tables, example table A,B,C.
It will then update or insert these indices to about 40 to 50 tables.
Table A,B,C is updated by many clients constantly.

When user request to update certain rules or formulas, I have to clear all the data in those 50 tables.
Then run the application to regenerate the data from table A,B,C. The amount is fairly large.
Simple application?

This application is a C# 2.0 windows form and connect to SQL server using OleDbConnection.

The "irritating" part is each time the error happens, I have to acknowledge the error before the application can continue to crunch the data, thus not able to complete the task in time. I am quite confuse at this moment,
so any kind of help is appreciated. Thanks.

regards
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17956080
>I have to clear all the data in those 50 tables.
do you use TRUNCATE TABLE to clear the data?

>Then run the application to regenerate the data from table A,B,C.
how does it do that?
0
 

Author Comment

by:novknow
ID: 17957354
1) I first use delete from <tablename>, then clear the transaction log.
What difference does this make?

2) It reads all the records that falls within a certain criteria and group them up, go through some computation, and then update them into the other 50 tables.

regards
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17975140
1) I first use delete from <tablename>, then clear the transaction log.
What difference does this make?

the TRUNCATE (which deletes all rows), will only log the statement itself in the transaction log. the delete statement will store all the deleted data in the transaction log.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Join 4 34
Sql query 107 29
Getting same value for every field in SQL 2 31
Microsoft SQL Server 2016 Setup..error 7 32
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now