No transaction rollback when using TransactionScope without Complete()

Connectivity
Enthusiast

No transaction rollback when using TransactionScope without Complete()

Hello,

I have a simple unit test which is failing the final assertion:

        [TestMethod]
public void ExecuteNonQueryWithCommand_ShouldUseTransaction()
{
const string insertString = "insert into Region values (77, 'Elbonia')";
const string countString = "select count(*) from Region";
const string deleteString = "delete from Region where RegionId = 77";

TdConnection cn = new TdConnection(db.ConnectionString);
cn.Open();

TdCommand insertCmd = new TdCommand(insertString, cn);
TdCommand countCmd = new TdCommand(countString, cn);
TdCommand deleteCmd = new TdCommand(deleteString, cn);

int initialRows = (int)countCmd.ExecuteScalar();

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
int rows = insertCmd.ExecuteNonQuery();
Assert.AreEqual(1, rows);
}

int postScopeRows = (int)countCmd.ExecuteScalar();
deleteCmd.ExecuteNonQuery();

cn.Close();
Assert.AreEqual(initialRows, postScopeRows);
}

I was under the impression per this MSDN documentation linked below (see the remarks section) that the transaction should roll back due to the lack of a scope.Complete() statement within the using block.  Does Teradata support this feature?  

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx 

Tags (2)
4 REPLIES
Teradata Employee

Re: No transaction rollback when using TransactionScope without Complete()

The Teradata Database does not support 2PC or Distributed Transactions.

The Data Provider supports local transaction (TdConnection.BeginTransaction, TdTransaction ...). TdTransaction class supports IDisposable and it can be used in a Using-Clause. TdTransaction.Dispose method will automatically rollback an uncommitted transaction.

Enthusiast

Re: No transaction rollback when using TransactionScope without Complete()

1 sec ago

Thanks for the response.  Is it not possible to execute multiple SQL statements within a single command in a single transaction?  For instance, I have the following method:

        private void ExecuteCommand(string sql)
{
using (TdConnection cn = db.CreateConnection() as TdConnection)
{
cn.Open();
using (TdTransaction transaction = cn.BeginTransaction())
{
var cmd = new TdCommand(sql, cn, transaction);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
transaction.Commit();
}
cn.Close();
}
}

The string sql here contains multiple SQL statements (separated by semicolons; I also tried surrounding it with BEGIN/END TRANSACTION for grins).  When I attempt cmd.ExecuteNonQuery(), however,  I get an exception "Only an ET or null statement is legal after a DDL statement."  Is there any way to send multiple SQL statements within a single TdCommand, or are separate TdCommands required for each?




Teradata Employee

Re: No transaction rollback when using TransactionScope without Complete()

Refer to the Teradata Database Messages manual. It states: 

3932 Only an ET or null statement is legal after a DDL Statement.

Explanation: A DDL statement was issued previously. By our current rules,
the only time that a DDL statement can be issued inside an explicit transaction is when
it is the last statement in that Transaction. In an explicit transaction, the only statements
allowed after a DDL statement are an END TRANSACTION , an unconditional ABORT, or a NULL statement.

Does the Multi-Statement SQL contain a DDL statement? if so, I suggest separating the DML statements from the DDL statement(s).

Enthusiast

Re: No transaction rollback when using TransactionScope without Complete()

Yes, that was the problem - I was attempting to issue multiple DDL statements simultaneously.  Thanks for the clarification.