SqlException on Commit() The transaction operation cannot be performed because there are pending requests working on this transaction

Question

My SQL transaction throws exception on Commit(). This is the first time I use transactions with SqlCommands so maybe I'm making some error in code. I saw other questions regardings same error but none of them helped. I tried explicitly calling Close() on reader but no use.

using (var selectModifiedCmd = new SqlCommand(selectModified, conn, trans))
                {
                    try
                    {
                        decimal qty, qtyPerUOM, weight, weightKg;
                        string no, binCode, binText, shelfNo, mainZone, sourceTu, destNo, cluster;
                        int lineNo, corridor, sortAsc, sortDesc, rowOrder;
                        short pricePerKg;

                        using (var reader = selectModifiedCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                               ........

                                using (var updateModifiedCmd = new SqlCommand(updateModified, conn, trans))
                                {
                                    ........

                                    updateModifiedCmd.ExecuteNonQuery();
                                }

                                using (var returnModifiedCmd = new SqlCommand(returnModified, conn, trans))
                                {
                                    returnModifiedCmd.Parameters.AddWithValue("no", no);
                                    returnModifiedCmd.Parameters.AddWithValue("lineNo", lineNo);

                                    returnModifiedCmd.ExecuteNonQuery();
                                }

                                trans.Commit();

                                Globals.WriteLog(MethodBase.GetCurrentMethod().Name, String.Format(logSuccess, no, lineNo, binCode, qty));
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        var trace = new StackTrace(ex, true);
                        Globals.WriteLog(
                            MethodBase.GetCurrentMethod().Name,
                            ex.Message + " At line: " + trace.GetFrame(trace.FrameCount - 1).GetFileLineNumber());

                        try
                        {
                            trans.Rollback();
                        }
                        catch (Exception exRollback)
                        {
                            Globals.WriteLog("Rollback error: ", exRollback.Message);
                        }
                    }
                }

You have your `trans.Commit()` inside the reader code, move it outside so that you close the reader and the command before you commit. From your code I think the commit command should be moved all the way to the bottom, **after** all the code you have there. — Lasse V. Karlsen, Sep 12 '18 at 07:56
@Lasse Vågsæther Karlsen but if I move it outside the while loop wouldn't it mean the command executes only once? I want it to execute multiple times on every Read().. — bigb055, Sep 12 '18 at 08:06
Then you need to start it inside as well, you can't commit a transaction more than once, so you only have 1 transaction here, and you get 1 chance to roll it back or commit it. And I doubt you can keep your reader open on the connection and start/commit a transaction anyway, you would have to have 2 connections, or grab all the data into memory first and close the reader before you start processing and using transactions. In short, **you cannot do it like this**, so find out how you want to process the data and rewrite accordingly. — Lasse V. Karlsen, Sep 12 '18 at 08:56
I moved the **Commit()** to the bottom, outside of the while loop. Seems to work now! — bigb055, Sep 12 '18 at 08:58

score 0 · Answer 1 · answered Oct 15 '20 at 19:52

Credit to the Lasse V. Karlsen in the comment thread above for this, but I wanted this to also appear in an answer because it is the answer, and some people don't read unanswered questions.

The issue is that the trans.Commit() is inside the using statement for the reader. It needs to be moved outside of that using statement so that the reader is dismantled before committing the change.

SqlException on Commit() The transaction operation cannot be performed because there are pending requests working on this transaction

1 Answers1