Solved

Row level locking in SQL Server 2005

Posted on 2006-11-16
9
1,049 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 information from SQL Server on Database, Connection and Server properties

758 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

19 Experts available now in Live!

Get 1:1 Help Now