[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS SQL Server, multiple threads reading the same table

Posted on 2010-11-12
5
Medium Priority
?
681 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
[X]
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
  • 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 800 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 1200 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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