By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Avoiding deadlocks

P: 1
I'm trying to develop a reader-lock-free application by using versioning. Instead of using SQL server 2005 built-in transactions, I'm inserting timestamped rows, so that readers would never have to wait for a writer. That is, I never use BEGIN TRANSACTION or any statements like that. I am aware that there exists a snapshot isolation mode which works similar to what I'm trying to do, but I cannot use it for reasons which are not relevant.

The issue is, when I stress the application using about 10 serialized writer threads and one reader thread, occasionally, the reader thread is deadlocked (error 1205).

What setting should I use in SQL server so that the reader thread is never deadlocked? Is it possible? What I want is a setting that would allow a read statement like "SELECT from table_a where timestamp <= 12345678", even though someone is currently writing to table_a, I have tried to figure out the isolation level settings and tried a few, like WITH(READUNCOMMITTED) for the reader storeprocs, same setting for the writer storeprocs, but with no success.. Do I have to set it on both? Do I have to set this as a global level?

I am not worried about reading corrupt data as the reader is only allowed to read posts which are older than a certain timestamp, and updating or deleting old posts is not allowed in the application.

any ideas would be greatly appreciated

Best regards,
Tor
Nov 13 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.