Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1103
  • Last Modified:

Row level locking in SQL Server 2005

Hi Expert,
How can I configure a table such that it allows only row level locking?
regards
0
novknow
Asked:
novknow
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
novknowAuthor Commented:
Hi angelIII,
Would appreciate if you can explain more. I have increased the number of points.
regards
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
novknowAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
novknowAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
novknowAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now