|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Handling Deadlock situations in client....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. 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.... Depends on exactly what it is you are doing, more than often it is a
complicated series of queries that you run and it's not as simple to simply rerun it. If you want to do a rerun approach then make sure you place it all into a transaction and roll back the transaction before trying to rerun, also some time in between is probably good (i.e thread.sleep after aborting the transaction) before running it again. I don't think it's a good idea to rerun forever, you should have a failcount and try maybe a maximum of three times with some time in between and then report it or log the error. KL. Show quote "Robinson" <itoldyounottospamme@nowmyinboxisfull.com> skrev i meddelandet news:ecjvhd$4df$1$8302bc10@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. 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.... > > > > >  |
|||||||||||||||||||||||
Other interesting topics