CODEWITHSUNDEEP
c#t-sqltransactionsado.net
Andrei Barbolin
Andrei Barbolin

Reputation: 435

ADO.NET retry of transaction within one sql connection

I try to implement simple retry logic using ADO.NET and transactions. A sample of code:

        using(SqlConnection conn = new SqlConnection(m_connection))
        {
            conn.Open();

            bool committed = false;
            Exception exception = null;
            for (int i = 0; i < s_attempts; i++)
            {
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    // different commands here..

                    tran.Commit();
                    committed = true;
                    break;
                }
                catch (Exception ex)
                {
                    exception = ex;
                    tran.Rollback();
                }
            }

            if (!committed && exception != null)
            {
                throw exception;
            }
        }

Is it okay to create another transaction within one connection after the previous one was rollbacked?

Thank you in advance!

Upvotes: 2

Views: 783

Answers (1)

Damon
Damon

Reputation: 3012

There's no reason why you can't continue to create transactions on the same connection, provided all transactions are either committed or rolled back.

I would question why the first attempt failed though, I'm assuming you'll put some delay (possibly incrementing) between each attempt to commit the transaction.

Upvotes: 1

Related Questions