I have an application built in C# that uses a SQL Anywhere 220.127.116.1169 database.
I have a process (call it process #1) that runs as a thread in the C# program which inserts rows into tables that have been transferred to the system from our proprietary server. This process must check the data type of an object sent to it from the server and determine which table to update based on its type. It then needs to determine if a row for that object already exists in that table and if so, update it, otherwise insert a new row. This process works well when run on its own.
I have another process (call it process #2) which also runs as a thread in the same C# program that needs to perform a check between the data in one table called
To determine that a row in the
In an attempt to prevent deadlocks, we designed this stored procedure so it would copy the rows in the
But recently deadlocks have started happening. Now, the only writes that the stored procedure in question makes is to insert new rows into one table and update the
For these reasons, I feel it is likely that the first process and the second are deadlocking when they are both trying to update the same row in the
How can I prevent these two processes from deadlocking? They nothing about each other whatsoever. Is there anything that can be done on the database side to prevent the two processes from deadlocking? Both processes use the default transaction isolation level.
I've resolved this. It wasn't what I thought it was at all.
In my C# program, the second process is actually started by a thread taken from the thread pool by a timer every 30 seconds. There's a flag that indicates whether there's any work to do or not. If there is no work, the stored procedure is not called, but if there is, it is called. In addition, the stored procedure processes up to 1,000 rows at a time in a single transaction. If there are more rows than that, it loops and processes up to another 1,000 until there are no rows left. The stored procedure returns 1 if there are more rows to process.
What was happening is that the loop would occasionally take longer than the 30 second window to run, and new rows were coming in. At that point, a new thread would be created that would also call the same stored procedure. It turns out that the stored procedure was deadlocking with itself.
This was a recent code change that was made in response to another problem we had. I didn't realize that a timer was the wrong way to do the job.
Since then, I've change the code so it runs as a single thread in a loop. In each iteration, it checks to see if there's any work to do, and if there is, it executes the stored procedure in a loop as before. In any event, it then sleeps for 30 seconds and when it wakes up, it iterates again.