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.
| Author |
Topic |
|
mgottli
Starting Member
4 Posts |
 Posted - 2005-12-12 : 13:41:22
|
| I have a transaction that may take up to 2 hours to complete. When it is running, the data is locked per default behavior and other users trying to read data are blocked. This is a problem because of the time it takes to complete. I understand that I can set the isolation level on the statements that are reading the data. Problem is this transaction is being retrofitted into a 5 year old application, and it would be a lot of work to find and update all these select statements. Is there any way to set the isolation level from the update end to allow anyone who wants to read to read the "dirty" data. Thank you! |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-12 : 13:55:39
|
| 1. I think it would be dangerous to just assume that you want to read dirty data for all cases for all queries in the entire application. While you probably can't change all of the commands if this is something you do want to do you can check SET TRANSACTION ISOLATION LEVEL and consider setting that in the application object that creates/manages the connections. It is managed connection by connection.2. Can you run the command at night or during off hours instead of during times of high activity?3. Have you ran the query and looked at the execution plan to ensure that any fields in the where clause are indexed so it isn't just doing a table scan? |
 |
|
|
|
|
|
Transaction isolation level set by the update?