Solved

MS SQL Server, multiple threads reading the same table

Posted on 2010-11-12
5
653 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]
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 142

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Delete Query 9 33
Problem with SQL Script - Cannot call methods on char 2 22
Help Required 2 33
Separate 2 comma delimited columns into separate rows 2 32
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…

803 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