The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.
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?
Asked:
2
2 Solutions
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.
>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
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
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.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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