Row level locking in SQL Server 2005

novknow
novknow used Ask the Experts™
on
Hi Expert,
How can I configure a table such that it allows only row level locking?
regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Author

Commented:
Hi angelIII,
Would appreciate if you can explain more. I have increased the number of points.
regards
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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?

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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?

Author

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
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial