RogerBman


I searched a bit but didn¡¯t get too far in actually solving a case of deadlock in a simple query I have running here. The queries in question are executed under 2 separate transactions (Serializable IsolationLevel) and are shown below. I guess I don¡¯t understand how those 2 can deadlock because they are operating on different rows of the table and Serializable should keep them isolated pretty well too. Is it because I¡¯m using the column value inside an update stmt How should this query be split if that¡¯s the case

This is what the SQL Profiler has to say:

Lock: Deadlock Chain Deadlock Chain SPID = 59

Lock: Deadlock Chain Deadlock Chain SPID = 57

Lock: Deadlock my_user_name

57: UPDATE CreditCard SET Balance = Balance - 200 WHERE (Account = 0 AND CardHolder = 'Foo' AND Balance - 200 >= 0)

59: UPDATE CreditCard SET Balance = Balance - 250 WHERE (Account = 3 AND CardHolder = 'Bar' AND Balance - 250 >= 0)

I also used DBCC TRACEON(1204, 3605, -1) but I don¡¯t understand what the SQL log is telling me. Can anyone shed some light on why the above 2 statements sometimes cause the following:

System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I really don't want to retry the update if I don't have to. Table looks like:

Column DataType Length

Account int 4

CardHolder char 64

Balance float 8




Re: How to avoid deadlock with simple update statements

Jens K. Suessmeyer


YOu should take alook on the type of lock (page / table / rowlock), did you try to enforce the rowlock on the Update statement using the ROWLOCK syntax

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---







Re: How to avoid deadlock with simple update statements

RogerBman

Hmm, should I have too Here is the exact piece of code I'm executing in 2 different threads:

Code Snippet

using (TransactionScope scope = new TransactionScope())

{

using (SqlConnection conn = new SqlConnection(DatabaseConnection))

{

conn.Open();

string command = string.Format("UPDATE CreditCard SET Balance = Balance - 200 WHERE (Account = {0} AND CardHolder = '{1}' AND Balance - 200 >= 0)", acctNum, cardHolder);

SqlCommand sqlCommand = new SqlCommand(command, conn);

sqlCommand.ExecuteNonQuery();

}

scope.Complete();

}

Shouldn't SQL be doing the locking for me esp. since I'm under a serializable transaction

Unfortunatly, if I change the command too:

string command = string.Format("UPDATE CreditCard WITH (ROWLOCK) SET Balance = Balance - 200 WHERE (Account = {0} AND CardHolder = '{1}' AND Balance - 200 >= 0)", acctNum, cardHolder);

I still receive deadlock error messages. I even tried the SERIALIZABLE hint as well with the same result.






Re: How to avoid deadlock with simple update statements

William Vaughn

Okay, I see a couple of issues.

First why are you trying to use a transaction With a single operation, there is an implied transaction so wrapping this operation in a transaction won't do anything useful except confuse the server and make things more difficult for you.

Next, it's dangerous to concatenate arguments into a WHERE clause as it can lead to a number of issues including SQL injection attacks. I recommend use of parameterized Command objects. These are created once and re-used with new Parameter values. This approach deals with the O'Malley issue as well as other Parameter formatting and framing issues.

hth






Re: How to avoid deadlock with simple update statements

RogerBman

This is just a simplified repro. In the real system there are 2 webservices updating databases and I need both to happen or not happen -- so I scope both webservice calls under the same tx. What you see above is just 1 of the updates to the 1st database.

Yes, I know about sql injection because this is user supplied data etc. but this is just a simple repro that I've used to show the problem with these 2 update statements.

BTW, removing the transaction scope does seem to make the problem go away -- but isn't this because the default IsolationLevel in sql is ReadCommitted which is fairly low compared to the System.Transaction default of Serializable

...

...

Talking to myself here Smile. Yupe, if I set the IsolationLevel on the TransactionScope to ReadCommitted things work fine. What is with Serializable that's the problem The heart of my question is why SQL thinks that operating on 2 distinct rows is ever deadlockable.





Re: How to avoid deadlock with simple update statements

William Vaughn

Ah, I doubt if you can process two operations like this on different connections. I doubt if you can get away with it with MARS either.