• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

SQL Server 2005 - provide NoLOCK on Database level

Just like NOLOCK option applied for a single table to get data without applying any locks on the table, I need to find if there is some option available on the database level which makes all its "select" automatically with NOLOCK.

Also SET TRANSACTION ISOLATION LEVEL will not work here as that has to be done on all the SPs. I need it on database level.

Thanks
Vipin
0
vipin_nagarro
Asked:
vipin_nagarro
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this does not exist on database/server/login level.

the only thing that exists is that if the database is marked read-only, there won't be any locks at all on the entire database...

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
The only thing that can be done at database level is

ALTER DATABASE ur_dbname SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ur_dbname SET READ_COMMITTED_SNAPSHOT  ON;
0
 
vipin_nagarroAuthor Commented:
@angel: Thanks for reply
Is there any way that database is readonly for some login and coulb be insert able for others. I mean can I made that distinguish in application level by connecting with one user for read only mode and with other in normal mode.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I mean can I made that distinguish in application level by connecting with one user for read only mode and with other in normal mode.

Then you need to execute the SET TRANSACTION ISOLATION LEVEL statement at the beginning of the session for those users alone..
0
 
vipin_nagarroAuthor Commented:
@rrjegan17: How could this be best implemented in existing applications. I have around 10 applications accessing a particular db and one application is inserting the realtime data.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Do you have any user levels or user based permissions available in your application. If so, then you would be passing user permissions or privileges across pages in a session and make use of that. Just use that info for this validation..
0
 
vipin_nagarroAuthor Commented:
Thanks, u helped me out
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
welcome..
And kindly let me know the reason for a C grade for this question..
More on grading here:

http://www.experts-exchange.com/help.jsp#hs=29&hi=403

"What's the right grade to give?

Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.

Giving a higher grade has no impact on your Available Points."
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now