|
I have an application built in C# that uses a SQL Anywhere 12.0.1.3769 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. Thanks anyway. |
Are you getting SQLCODE -306 or -307?
Deadlock detected
Error constant SQLE_DEADLOCK ODBC 2 state 40001 ODBC 3 state 40001 Severity 13 SQLCODE -306 SQLSTATE 40001 Sybase error code 1205
Probable cause
You attempted to read or write a row and it is locked by another user. Also, the other user is blocked directly or indirectly on your own transaction. This is a deadlock situation and your transaction has been chosen as the one to rollback.
All threads are blocked
Error constant SQLE_THREAD_DEADLOCK ODBC 2 state 40001 ODBC 3 state 40001 Severity 13 SQLCODE -307 SQLSTATE 40W06 Sybase error code 1205
Probable cause
You attempted to read or write a row and it is locked by another user. Also, all other threads (see the -gn server option) are blocked waiting for a lock to be released. This is a deadlock situation and your transaction has been chosen as the one to rollback.