Trapped escaped exception: This SqlTransaction has completed it is no longer usable.Įdit: I reproduced the error against SQL Server. Transaction state after second command: AbortedĮrror: System.InvalidOperationException: This SqlTransaction has completed it is no longer usable.Īt .ZombieCheck()Īt .Commit()Īt (String keyValue) in C:\Dev\SqlClient-Aborted-Transaction-Repro\Program.cs:line 194 However if you do need to make sure that a stored procedure is only called once then you can use a sql mutex lock using spgetapplock. Transaction state after first command: Aborted sql server deadlocks are normal & to be expected in this type of scenario - MS's recommendation is that these should be handled on the application side rather than the db side. Trapped escaped exception: Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Īt .OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)Īt .OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)Īt .ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)Īt .TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)Īt .TryHasMoreRows(Boolean& moreRows)Īt .TryReadInternal(Boolean setTimeout, Boolean& more)Īt .Read()Īt .CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)Īt .ExecuteScalar()Īt (String keyValue) in D:\GitHub\Issues\458\repro\Program.cs:line 208ĬlientConnectionId:830ce54b-b3a6-4c15-8592-9e7f960b8895 Transaction state after commit: No transactionĮrror: (0x80131904): Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Transaction state after second command: Active Transaction state after first command: Active Transaction state before first command: Active To reproduceĬreate a new database using the ZombieDb.sql scriptįor. The issue reproduces with /dotnet core 2.1 and /dotnet core 3.1. I have a reduced repro case which consistently reproduces the error on SQL Server 12 and 13, tested across multiple machines.
The issue does not seem to occur when using ExecuteReader or ExecuteNonQuery.
#NET NO EXCEPTION THROWN FOR DEADLOCK SQL CODE#
The transaction state is correctly set to Aborted, but the lack of an exception confuses application code and libraries like EF Core and who assume (fairly reasonably) that no exception means success.Īdditionally, ExecuteScalar is returning the value that would be expected for a successful transaction even though the transaction has been rolled back. _log.We have encountered an issue in production where ExecuteScalar run in context of a RepeatableRead transaction intermittently fails to raise an exception when SQL Server selects it as a deadlock victim and rolls back the transaction. Protected static void Execute(string connectionString, string commandString) get a deadlock exception when executing a command (using VB.NET 2005, with theReader theCommand.ExecuteReader () would throw, say, a timeout error or a. TimeSpan delay = TimeSpan.FromSeconds(5) Since both tasks hold the resource lock they own while waiting for the other resource lock to become available, neither can make progress. Could I add here any code to handle it?Ĭan I do this handling inside a catch block?īuilding on response, I present a general purpose retry wrapper method: private static T Retry(Func func) The simplest deadlock occurs when one process holds a lock on resource A and requests a lock on resource B, and another process holds a lock on resource B and requests a lock on resource A. If (Connection.State = ConnectionState.Closed) If (DatabaseType != enumDatabaseType.SQLCOMPACT)Ĭommand.CommandTimeout = Connection.ConnectionTimeout
#NET NO EXCEPTION THROWN FOR DEADLOCK SQL HOW TO#
I am using NUnit and Moq, but I am not sure how to fake this. If(DatabaseType != enumDatabaseType.ORACLE)Ĭommand.CommandText ="BEGIN " + query + " END " It seems that you can't go new SqlException () so I am not sure how I can throw an exception especially without somehow calling the database (and since these are unit tests it is usually advised not to call the database since it is slow). Here is the code for a method we use: public int ExecuteQuery(string query) We want the deadlock victim to retry perhaps after some time delay, but I don't know if it is possible.
Part of our team is working on the sql code and transactions to minimize these events, but I'm thinking about making this Database class robust against deadlock situations. We are experiencing serious problems with Deadlock situations in SQL Server 2000. For instance, when I need to execute a procedure, I call Database.ExecuteProcedure(procedureName, parametersAndItsValues). I have a class 'Database' that works as a wrapper for ADO.net.
0 kommentar(er)
