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(READUNCOMM ITTED) 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
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(READUNCOMM ITTED) 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