🚨 When autocommit = false Caused Hidden Table Locks in SQL Server (pymssql + AWS Batch)

While moving a workload from server to AWS Batch (Python), we hit a strange issue:

• Some SQL jobs started taking 2x longer
• Parallel jobs were getting blocked / waiting
• No code changes in SQL

After debugging, the root cause turned out to be:
👉 autocommit = false in pymssql

🧩 The Setup

From Python, we were executing a group of SQL statements in one go (sample):

DECLARE @jobId INT;

EXEC @jobId = dbo.ProcessJob @input = 123;

IF (@jobId > 0)
    PRINT 'Job processed';
ELSE
    PRINT 'Job failed';

Inside the stored procedure (sample):

CREATE PROCEDURE dbo.ProcessJob
    @input INT
AS
BEGIN
    BEGIN TRAN;

    INSERT INTO Jobs (input) VALUES (@input);

    UPDATE JobStatus
    SET status = 'PROCESSING'
    WHERE job_id = @input;

    SELECT * FROM Jobs WHERE input = @input;

    COMMIT TRAN;

    RETURN @input;
END

So overall:

• Python → executes batch SQL
• Stored Procedure → has its own transaction

⚠️ The Problem

Even though the stored procedure had COMMIT, we saw:

• Other queries getting blocked
• Jobs stuck in waiting state
• Execution time increasing

🔍 What Was Actually Happening

Python connection was:

conn = pymssql.connect(..., autocommit=False)
#This created a parent transaction.

🔥 Locking Flow

• Python opens connection → starts transaction
• Stored procedure runs:
  • BEGIN TRAN → does INSERT/UPDATE
  • COMMIT → completes inner transaction
  • But outer transaction (Python) is still open

Result:

• SQL Server does NOT release locks
• Rows/tables remain locked until Python commits
• Other queries trying to access same tables → WAIT

📌 Example Lock Scenario
Job 1 (Running)

UPDATE JobStatus SET status = 'PROCESSING' WHERE job_id = 123;

➡️ Lock acquired on JobStatus table

Job 2 (Parallel)

UPDATE JobStatus SET status = 'DONE' WHERE job_id = 123;

⛔ Blocked because:

Job 1 still holds lock (due to outer transaction)

👉 Even though SP committed, lock is held by parent transaction

🛠️ The Fix

Changed to:

conn = pymssql.connect(..., autocommit=True)

What changed?

• No parent transaction from Python
• Stored procedure controls its own transactions
• Locks released immediately after COMMIT

✅ Result

• Execution time back to normal
• No blocking between jobs
• Smooth parallel execution

🧠 Key Takeaway
-Even if your stored procedure commits,
locks won’t release if there’s an outer transaction still open.

🎯 When This Can Happen

• Running parallel database operations
• Using pymssql / drivers with autocommit off
• Working with transaction-heavy stored procedures

🏁 Final Thought

This issue wasn’t about query complexity — it was about transaction boundaries.

A small config like autocommit can silently:

• Increase execution time
• Cause blocking
• Impact system performance

— Lokesh Vangari