"Robinson" <itoldyounottospamme@nowmyinboxisfull.com> wrote in message
news:ecjtmn$qe9$1$8300dec7@news.demon.co.uk...
>I ran a test overnight last night (the first of many I will do), to see how
>my caching system holds up to 3 or 4 multiple users all accessing at the
>same time. Basically I create random operations and apply them at random
>times to the database via. stored procedures. Anyway, 3 of the 4 processes
>died at around 3.a.m. due to deadlock. The error was "Transaction (Process
>ID nn) was deadlocked on lock resources with another process and has been
>chosen as the deadlock victim. Rerun the transaction" - this is SQL Server
>2005 (or rather, the Desktop Engine).
>
> My questions are as follows:
>
> I can detect this error in the client (VB.NET/.NET 2.0) with Try / Catch,
> so should the general pattern for all operations that access the database
> be something like this:
>
>
>
>
> Dim bFinished As Boolean = False
>
> While not bFinished
>
> Try
>
> .... Execute the stored procedure against the data source.
>
> .... Update my cache data structures with the result....
>
> ....
>
> Catch Ex as Exception
>
> ' Log the error
>
> .....
> ....
>
> If not Ex.Message is something like "deadlock, rerun..."
>
> ' Failed for some other reason....
>
> bFinished = true
> Else
> ' Failed due to deadlock, so we are not finished...
> End If
>
> End Try
>
> End While
>
>
> This is of course with respect to a client application using VB.NET (.NET
> 2.0). I don't want to "TRY/CATCH" in the stored procedure, because I
> don't want to use non-SQL commands there - ie. I will eventually be
> porting to MySQL, ORACLE, etc.
>
> Is this a good pattern for dealing with deadlock in your experience?
>
>
> Thanks....
>
>
>
>
>
>

"Robinson" <itoldyounottospamme@nowmyinboxisfull.com> wrote in message
news:ecjtmn$qe9$1$8300dec7@news.demon.co.uk...
>I ran a test overnight last night (the first of many I will do), to see how
>my caching system holds up to 3 or 4 multiple users all accessing at the
>same time. Basically I create random operations and apply them at random
>times to the database via. stored procedures. Anyway, 3 of the 4 processes
>died at around 3.a.m. due to deadlock. The error was "Transaction (Process
>ID nn) was deadlocked on lock resources with another process and has been
>chosen as the deadlock victim. Rerun the transaction" - this is SQL Server
>2005 (or rather, the Desktop Engine).
>
> My questions are as follows:
>
> I can detect this error in the client (VB.NET/.NET 2.0) with Try / Catch,
> so should the general pattern for all operations that access the database
> be something like this:
>
>
>
>
> Dim bFinished As Boolean = False
>
> While not bFinished
>
> Try
>
> .... Execute the stored procedure against the data source.
>
> .... Update my cache data structures with the result....
>
> ....
>
> Catch Ex as Exception
>
> ' Log the error
>
> .....
> ....
>
> If not Ex.Message is something like "deadlock, rerun..."
>
> ' Failed for some other reason....
>
> bFinished = true
> Else
> ' Failed due to deadlock, so we are not finished...
> End If
>
> End Try
>
> End While
>
>
> This is of course with respect to a client application using VB.NET (.NET
> 2.0). I don't want to "TRY/CATCH" in the stored procedure, because I
> don't want to use non-SQL commands there - ie. I will eventually be
> porting to MySQL, ORACLE, etc.
>
> Is this a good pattern for dealing with deadlock in your experience?
>
>
> Thanks....
>
>
>
>
>
>