ExecuteXmlReader deadlocked exception; new to framework 1.1

[Adam Smith]

When executing ExecuteXmlReader() against a table where records are
being inserted, I get:

9/5/2003 8:39:47 AM Transaction (Process ID 66) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()

The 1.1 Framework help says this is design, that an SqlException is
raised:

An exception occurred while executing the command against a locked
row. This exception is not generated when using Microsoft .NET
Framework version 1.0.

Has anyone found a workaround to this?

Thanks in advance.

---Adam Smith

 

[Adam Smith]

You can never be sure what another programmer might do, or in our case
a QA tester executing an sql script. I found a solution, one that
still raises an exception, but does not "deadlock" and cause data
loss.

It involves the database setting SET LOCK_TIMEOUT 0

For example:

try
{
dbCommand.CommandText = "SET LOCK_TIMEOUT 0";
dbCommand.ExecuteNonQuery();
dbCommand.CommandType = CommandType.StoredProcedure;

dbCommand.CommandText = "some_stored_procedure";

xmlReader = dbCommand.ExecuteXmlReader();
}
catch(Exception e)
{
log(e);
}

---Adam Smith

Adam,

Well, one has to ask, what do you have running somewhere else that is
locking up the row for so long? I think that if you address that, then the
exception becomes a moot point. If I received this error, I would think
that it is a flaw in the design somewhere.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

When executing ExecuteXmlReader() against a table where records are
being inserted, I get:

9/5/2003 8:39:47 AM Transaction (Process ID 66) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()

The 1.1 Framework help says this is design, that an SqlException is
raised:

An exception occurred while executing the command against a locked
row. This exception is not generated when using Microsoft .NET
Framework version 1.0.

Has anyone found a workaround to this?

Thanks in advance.

---Adam Smith