One final question – about locking | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

One final question – about locking

I’m just wrapping up my fist piece of TSQL-related work, and this forum has saved me a great deal of time – thankyou to all. I have one final question: I want to get several pieces of information about a table, all of the form SELECT count(*) FROM table WHERE <condition>. I could run each query one after the other – but if someone else updates that table at the same time then some queries will be reading the old version and some reading the new – that’s no good.
So:
How do I lock this table for the duration of these queries being performed?

Check Locking Hints in BOL. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.
Read about isolation levels in SQL Server. By default it is Read Committed. If it is not enough you can have Serailizable Isolation Level. Read about this in BOL and below mentioned links http://msdn2.microsoft.com/en-US/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/aa213034(sql.80).aspx Madhu
Though you really need to consider the implications on concurrency if you decide to lock others out while your counts are running. How long does it take? How long will it take in a production environment? How long will it take in a year? (you want to consider the growth rate in the system). The last thing you want is to implement something that the users will scream at if they happen to be bitten by it. /Kenneth
How frequently you need the data for row count of these tables? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>