Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS SQL Server, multiple threads reading the same table

Posted on 2010-11-12
5
657 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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34123242
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
ID: 34123288
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
ID: 34123724
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 34126566
>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

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.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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