Read Uncommitted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Read Uncommitted

Author

Topic

ablanchet
Starting Member

1 Post

Posted - 2004-10-14 : 15:43:38

Hi,

MS-SQL 2000's Default Transaction Isolation Level is Read Committed. I'd like to change the Default Transaction Isolation Level of my MS-SQL Server to Read Uncommitted; how can I do that? I checked in ALTER DATABASE and sp_configure and I couldn't find a way to do this.

Thanks,

André

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-14 : 17:43:59

Have no clue whatsoever if this is even possible, but you can do it on a per-query-basis using WITH (NOLOCK)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

sify
Starting Member

18 Posts

Posted - 2004-10-14 : 20:51:24

We too are looking for such setting on the server level...
We want to set this option for a Dataware housing project.

Set Transaction Isolation Level Read Uncommitted command can be used for a user / connection session instead of setting for each row..

Is setting the option Set transaction Isolation level read uncommitted best suitable for dataware housing project??

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 21:19:08

To answer the initial question, you can't do it at the SQL Server server or database levels, but rather at the session/connection level:

quote:

Transact-SQL

Transact-SQL scripts and DB-Library applications use the SET TRANSACTION ISOLATION LEVEL statement.

ADO

ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

OLE DB

OLE DB applications call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, or ISOLATIONLEVEL_SERIALIZABLE

ODBC

ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources