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
"Nicholas Paldino [.NET/C# MVP]" <ni**************@exisconsulting.com> wrote in message news:<O#**************@tk2msftngp13.phx.gbl>...
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]
- ni**************@exisconsulting.com
"Adam Smith" <ad*********@hotmail.com> wrote in message
news:cd**************************@posting.google.c om... 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(Com mandBehavior
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