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
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
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;
ALTER DATABASE ur_dbname SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ur_dbname SET READ_COMMITTED_SNAPSHOT ON;
ASKER
@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.
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.
>> 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..
Then you need to execute the SET TRANSACTION ISOLATION LEVEL statement at the beginning of the session for those users alone..
ASKER
@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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, u helped me out
welcome..
And kindly let me know the reason for a C grade for this question..
More on grading here:
https://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."
And kindly let me know the reason for a C grade for this question..
More on grading here:
https://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."
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...