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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-12-13 : 08:10:56
|
| Tommy writes "Hello Everyone, I have been struggling through this issue all week. My situation is unique but I don't think unheard of. I have several Visual Basic applications attempting to fire the same stored procedure which searches the same table (queue) at the exact same time (7 processes every half second to be exact). This is causing major Deadlocks!!! I care more about concurrency than accuracy but here is the thing...due to the nature of the data I can't give up the accuracy. I have become very familiar with the different isolation levels such as READ COMMITTED AND REPEATABLE READ and lock hints. We are currently using the REPEATABLE READ Isolation level in this particular stored procedure. When I modified the Isolation level to READ COMMITTED and put in the READPAST Lock Hint my concurrency went through the roof. The only thing is that there were some duplicate reads (one after another) which caused issues later because the way this Isolation Level works it released the Selected items long enough for them to be grabbed again by another process before the update took place (thus 2 updates to the same record that were pulled). My question is: Is there a way to enforce REPEATABLE READ Isolation level in SQL Server 2000 with the READPAST Lock Hint. SQL Server 2000 books online as well as SQL Server 7.0 Books Online suggest that there isn't and it can only be done in the Isolation Level of READ COMMITTED. I know for a fact that you can now do this in SQL Server 2005 but short of upgrading my SQL Server to 2005 is there a SQL Server 2000 Hotfix/Patch that I can update to receive this capability. My current configuration is the following: 3.0 GHz Dual Xeon Processors 70 GB SCSI HD (with 320 Adapter) SQL Has priority on box and the box is dedicated to SQL Server alone. I have already check the indexes and the querys are as light as possible. I basically want the locks to last until the end of the transaction (thus no duplicate reads from multiple processes), but I want any current locks to be skipped using the READPAST lock hint (thus gaining the concurrency). Also I am open to any further suggestions. Thanks alot for any help, Tommy" |
|
|
|
|
|
REPEATABLE READ Isolation Level with READPAST???