DEV Community

Lokesh Vangari
Lokesh Vangari

Posted on

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

#sql #pymssql

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';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

🔥 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;
Enter fullscreen mode Exit fullscreen mode

➡️ Lock acquired on JobStatus table

Job 2 (Parallel)

UPDATE JobStatus SET status = 'DONE' WHERE job_id = 123;
Enter fullscreen mode Exit fullscreen mode

⛔ 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)
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)