471,571 Members | 1,076 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,571 software developers and data experts.

How does one set read-uncommitted on the entire DB?

Rather than setting by session I would like to configure the DB as read
uncommitted.

Thanx Advance.
Mar 1 '06 #1
5 8622
Robert (ro***********@boeing.com) writes:
Rather than setting by session I would like to configure the DB as read
uncommitted.


You can set the database read-only to eliminate locking entirely.

But else you can't do it, and that is probably a good thing. Dirty reads
is nothing you should engage in as a matter of routine. Maybe you should
review indexing in your database instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 1 '06 #2
Thanx Erland, unfortunately read-only is not a viable option. We have be
reviewing the database and are putting in indexes and hints on the queries
to determine if the locking will become less of a problem. The users are
engaged in some practices that have been going on for awhile and there
application keeps timing out when a lock is placed on a table for more that
30 seconds. The biggest problem we have seen are the locks created by the
ad hoc queries from Access and Excel. Until we can convince the dbo to
setup an olap database or stop using Access I think the problems will
continue.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Robert (ro***********@boeing.com) writes:
Rather than setting by session I would like to configure the DB as read
uncommitted.


You can set the database read-only to eliminate locking entirely.

But else you can't do it, and that is probably a good thing. Dirty reads
is nothing you should engage in as a matter of routine. Maybe you should
review indexing in your database instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Mar 2 '06 #3
Robert (ro***********@boeing.com) writes:
Thanx Erland, unfortunately read-only is not a viable option. We have
be reviewing the database and are putting in indexes and hints on the
queries to determine if the locking will become less of a problem. The
users are engaged in some practices that have been going on for awhile
and there application keeps timing out when a lock is placed on a table
for more that 30 seconds. The biggest problem we have seen are the
locks created by the ad hoc queries from Access and Excel. Until we can
convince the dbo to setup an olap database or stop using Access I think
the problems will continue.


Judging from your description, I don't think having a universal dirty read
would address your problem, even if it existed. The default timeout in
many client API (which is a really stupid idea, if you ask me) is not
related to locking, but the client API getting bored if does not see a
result set within 30 seconds. If this due to a complex query plan with,
NOLOCK is not going to help you. (But it may of course prevent writers
from being blocked.)

All client APIs permit you to set the timeout, but there may be a
problem if the queries are submitted without any real programming
code. You cannot set the timeout on the connect string. In ADO,
which is what you use from Access and Excel I guess, you set the
command timeout on the Connection or Command objects.

Note that query timeout is unrelated to SQL Server. All SQL Server sees
is a request to cancel the query batch.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 2 '06 #4
SQL 2005 - Row level versioning works very nice for this purpose.

Mar 6 '06 #5
pb648174 (go****@webpaul.net) writes:
SQL 2005 - Row level versioning works very nice for this purpose.


Yes, snapshot isolation can be a very good way to resolve locking issues.
And note that there is a database switch: ALTER DATABASE db SET
READ_COMMITTED_SNAPSHOT ON. This changes the default isolation level of
READ COMMITTED to use the snapshot instead.

However, I got the feeling that Robert's problem rather was long-running
queries, and not blocking.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 7 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Håkan Persson | last post: by
3 posts views Thread by AC Slater | last post: by
4 posts views Thread by Ollie Cook | last post: by
5 posts views Thread by Arvind P Rangan | last post: by
7 posts views Thread by Dave Coate | last post: by
3 posts views Thread by Dave Coate | last post: by
5 posts views Thread by Piotr | last post: by
5 posts views Thread by barbara_dave | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.