So, we have been having these deadlock problems and I tried to replicate this in a simple code snippet. Basically, we are logging in our database before some treatment, and updating with the result after.
Database is Sybase ASE 15.7, driver used is Adaptive Server Enterprise ODBC driver (v15.05). Code is C# 4.0
The code I use to replicate the problem is as follow :
Database :
create table dbo.test_deadlock_t(
id int identity,
col1 int not null,
col2 varchar(255) not null,
constraint test_deadl_id_pk primary key clustered ( id ))
alter table test_deadlock_t lock allpages
C# (ignoring some of the declarative code)
using System.Data.Odbc;
public const string cmdInsert = "INSERT INTO test_deadlock_t (col1, col2) VALUES (1, 'test') SELECT @@identity";
public const string cmdUpdate = "UPDATE test_deadlock_t SET col1 = 3, col2 = 'aaaaaaaa' WHERE id = {0}";
public static void Test()
{
Task[] tasks = new Task[threadCount];
for (int ii = 0; ii < threadCount; ii++)
{
tasks[ii] = Task.Factory.StartNew(() => InsertThenUpdate());
}
Task.WaitAll(tasks);
}
public static void Test2()
{
Task[] tasks = new Task[threadCount];
for (int ii = 0; ii < threadCount; ii++)
{
int ii_copy = ii;
tasks[ii_copy] = Task.Factory.StartNew(() => Update(ii_copy));
}
Task.WaitAll(tasks);
}
public static void InsertThenUpdate()
{
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
connection.Open();
OdbcCommand command = new OdbcCommand(cmdInsert, connection);
int result = (int)(command.ExecuteScalar() as decimal?).Value;
Update(result);
}
}
public static void Update(int id)
{
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
connection.Open();
OdbcCommand command = new OdbcCommand(String.Format(cmdUpdate, id), connection);
int result = command.ExecuteNonQuery();
Console.WriteLine("Update result : " + result);
}
}
Both Test and Test2 throw random deadlocks exceptions. I am not explicitely initiating transactions, the table and the queries are as simple as it gets, can anyone explain what is happening and how to avoid this issue ?
Thanks !
edit : this test case is actually not really reproducing the problem, I am editing this as I have the feeling that the deadlocks occur when the update set the varchar column with a bigger size.
As Jason mentionned, the deadlock might be coming from the update of the PK index. A possible cause might be that the queries are locking the table, realize that the page won't be sufficient, move the row to a new page, then try to lock the index to update while holding a lock on a page, when another query begins with querying the index and then asks for a lock on the page.
I'm not sure this will help anyone in the future, but unanswered questions suck, so here is what I found :
The problem only occur when the update makes the row longer. Jason was right in suggesting that the lock might come from the index. After further analysis of the logs, we got this :
Deadlock Id 29756: Process (Familyid 0, Spid 282) was waiting for a 'exclusive page' lock on page 26700113 of table 'test_deadlock_t' in database 'xxx' but process (Familyid 0, Spid 1051) already held a 'exclusive page' lock on it.
Deadlock Id 29756: Process (Familyid 0, Spid 1051) was waiting for a 'exclusive page' lock on page 29892374 of table 'test_deadlock_t' , indid 1 in database 'xxx' but process (Familyid 0, Spid 282) already held a 'exclusive page' lock on it.
The "indid 1" refers to the primary key.
My understanding is that sybase locks the page where it wants to do an update. In the meantime, a select somehow requests a lock on the index. The update then realizes that the page is too small for the updated row, so it tries to move the page, and requests a lock on the index. But the select already has a lock, and the select wants to access the same page...
If anyone has a better understanding of what is happening, I'd be very glad to know. In our case, using fixed-length fields solved the issue.