Solved

MS SQL Server, multiple threads reading the same table

Posted on 2010-11-12
5
649 Views
Last Modified: 2012-05-10
I want to have two threads reading from the same table with different queries. I use ADO with C++.

Will this work? Any special magic required?
0
Comment
Question by:Z-Nerd
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
no magic required, this shall work.

of course, if you try to read (or update) the same records, you will get some "locks" (so your code will get into wait), but for the rest, nothing
0
 

Author Comment

by:Z-Nerd
Comment Utility
Why would two reads on the same record cause a lock? I understand if updating.
0
 
LVL 1

Assisted Solution

by:bclevlnd
bclevlnd earned 200 total points
Comment Utility
You should use hints in your queries if you want sql to not lock records when selecting data.  That is the default behavior of sql.
Use:

select yourselection from yourtable with(nolock)

if you want no data locked.  You will, however, also be returned any dirty records.  You can use

with(rowlock)

to only lock selected rows, or

with(readpast)

to not return any dirty rows.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
>Why would two reads on the same record cause a lock?

it depends on which transactional level the 2 sessions are.
if the 2 are in serializable mode, everything they touch in common will make the other one to wait on that same resource... because further down the transaction, a update might occur etc...

for single queries, there is no issue, though, as you say, they just will issue a read shared lock, and can both continue without actually blocking each other
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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