The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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 ListDetails that was newly downloaded and data in another table called Reads which is generated by a sensor device connected to the system.

To determine that a row in the ListDetails table is new, it has a bit column called IsNewRow; this column defaults to 1 when a row is inserted in to the table. There is a stored procedure which is run by process #2 once every 30 seconds. It's job is to join the ListDetails and Reads tables using specific conditions and insert new rows into a third table. It then updates the rows in the ListDetails table and sets the IsNewRow column to 0.

In an attempt to prevent deadlocks, we designed this stored procedure so it would copy the rows in the ListDetails table whose IsNewRow columns was set to 1 into a temporary table within the stored procedure. We then use this table to perform our operations and finally to update the rows in the ListDetails table. This works well and actually solves another problem we had. And for a long time, we did not see any deadlocks.

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 ListDetails table. The first process never touches the table that the second process is inserting new rows into. The only table that both processes can update is the ListDetails table.

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 ListDetails table.

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.

asked 06 Dec '12, 11:26

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 06 Dec '12, 11:27

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.

(06 Dec '12, 15:43) Breck Carter

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.

permanent link

answered 06 Dec '12, 16:12

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%

edited 06 Dec '12, 16:12

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×100
×10

question asked: 06 Dec '12, 11:26

question was seen: 2,801 times

last updated: 06 Dec '12, 16:12