locked
Help Needed in Transaction RRS feed

  • Question

  • User1489758560 posted

    <div>Hello,</div> <div> </div> <div>I am using asp.net core 2.2 and i would need to use the transaction scope. on my business layer i am using transaction scope as follows</div> <div>

    TransactionOptions options = new TransactionOptions();
                options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
                options.Timeout = new TimeSpan(0, 10, 0); 
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
                { 	
    
    	        // Method 1 which has DB connnection am opening and coling the connection properly
    		// Method 2	 which has DB connnection am opening and coling the connection properly
    		// Method 3  which has DB connnection am opening and coling the connection properly		
    		
                 scope.Complete();
    
    	    }

    DAL Layer :</div> <div></div> <div>

    public class EmpDB  : DbContext
        {
             
            public EmpDB()
            {
            }
    
            public EmpDB(DbContextOptions<EmpDB> options)
                : base(options)
            {
            }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                if (!optionsBuilder.IsConfigured)
                {
                    optionsBuilder.UseSqlServer(//ConnectionString);
                }
            }
    
    
    public int GetEmpId(string EmpEmail)
            {
                using (EmpDB empDB = new EmpDB())
                {
                    using (var dbCommand = empDB.Database.GetDbConnection().CreateCommand())
                    {
                        // proc call with parameters
    
                        if (dbCommand.Connection.State != ConnectionState.Open)
                        {
                            dbCommand.Connection.Open();
                        }
                        int EmpId = (int)dbCommand.ExecuteScalar();
                         
                        dbCommand.Connection.Close(); 
                        return EmpId 
                    }
          }
      }
    

    when i run the API, am getting the below error</div> <div></div> <div>

    System.PlatformNotSupportedException: This platform does not support distributed transactions.
       at System.Transactions.Distributed.DistributedTransactionManager.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
       at System.Transactions.TransactionInterop.GetDistributedTransactionFromTransmitterPropagationToken(Byte[] propagationToken)
       at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
       at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
       at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
       at System.Transactions.Transaction.Promote()
       at System.Transactions.TransactionInterop.ConvertToDistributedTransaction(Transaction transaction)
       at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
       at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
    

    am no sure what mistake am doing??? please help me to solve this error. i tried to google through and people are saying .net core 2.2 supports transaction scope. please suggest to solve the issue</div> <div></div>

    Thursday, April 23, 2020 3:14 AM

All replies

  • User-474980206 posted

    While not related to your error, Transaction scope uses thread local storage, so it’s not compatible with asp.net core, which runs multiple requests on the same thread. You should use sql servers 2 phase commit, or use the same connection, and use begin tran.

    Thursday, April 23, 2020 3:57 AM
  • User1120430333 posted

    How do you know that Distributed Transaction Coordinator is even enabled on the Web server computer's O/S and on  the database server computer's O/S? 

    https://en.wikipedia.org/wiki/Microsoft_Distributed_Transaction_Coordinator

    Thursday, April 23, 2020 4:04 AM
  • User711641945 posted

    Hi born2win,

    .NET Core doesn't support Distributed Transactions because it would require a different transaction manager on each platform. It may appear in the future (here's the issue in-progress)

    Best Regards,

    Rena

    Friday, April 24, 2020 9:10 AM
  • User1489758560 posted

    Thank you rena ni and in that i case if case can i use open connection for multiple db calls in transaction scope? 

    https://docs.microsoft.com/en-us/ef/core/saving/transactions#using-external-dbtransactions-relational-databases-only

    will it work? please suggest me.

    Friday, April 24, 2020 2:27 PM
  • User-474980206 posted

    Rather than using the distributed transaction manager (2 phase commit) you can use sql server builtin batch transaction processes. If you use the same connection you can do a transaction batch which is much more efficient than 2 phase commit. 

    as the example shows if you can use the same connection for all transactions, use BeginTransaction or just execute the command. 

    Note: and again, transaction scope is not supported with thread agile applications like asp.net (core or standard). Asp.net standard may supports it if no async calls are done.

    also you appear to be using all sync database calls, which will kill performance in asp.net core.

    Friday, April 24, 2020 2:43 PM
  • User1489758560 posted

    Thank you Bruce and i will go with native sql transaction as you suggested. also,  from your statement what i understood that all the calls in asp.ent core must be async. please correct me if am wrong. 

    Friday, April 24, 2020 5:09 PM
  • User-474980206 posted

    the asp.net core design counts on several request running on the same thread. the only way this works is to use async i/o. blocking a thread for a database calls (which are really slow >100ms) will really kill performance.

    sql server has always supported transaction batches over a single connection. if you are using ef core. just use the database to begin transaction

      db.Database.BeginTransactionAsync()

    which use underlying database technology rather than the distributed transaction manager.

       https://docs.microsoft.com/en-us/ef/core/saving/transactions

    note: due to its overhead I've never found a case to use DTC. Generally you only need it for multiple database server commits. 

    Friday, April 24, 2020 8:15 PM