Solved

Row level locking in SQL Server 2005

Posted on 2006-11-16
9
1,085 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 143

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 143

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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
 
LVL 143

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 143

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 143

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 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