Prevent SQL from locking at the table level

Question

Post reply

Prevent SQL from locking at the table level

junk.mail291276

SSCrazy

Points: 2503
More actions
March 7, 2018 at 4:45 pm

#336447

Hello,
Is there any way to limit SQL to locking only at the row level and never at the table level?
I have an application called RAWA that is parsing a file and writing the data from that file to a table in the database. Meanwhile, an API tries to read data from that same table. The two, however, are completely different sets of data, so there should be no need to block at the table level, only the row level.
Is there a way to force SQL NOT to block at the table level?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

No.

You need to figure out how to improve your application so that you aren't looking for a kludge on the SQL Server side.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Look at the queries, make sure they're efficient. Look at the indexes, make sure that they support the queries
If SQL's locking at the table level then either it can't lock at the other levels (row/page locks disabled), or it's reading the entire table because the query/indexes are inefficient and as a result it locks the entire table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

See if this helps

https://support.microsoft.com/en-us/kb/323630

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Pretty sure this question is directly related to this question.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 5

Pretty sure of that also - and some questions from that were not answered - and eventually the solution is easy but not what the OP wishes to be given.