468,242 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Avoiding deadlocks

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,
Nov 13 '08 #1
0 1057

Post your reply

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

Similar topics

14 posts views Thread by Eloff | last post: by
1 post views Thread by AKS | last post: by
3 posts views Thread by Hasan | last post: by
1 post views Thread by Matt White | last post: by
4 posts views Thread by T Dubya | last post: by
7 posts views Thread by Marcus | last post: by
9 posts views Thread by Mike Carr | last post: by
4 posts views Thread by John Rivers | last post: by
1 post views Thread by puzzlecracker | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.