February 17, 2006 at 4:26 am
Hi - I have a couple of questions that I hope one or more of you might be able and willing to help me with:
I have a database with data that doesn't need transactional accuracy and I use "NOLOCK" hints on most of my select statements - does that effectively disable locking as much as possible?
- is there somehow I can set the database to "READ UNCOMMITED" mode globally? (with SQL server configuration / SQL server 2000) - or would I have to issue a "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" statement on each database connection before executing SQL commands to accomplish this?
Kind regards,
Dennis
February 17, 2006 at 7:49 am
Look up transaction isolation levels in books online.
You can set your transaction isolation level to read uncommited as you see necessary.
It is a connection level property
From BOL:
Controls the default transaction locking behavior for all Microsoft® SQL Server™ SELECT statements issued by a connection.
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Only one of the options can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of the statement.
The setting of SET TRANSACTION ISOLATION LEVEL is set at execute or run time and not at parse time.
This example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READGOBEGIN TRANSACTIONSELECT * FROM publishersSELECT * FROM authors...COMMIT TRANSACTIONFebruary 17, 2006 at 11:02 am
thanks, but that doesn't really answer my question - as mentioned I know about the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" option, but as BOL mentions that is per connection.
As the database is accessed from a website the connections are established many times - and I'd like to set the database into "permanently read uncommitted" mode, and not having to do it for each connection (as that must clearly be a performance overhead with the changes - and if not, the network traffic for the commands are)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply