All Forums Connectivity
tekriewa 8 posts Joined 04/13
16 Jul 2014
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 

NetFx 346 posts Joined 09/06
16 Jul 2014

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.

tekriewa 8 posts Joined 04/13
19 Jul 2014

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?

 

NetFx 346 posts Joined 09/06
21 Jul 2014

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).

tekriewa 8 posts Joined 04/13
21 Jul 2014

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

You must sign in to leave a comment.