Question
VB.NET ADO.NET Transactions and Cross-Database DDL Rollback Consistency
Rahul River
0 reputation · 07 Apr 2023, 01:50 UTC
42.2K views1
Loading…
0 reputation · 07 Apr 2023, 01:50 UTC
VB.NET applications using System.Transactions.TransactionScope or explicit DbTransaction objects rely on ADO.NET's common abstraction layer to coordinate work across SQL Server, PostgreSQL, and Oracle. The abstraction promises a single Rollback() call that undoes all enlisted operations, but the underlying providers diverge sharply when DDL statements such as ALTER TABLE or CREATE INDEX are mixed with DML in the same logical unit of work.
The goal is to design a deployment pipeline that can safely revert both schema changes and data mutations together when any step fails, without maintaining separate rollback scripts per database. The constraint is that SQL Server and PostgreSQL support transactional DDL for most commands, while Oracle issues an implicit COMMIT before and after every DDL statement, making true atomic rollback impossible on that platform. EF Core migrations already exhibit this split behavior, yet no built-in VB.NET mechanism detects or enforces the separation at runtime.
TransactionScope be configured to throw or escalate when it detects a provider that will silently commit DDL, rather than proceeding with a false sense of atomicity?31775 reputation · 07 Apr 2023, 04:54 UTC
There is no provider‑agnostic VB.NET pattern that can guarantee true all‑or‑nothing semantics when DDL statements are mixed with DML across SQL Server, PostgreSQL, and Oracle together. Oracle implicitly commits before and after every DDL statement, so a TransactionScope (or explicit DbTransaction) cannot roll back those changes. The scope will still complete, but Oracle will have already persisted the DDL, breaking atomicity.
visualbasicSystem.Transactions.TransactionScope automatically promotes to a distributed transaction (MSDTC) when two or more durable connections are enlisted, giving atomic commit/rollback for databases that support transactional DDL (SQL Server, PostgreSQL).The ADO.NET abstraction cannot override the database engine’s commit behavior. When Oracle is enlisted, the transaction is still promoted to MSDTC, but Oracle’s internal commit occurs before the distributed transaction’s prepare phase, so the prepare/commit never sees the DDL as mutable.
TransactionScope. Run Oracle DDL in its own step (or in a dedicated Oracle‑only transaction).TransactionScope block, enlist only SQL Server and PostgreSQL connections. Execute all DDL/DML for those platforms; call ts.Complete() only after every command succeeds.TransactionScope rollback will undo their changes automatically.TransactionScope:
Using ts As New TransactionScope()
Using cn As DbConnection = factory.CreateConnection()
If cn.GetType().Name.Contains("Oracle") Then
Throw New InvalidOperationException("Oracle DDL cannot be enlisted in a distributed transaction.")
End If
' … open, enlist, execute …
End Using
ts.Complete()
End UsingCan Oracle DDL be moved out of the same logical unit of work (i.e., executed as a separate step) in your pipeline? If the answer is “no,” you must rely on Oracle‑specific compensation mechanisms; if “yes,” the pattern above provides a practical all‑or‑nothing solution for the other two databases.
Use comments to ask for clarification. Post a solution as an answer.
31,775 reputation · 07 Apr 2023, 13:10 UTC
The existing answer correctly identifies runtime provider detection as the practical gate. For VB.NET code, the invariant names to check are:
Oracle.ManagedDataAccess.Client (ODP.NET Managed)Oracle.DataAccess.Client (legacy unmanaged)Npgsql (PostgreSQL)System.Data.SqlClient / Microsoft.Data.SqlClient (SQL Server)Private Sub AssertDdlAllowed(conn As DbConnection)
Dim inv = conn.ProviderInvariantName
If inv IsNot Nothing AndAlso (inv.Contains("Oracle") OrElse inv.Contains("Oracle.DataAccess")) Then
Throw New InvalidOperationException("DDL not permitted inside TransactionScope for Oracle provider: " & inv)
End If
End SubCall this before any ALTER TABLE, CREATE INDEX, or similar command. It fails fast, preventing the implicit commit that would otherwise leave schema changes stranded after a reported rollback.
For compile-time safety, a Roslyn analyzer can flag ExecuteNonQuery calls containing DDL keywords when the connection variable's type resolves to an Oracle provider namespace, catching the issue before deployment.