오류-현재 연결과 관련된 트랜잭션이 완료되었지만 삭제되지 않았습니다.

오류-현재 연결과 관련된 트랜잭션이 완료되었지만 삭제되지 않았습니다.

---

TransactionScope여러 데이터베이스 쿼리를 트랜잭션으로 래핑 하기 위해를 사용하는 데 문제가있었습니다 . 저는 batchsize 500과 함께 SqlBulkCopy를 사용하고 있습니다. 배치 크기를 1000으로 늘 렸을 때 오류가 발생합니다.

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

이것은 내가 사용하는 코드입니다.

using (var scope = new TransactionScope())
{
    using (var connection = (SqlConnection)customerTable.OpenConnection())
    {
        var table1BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName1
        };

        table1BulkCopy.WriteToServer(table1DataTable);

        var table2BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName2
        };

        table2BulkCopy.WriteToServer(table2DataTable);

        var table3BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName3
        };

        table1BulkCopy.WriteToServer(table3DataTable);

        var table4BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName4
        };

        table4BulkCopy.WriteToServer(table4DataTable);

        scope.Complete();
    }
}

---

이는 트랜잭션 시간이 초과 될 때 발생할 수 있습니다. 이와 같이 트랜잭션의 시간 제한을 늘릴 수 있습니다 (예상 트랜잭션 길이에 적합한 값 사용). 아래 코드는 15 분입니다.

using (TransactionScope scope = 
             new TransactionScope(TransactionScopeOption.Required, 
                                   new System.TimeSpan(0, 15, 0)))
  {
      // working code here
  }

이것이 1000이 아닌 배치 크기 500에서 작동 할 수 있었던 이유입니다.

---

I found that setting the timeout in the TransactionScope didn't work for me. I also needed to add the following config key to the end of the machine.config <configuration> tag to extend past the default max timeout of 10 minutes.

<system.transactions>
    <machineSettings maxTimeout="00:30:00" /> <!-- 30 minutes -->
</system.transactions>

Credit: http://thecodesaysitall.blogspot.com.au/2012/04/long-running-systemtransactions.html

---

Move scope.Complete(); outside the connection block.

using (var scope = new TransactionScope())
{
  using (var connection = (SqlConnection)customerTable.OpenConnection())
   {
    //
   }
  scope.Complete();
}

---

Pretty obvious problem with time out, but you don't get effect if you set TransactionOptions.Timeout higher. Even if you set TimeSpan.MaxValue you don't actually get a profit. It doesnt matter that the Timeout property of the TransactionOptions are set to a higher value, TransactionOptions.Timeout can not exceed maxTimeout property. You should set up some changes in machine.config.

Shortly you should find machine.config file %windir%\Microsoft.NET\Framework\yourversion\config\machine.config
And add this in <configuration> tag:

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

Here you can set maxTimeout property to 30 minutes.
See following for more details http://thecodesaysitall.blogspot.com/2012/04/long-running-systemtransactions.html

---

The full answer must be more full.

You must specify - where will be max transaction timeout determined - in the .Net code, or in the server config

<sectionGroup name="system.transactions".... 
    ...allowDefinition="MachineOnly"
</sectionGroup>

In this case you can set max timeout in the machine.config

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

Or may be you want to override this behaivor in the application. Then in the machine.config you should set atribute the velue:

...allowDefinition="MachineToApplication"

This is a good arcticle: https://blogs.msdn.microsoft.com/ajit/2008/06/18/override-the-system-transactions-default-timeout-of-10-minutes-in-the-code/

참고URL : https://stackoverflow.com/questions/11453066/error-the-transaction-associated-with-the-current-connection-has-completed-but