So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this.:ermm: For my logic to work, I cannot rollback the entire transaction, so SProc Y only performs as expected outside a transaction Whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot be rolled back to a savepoint. However, if SProc Y is called inside a transaction, I need to perform a SAVE TRAN and rolback to the savepoint.If SProc Y is called outside any transaction, I can begin a new transaction and rollback if a deadlock occurred and successfully retry the DML.In SProc Y, that runs DML statements that will fire triggers that call SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error.However, whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot support operations that write to the log file.In SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error.In both scenarios, I have a SProc - let's called it "X" - that is called mostly from within triggers (that performs some denormalized calculations and cannot be changed at this time) - in which deadlocks are often occurring. Does anyone know of way to accomplish what I am trying to accomplish without a doomed transaction given the constraints? I have seen many posts that this appears to be a limitation of the dbengine, but am looking for confirmation. ![]() Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try the DML that was chosen as the deadlock victim.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |