TransactionScope가 조기에 완료 됨

TransactionScope가 조기에 완료 됨

---

TransactionScope 내에서 실행되는 코드 블록이 있으며이 코드 블록 내에서 DB를 여러 번 호출합니다. 전체 영역을 선택, 업데이트, 생성 및 삭제합니다. 삭제를 실행할 때 SqlCommand의 확장 메서드를 사용하여 실행합니다.이 쿼리는 교착 상태에 빠질 수 있으므로 자동으로 쿼리를 다시 제출합니다.

교착 상태가 발생하고 함수가 쿼리를 다시 제출하려고 할 때 문제가 발생한다고 생각합니다. 이것은 내가받는 오류입니다.

현재 연결과 관련된 트랜잭션이 완료되었지만 삭제되지 않았습니다. SQL 문을 실행하는 데 연결을 사용하려면 먼저 트랜잭션을 삭제해야합니다.

다음은 쿼리를 실행하는 간단한 코드입니다 (아래의 모든 코드는 TransactionScope를 사용하여 실행 됨).

using (sqlCommand.Connection = new SqlConnection(ConnectionStrings.App))
{
    sqlCommand.Connection.Open();
    sqlCommand.ExecuteNonQueryWithDeadlockHandling();
}

교착 상태 쿼리를 다시 제출하는 확장 메서드는 다음과 같습니다.

public static class SqlCommandExtender
{
    private const int DEADLOCK_ERROR = 1205;
    private const int MAXIMUM_DEADLOCK_RETRIES = 5;
    private const int SLEEP_INCREMENT = 100;

    public static void ExecuteNonQueryWithDeadlockHandling(this SqlCommand sqlCommand)
    {
        int count = 0;
        SqlException deadlockException = null;

        do
        {
            if (count > 0) Thread.Sleep(count * SLEEP_INCREMENT);
            deadlockException = ExecuteNonQuery(sqlCommand);
            count++;
        }
        while (deadlockException != null && count < MAXIMUM_DEADLOCK_RETRIES);

        if (deadlockException != null) throw deadlockException;
    }

    private static SqlException ExecuteNonQuery(SqlCommand sqlCommand)
    {
        try
        {
            sqlCommand.ExecuteNonQuery();
        }
        catch (SqlException exception)
        {
            if (exception.Number == DEADLOCK_ERROR) return exception;
            throw;
        }

        return null;
    }
}

다음 줄에서 오류가 발생합니다.

sqlCommand.ExecuteNonQuery();

---

TransactionScope에서 select 문을 억제하는 것을 잊지 마십시오. SQL Server 2005 이상에서는 with (nolock)를 사용하는 경우에도 선택이 접촉하는 해당 테이블에 잠금이 생성됩니다. 이것을 확인 하고 TransactionScope를 설정하고 사용하는 방법을 보여줍니다 .

using(TransactionScope ts = new TransactionScope 
{ 
  // db calls here are in the transaction 
  using(TransactionScope tsSuppressed = new TransactionScope (TransactionScopeOption.Suppress)) 
  { 
    // all db calls here are now not in the transaction 
  } 
} 

---

나는 트랜잭션이보다 더 긴 기간 동안 실행할 때이 메시지가 발생할 수 있습니다 것으로 나타났습니다 maxTimeout위한 System.Transactions. TransactionOptions.Timeout증가 하는 것은 중요하지 않으며 maxTimeout.

의 기본 값은 maxTimeout10 분으로 설정하고, 그 값은 수 만 수정할 수machine.config

machine.config제한 시간을 수정 하려면에 다음을 (구성 레벨에서) 추가하십시오 .

<configuration>
    <system.transactions>
        <machineSettings maxTimeout="00:30:00" />
    </system.transactions>
</configuration>

The machine.config can be found at: %windir%\Microsoft.NET\Framework\[version]\config\machine.config

You can read more about it in this blog post: http://thecodesaysitall.blogspot.se/2012/04/long-running-systemtransactions.html

---

I can reproduce the problem. It is a transaction timeout.

using (new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 0, 1)))
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var sqlCommand = connection.CreateCommand())
        {
            for (int i = 0; i < 10000; i++)
            {
                sqlCommand.CommandText = "select * from actor";
                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                    }
                }
            }
        }
    }
}

Throws System.InvalidOperationException with this message:

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

To solve the problem make your query run faster or increase the timeout.

---

If an exception happens inside a TransactionScope it is rolled back. This means that TransactionScope is done. You must now call dispose() on it and start a new transaction. I'm honestly not sure if you can reuse the old TransactionScope or not, I've never tried, but I'd assume not.

---

My issue was a stupid one, if you sit on a debug break through the timeout you will get this. Face Palm

Man, programming makes you feel thick some days...

---

Confirmed this error can also be caused by a transaction timeout. Just to add to what Marcus + Rolf have stated, if you haven't explicitly set a timeout on the TransactionScope, the timeout TimeSpan will assume a default value. This default value is the smaller of:

1. If you've overridden the local app.config / web.config setting, e.g.

   <system.transactions>
   <defaultSettings timeout="00:05:00" />
   </system.transactions>
   

2. But this is then 'capped' at the machine.config setting <machineSettings maxTimeout="00:10:00" />

---

This exception can also be caused by disable Microsoft Distributed Transaction Coordinator.

If we want enable it, we run "dcomcnfg" and select "Component Services" -> "My Computer" -> "Distributed Transaction Coordinator" -> "Local Service DTC" and choose "Properties".

It should be checked "Allow Remote Client", "Allow Inbound", "Allow Outbound" and "No Authentication Required".

참고URL : https://stackoverflow.com/questions/2921920/transactionscope-prematurely-completed