473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Avoiding deadlocks

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
0 1176

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

Similar topics

14
by: Eloff | last post by:
This is not really Python specific, but I know Python programmers are among the best in the world. I have a fair understanding of the concepts involved, enough to realize that I would benefit from...
1
by: AKS | last post by:
I am getting lot of deadlocks in my application. As it is very complex ti avoid deadlocks at this stage of application we have done few steps to lessen the impact. We have added retries after...
3
by: Hasan | last post by:
Hi I'm having a problem with deadlocks in a table in SQL server when trying to update it through Biztalk 2004. There is no problem when I use the same Biztalk solution to update a similar dummy...
1
by: Matt White | last post by:
We've found deadlocks in the trace file that were not captured by our Powerbuilder application. Some deadlocks are trapped or, at least, reported to the user as a db error, and others are...
4
by: T Dubya | last post by:
We're experiencing a large number of deadlocks since we began running SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper threading intel processors. We don't have the same problem on...
7
by: Marcus | last post by:
Hello all, I am trying to figure out when it is appropriate to use shared and exclusive locks with InnoDB. Using shared locks (lock in share mode), I can easily create a scenario with 2 clients...
9
by: Mike Carr | last post by:
I am running into an issue. Recently I installed IBuySpy Portal and then converted the data source to odp.net. When debugging the app my machine would freeze or become really slow. I can reproduce...
6
by: Greg Stark | last post by:
There's another poster complaining about referential integrity checks causing deadlocks. Unfortunately I've deleted the message so this response (and the archives aren't responding) isn't going to...
4
by: John Rivers | last post by:
There are many references to deadlock handlers that retry the transaction automatically. But IMO a deadlock is the result of a design flaw that should be fixed. My applications treat...
1
by: puzzlecracker | last post by:
There is a common way to deal with deadlock using timeouts to obtain multiple locks (In java, there is a tryLock for that). I wonder about common ways to avoid deadlock prevalent in CSharp. Is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.