February 6, 2004 at 3:05 am
Dear All,
in a multi-user application I need to enable "reading" users to access data even while other "writing" users modify or create rows in a table. This seems to be a standard task, but the only choice ssems to be to either wait until writing is done (regular locking), or read uncommited (with NOLOCK), or skip locked rows (with READPAST). What my users want is different and appears to be "natural" : Show the table in its last valid state, i.e. read data as it was before any other unfinished Transaction started modifying it.
I hear Oracle and PostgreSQL support this and call it a "snapshot" mechanism. I can't find a way in SQLServer2000. Any ideas ?
Kay
February 9, 2004 at 8:00 am
This was removed by the editor as SPAM
February 9, 2004 at 2:23 pm
Look at "SET TRANSACTION ISOLATION LEVEL" i Books on line for more info.
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 TRANSACTIONAdjusting Transaction Isolation Levels
February 11, 2004 at 4:51 am
Does any of these offer a snapshot ?
Not as far as I know them - "READ UNCOMMITTED" reads everything, even stuff that's only half way through, and the other three (READ UNCOMMITTED, REPEATABLE READ and SERIALIZABLE) wait until another writer is done. None of these gives you the old data as it was before a modifying transaction opened.
Or did I miss anything ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply