Locust tasks block when executing queries with pyodbc, but not in ThreadPool

[aparnansu]

Prerequisites

• I am using the latest version of Locust
• I am reporting a bug, not asking a question

Description

Issue Description

When using the Python pyodbc library to execute queries within Locust tasks, the tasks seem to block or get stuck, preventing other tasks from executing concurrently. However, the same code works as expected when executed within a ThreadPool using concurrent.futures.

Steps to Reproduce

1. Run the Locust file (db_locust.py) with the command: locust -f db_locust.py --headless -u10 -r10 --host=localhost

Expected behavior: Multiple Locust users should be able to execute the queries concurrently without blocking.

Actual behavior: Only one Locust user can execute the query at a time, and other users get stuck or blocked until the previous user completes the query.

Code Examples

The issue description includes two code examples:

1. A Locust file (db_locust.py) that demonstrates the blocking behavior when using pyodbc within Locust tasks. This file also sets up the required database table and data.
2. A separate Python file that executes the same queries using a ThreadPool from concurrent.futures, and this code works as expected without any blocking issues. (Not provided in the issue but I can share this code that's working).

Environment

• Python version: Latest
• Locust version: Latest
• pyodbc version: Latest
• Database: SQL Server or PostgreSQL (Latest)
• Operating System: Windows and Linux (issue is operating system agnostic)

Log Output

Actual

[...]: Loaded data to table SampleTable_1711171627
[...]: Ramping to 10 users at a rate of 10.00 per second
[...]: All users spawned: {"TestUser": 10} (10 total users)
[...]: User 1 started
[...]: User 1 connection established
[...]: User 1 Executing Query select * from SampleTable_1711171627
[...]: User 1 completed Query
[...]: User 2 started
[...]: User 2 connection established
[...]: User 2 Executing Query select * from SampleTable_1711171627
[...]: User 2 completed Query

Expected

[...] User 1 started
[...] User 2 started
[...] User 3 started
[...] User 1 connection established 
[...] User 2 connection established
[...] User 3 connection established
[...] User 1 Executing Query select * from SampleTable_1711171627
[...] User 2 Executing Query select * from SampleTable_1711171627  
[...] User 3 Executing Query select * from SampleTable_1711171627
[...] User 1 completed Query
[...] User 2 completed Query
[...] User 3 completed Query

Additional Information

The issue seems to be specific to the combination of Locust and pyodbc, as the code works as expected when executed within a ThreadPool. The blocking behavior occurs with both SQL Server and PostgreSQL databases, suggesting that the issue is not database-specific.

The blocking behavior occurs when executing queries with pyodbc within Locust tasks, but it does not occur when using time.sleep() instead of conn.execute().

Any assistance or insights into this issue would be greatly appreciated.

Command line

locust -f db_locust.py --headless -u10 -r10 --host=localhost

Locustfile contents

import logging
import random
import sys
import time

import pyodbc
from gevent import killall
from locust import task, User, TaskSet, events

# SQL Server Connection Details
server = 'localhost'
database = 'test'
username = 'test'
password = 'test'
driver = 'ODBC Driver 17 for SQL Server'
connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"

count = 0
running_greenlets = set()
stop_event = None

table_name = f"SampleTable_{int(time.time())}"


class SampleTaskSet(TaskSet):

    def __init__(self, parent: User):
        super().__init__(parent)
        global count
        count = count + 1
        self.counter = count

    @task
    def execute_query(self):
        global stop_event
        if stop_event and stop_event.is_set():
            logging.info("Stop event set. Exiting task.")
            return

        logging.info("User {} started".format(self.counter))
        global table_name
        query = "select * from {}".format(table_name)
        conn = pyodbc.connect(driver=driver, host=server, Database=database, UID=username,
                              PWD=password, Trusted_Connection='no')
        logging.info("User {} connection established".format(self.counter))
        cursor = conn.cursor()
        try:
            logging.info("User %s Executing Query %s ", self.counter, query)

            # Doesn't block task
            # sleep_in_secs = random.randint(5, 10)
            # time.sleep(sleep_in_secs)

            # Blocks task
            cursor.execute(query)
            rows = cursor.fetchall()
            resp_len = len(rows)
            logging.info("User %s completed Query", self.counter)

        except KeyboardInterrupt:
            logging.info("KeyboardInterrupt caught in task")
            if stop_event:
                stop_event.set()
            raise

        finally:
            cursor.close()
            conn.close()


class TestUser(User):
    tasks = [SampleTaskSet]


def on_locust_stop(*args, **kwargs):
    logging.info("Stopping Locust due to signal")
    killall(running_greenlets)
    sys.exit()


@events.test_start.add_listener
def prep_data(environment, **kwargs):
    global table_name

    conn = pyodbc.connect(driver=driver, host=server, Database=database, UID=username, PWD=password,
                          Trusted_Connection='no', autocommit=True)
    cursor = conn.cursor()

    logging.info("Creating table %s", table_name)
    try:
        create_table_query = f"CREATE TABLE {table_name} (id INT, name VARCHAR(255), email VARCHAR(255))"
        cursor.execute(create_table_query)
        logging.info("Created table %s", table_name)
        logging.info("Loading data to table %s", table_name)

        for i in range(100_000):
            insert_query = f"INSERT INTO {table_name} (id, name, email) VALUES (?, ?, ?)"
            cursor.execute(insert_query, (i + 1, f"Name {i + 1}", f"email_{i + 1}@example.com"))
        logging.info("Loaded data to table %s", table_name)

    finally:
        cursor.close()
        conn.close()

Python version

3.12.2

Locust version

2.24.1

Operating system

Windows

[cyberw]

Hi! Sorry for not responding faster. I'm not sure pyodbc works with gevent?

[github-actions]

This issue was closed because it has been stalled for 10 days with no activity. This does not necessarily mean that the issue is bad, but it most likely means that nobody is willing to take the time to fix it. If you have found Locust useful, then consider contributing a fix yourself!