CODEWITHSUNDEEP
sql-server-2005ado.netdeadlock
Steve Johnson
Steve Johnson

Reputation: 151

ADO.NET DeadLock

I'm experiencing an intermittent deadlock situation with following (simplified) code.

DataSet my_dataset = new DataSet()
SqlCommand sql_command = new SqlCommand();

sql_command.Connection = <valid connection>
sql_command.CommandType = CommandType.Text;
sql_command.CommandText = 'SELECT * FROM MyView ORDER BY 1'

SqlDataAdapter data_adapter = new SqlDataAdapter(sql_command);

sql_command.Connection.Open();

data_adapter.Fill(my_dataset);

sql_command.Connection.Close();

The error I get is:

Transaction (Process ID 269) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As I understand it, simply filling a DataSet via the ADO.Net .Fill() command shouldn't create a lock on the database. And, it would appear from the error message that the lock is owned by another process. The View I'm querying against has select statements only, but it does join a few table together.

SQL Server 2005 (9.0.4035)

Upvotes: 0

Views: 1482

Answers (3)

YWE
YWE

Reputation: 2909

A select query with joins can indeed cause a deadlock. One way to deal with this is to do the query in a SqlTransaction using Snapshot Isolation.

using(SqlTransaction sqlTran = connection.BeginTransaction(IsolationLevel.Snapshot))
{
    // Query goes here.
}

A deadlock can occur because it locks each table being joined one after another before performing the join. If another query has a lock on a table that the other query needs to lock, and vice versa, there is a dead lock. With Snapshot Isolation queries that just read from tables do not lock them. Integrity is maintained because the read is actually done from a snapshot of the data at the time the transaction started.

This can have a negative impact on performance, though, because of the overhead of having to produce the snapshots. Depending on the application, it may be better to not use snapshot isolation and instead, if a query fails do to a deadlock, wait a little while and try again.

It might also be better to try to find out why the deadlocks are occurring and change the structure of the database and/or modify the application to prevent deadlocks. This article has more information.

Upvotes: 2

rsenna
rsenna

Reputation: 11982

You may try this:

  • Lower the transaction level for that query (for instance, IsolationLevel.ReadUncommited).
  • Use the NOLOCK hint on you query.

Upvotes: 1

0xCAFEBABE
0xCAFEBABE

Reputation: 5666

It might be far off and not the solution to your problem, check other solutions first - but, we had a similar problem (a select that locks records!) that after much effort we tracked to the file/SMB layer. It seemed that under heavy load, reading files from the networked drive (SAN) got held up, creating a waiting read lock on the actual database files. This expressed as a lock on the records contained.

But this was a race condition and not reproducable without load on the drives. Oh, and it was SQL Server 2005, too.

You should be able to determine using the SQL Server included tools which transactions are deadlocking each other.

Upvotes: 0

Related Questions