MS SQL Server, multiple threads reading the same table
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?
Will this work? Any special magic required?
Who is Participating?
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
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
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.
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month8 days, 23 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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