>Hey Larry, your great, thats exactly the problem. I was not aware of isolation levels. I am learning SQL Server on my own for about a year so from time to time I hear of something totally new. I'm now trying to acquire the info to decide which is the best isolation level to use. What i really want is to allow other users to be selecting and accessing the tables while someone else is adding/updating. Is that possible ???
>
Chris,
Yes. As JVP pointed out, you can set the transaction isolation level to READ UNCOMMITTED. This will give you free reign to access any part of the table for reading. However, the drawback here is that you may not be able to get consistent results.
Read Uncommitted gives you the ability to read data that is in the midst of a transaction. Take the following example:
User A begins a transaction
User A changes information in table A
User B queries the information in table A
User B sees the "changed" information in table A
User A changes the same row again to something else
User B queries the information in table A and gets the "new" information
As John posted, Read Uncommitted is also called "dirty reads". The information is in a state of flux and can change from one query to the next. If you want a snapshot of the data and you don't care if that data will be changed from one query to the next, use it.
FWIW, I use it when generating snapshot reports. It doesn't matter to me if the data is going to be rollbacked or committed. Even if committed, the user who initially had the lock could most likely get the record again and change it back so the data is no more stable, IMO.
HTH.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao