Common Transaction Pitfalls After Migrating from Oracle to SQL Server

When applications move from Oracle to SQL Server, it’s common to see unexpected blocking and performance regressions. Most issues trace back to transaction handling differences and assumptions carried over from Oracle.

At a glance

• Oracle’s MVCC design lets readers and writers proceed without blocking each other.
• SQL Server’s default is autocommit ON under READ COMMITTED isolation, implemented with locks (unless you enable row versioning).
• Teams sometimes try to mimic Oracle by disabling autocommit or enabling IMPLICIT_TRANSACTIONS, which can lead to long‑lived transactions that hold locks, causing blocking, deadlocks, and log growth.

Important: Always validate Oracle behaviors and environment‑specific defaults in official docs during migration planning. Oracle’s concurrency model is MVCC, but autocommit is a client/driver setting (see note below).

---

Why the mismatch happens

Oracle (conceptual defaults): MVCC with READ COMMITTED isolation; readers don’t block writers and vice versa, thanks to undo/consistent read.

Note on autocommit: Oracle Database itself doesn’t have a server‑wide “autocommit default.” Tools like SQL*Plus default to autocommit OFF, while JDBC connections default to autocommit ON unless changed by the app. Don’t assume a single universal setting.

SQL Server defaults: Autocommit ON, IMPLICIT_TRANSACTIONS OFF, isolation level READ COMMITTED implemented with locking—so readers and writers can block each other.

Common migration misstep: Apps ported from Oracle sometimes turn on IMPLICIT_TRANSACTIONS or disable autocommit to “feel Oracle‑like.” On SQL Server, that choice often yields open transactions for an entire session, which hold locks and block others.

---

Why SQL Server’s defaults are sensible (for SQL Server)

• Autocommit ON: Avoids forgotten, long‑running transactions that hold locks.
• READ COMMITTED: Reasonable integrity (no dirty reads) with minimal overhead; switch to row‑versioning for higher concurrency.
• IMPLICIT_TRANSACTIONS OFF: Prevents accidental long‑lived transactions.

---

A pragmatic SQL Server configuration for “Oracle‑like” concurrency

1. Keep defaults for most workloads:
   • autocommit = ON
   • IMPLICIT_TRANSACTIONS = OFF
   • Default isolation = READ COMMITTED (locking by default)
2. Enable READ_COMMITTED_SNAPSHOT (RCSI) to make READ COMMITTED use row versioning instead of reader/writer locks:

   ALTER DATABASE YourDB 
     SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

3. Optionally allow SNAPSHOT isolation for explicit use where repeatable, statement‑stable reads are required:

   ALTER DATABASE YourDB 
     SET ALLOW_SNAPSHOT_ISOLATION ON;

   Caution: Under SNAPSHOT, update conflicts (Msg 3960) can occur if two transactions try to update the same row (writers still block writers).

4. Educate developers: Keep transactions short; always COMMIT or ROLLBACK promptly.

---

Where row versions live (and what to monitor)

• If you enable RCSI or SNAPSHOT, SQL Server uses row versioning.
• Without ADR (pre‑SQL Server 2019 or ADR OFF): versions live in tempdb.
• With ADR enabled (SQL Server 2019+ or Azure SQL Database): row versions live in the per‑database Persistent Version Store (PVS).

What to monitor

• tempdb version store usage (when ADR is off):

  SELECT * FROM sys.dm_tran_version_store_space_usage;

• PVS (when ADR is on):

  SELECT * FROM sys.dm_tran_persistent_version_store_stats;

• Long‑running transactions (they delay version cleanup and cause growth):

  SELECT * FROM sys.dm_tran_active_snapshot_database_transactions;

Note: In Azure SQL Database, RCSI is enabled by default and ADR is always on—plan capacity accordingly.

---

Oracle vs. SQL Server defaults (and SQL Server + RCSI)

Feature	Oracle (typical)	SQL Server (default)	SQL Server with RCSI
Autocommit default	Client/tool specific (e.g., SQL*Plus OFF; JDBC ON)	ON	ON
Default isolation	READ COMMITTED (MVCC)	READ COMMITTED (locking)	READ COMMITTED (row‑versioning)
Concurrency model	MVCC	Lock‑based	Row versioning
Readers vs. writers	No blocking	Can block	No blocking
Row‑version storage	Undo segments	N/A (locking)	tempdb (no ADR) or PVS (ADR)

---

Detecting “implicit transactions” problems after migration

Find sessions with open implicit transactions

;WITH implicit_tx AS
(
  SELECT
      at.transaction_id,
      at.transaction_begin_time,
      st.session_id
  FROM sys.dm_tran_active_transactions AS at
  JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = at.transaction_id
  WHERE at.name = N'implicit_transaction'
)
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.open_transaction_count,
    x.transaction_begin_time,
    DATEDIFF(SECOND, x.transaction_begin_time, SYSUTCDATETIME()) AS open_seconds
FROM implicit_tx AS x
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = x.session_id
ORDER BY open_seconds DESC;

Flag sleeping sessions with open transactions

SELECT
  s.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  s.status,
  s.open_transaction_count
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
  AND s.status = 'sleeping'
  AND s.open_transaction_count > 0;

Capture who’s enabling implicit transactions (Extended Events)

Create a lightweight XE session to capture SET options on connect and reuse:

CREATE EVENT SESSION [CaptureImplicitTxns] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text = (1)),
ADD EVENT sqlserver.existing_connection(SET collect_options_text = (1))
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION [CaptureImplicitTxns] ON SERVER STATE = START;

Then filter results for options_text containing implicit_transactions on. For ongoing analysis, prefer an event_file target instead of ring_buffer on busy servers.

---

Optimizing and monitoring tempdb / PVS

• tempdb layout (if ADR is off or for other versioning features): Use multiple equally sized data files to reduce allocation contention. A good starting point is 1 file per logical CPU up to 8, then grow by 4 at a time only if contention persists.
• Instant File Initialization (IFI) for faster growth of data files, including tempdb data files (does not apply to the log): enable Windows “Perform volume maintenance tasks” for the SQL Server service account.
• Watch version store growth continuously (DMVs above). Long‑running transactions delay cleanup and can fill tempdb or the PVS.

---

Quick commands & checks

Enable row‑versioning behaviors

-- Make READ COMMITTED use row versioning:
ALTER DATABASE YourDB 
  SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Allow explicit SNAPSHOT isolation:
ALTER DATABASE YourDB 
  SET ALLOW_SNAPSHOT_ISOLATION ON;

Confirm settings / environment

-- Per-database snapshot/RCSI switches
SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'YourDB';

-- ADR / PVS signal
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'YourDB';

---

DBA checklist (post‑migration)

• ✅ Decide on RCSI for migrated databases; test critical read/write paths before enabling in production.
• ✅ Validate tempdb:
  • Multiple equally sized data files (start with cores ≤ 8 → that many files; > 8 → start with 8).
  • IFI enabled for data files.
• ✅ If ADR is enabled, monitor PVS with sys.dm_tran_persistent_version_store_stats.
• ✅ Monitor version store usage in tempdb with sys.dm_tran_version_store_space_usage.
• ✅ Detect long‑running transactions: sys.dm_tran_active_snapshot_database_transactions.
• ✅ Audit implicit transactions with DMVs/XE as shown above.
• ✅ Coach developers to avoid long‑lived transactions and always COMMIT/ROLLBACK promptly.

---

Conclusion

Migrating from Oracle to SQL Server isn’t just a connection‑string change; it’s a shift in concurrency and transaction semantics. Oracle’s MVCC generally prevents reader/writer blocking. SQL Server’s defaults are different and, if you try to emulate Oracle by disabling autocommit or enabling implicit transactions, you’re likely to run into blocking and scalability issues.

The pragmatic approach on SQL Server is to keep the defaults, enable RCSI for Oracle‑like read behavior where appropriate, and monitor version store usage (in tempdb or PVS when ADR is on). With the right configuration and developer practices, you can achieve high concurrency and predictable performance on SQL Server.

---

Sources & further reading

• Oracle Database Concepts: Data Concurrency and Consistency; Read Consistency; Isolation Levels (default READ COMMITTED).
• Microsoft Docs – SQL Server: Transaction Isolation Levels; Read Committed Snapshot (RCSI); Snapshot Isolation; Row Versioning behavior.
• Accelerated Database Recovery (ADR) and Persistent Version Store (PVS): Concepts, behavior, and monitoring DMVs.
• DMVs: sys.dm_tran_version_store_space_usage, sys.dm_tran_persistent_version_store_stats, sys.dm_tran_active_snapshot_database_transactions.
• Extended Events: sqlserver.login / sqlserver.existing_connection with collect_options_text; inspect options_text to compare SET options.
• tempdb guidance: Number of data files (start up to 8 and scale as needed), instant file initialization (IFI) for data files.
• Azure SQL Database: RCSI default ON; ADR always on.