Python queries are getting gridlocked on SQL Server

Question

I'm trying to automate a large database process that is run at the beginning of every month. This is done, currently, with a bunch of stored procedures that are already in the database. My task is currently to get these stored procedures to run via Python's pyodbc.

The tricky part about these stored procedures is I need to run them one-at-a-time so they don't hog all of the database's resources. So, to check to see if the stored procedures have been run I created a table that is updated, changing a flag called "IsFinished" to False at the beginning of the procedure to True at the end.

The problem occurs when I try to query the database again. The activity monitor in SQL server is showing that my query against the Tracking table as SUSPENDED and blocked by the stored procedure call.

I've even gone so far as to create a new database so that I can call both of the databases independently just to further make sure I'm not competing for resources... but I still am competing for resources.

I'm using Python 3.6.4 and the pyodbc module. Here's my code. It should be noted that connect_db_1() and connect_db_2() point to two separate databases.

conn = connect_db_1()

conn.execute("{call dbo.StoredProcedure}")

conn2 = connect_db_2()

start_time = time.time()

finished = False

while finished is False:
    print("Looping")
    time.sleep(120)
    cursor = conn2.execute(
        "SELECT IsFinished from dbo.Tracking where StoredProcedureName='dbo.StoredProcedure'")
    results = cursor.fetchall()
    if results[0][0] == True:
        finished = True
    print(results)
print(f"dbo.StoredProcedure finished in {time_diff(start_time)} seconds.")
cursor.close()

EDIT: Added a sanitized version of my code.

Can you show some of your python code? It may be possible that you are locking connection to DB from python end. — Anil_M
– Anil_M, Commented Jan 18, 2018 at 21:45
@Anil_M just added my code - only changes are in table names and try/excepts have been removed. — dmcoding
– dmcoding, Commented Jan 18, 2018 at 21:56
There is a bunch of file manipulation that needs to happen before the scripts are run. The file manipulation also needs to be automated, hence the Python. — dmcoding
– dmcoding, Commented Jan 19, 2018 at 20:54

Ctznkane525 · Accepted Answer · 2018-01-19 00:16:45Z

1

You want to close your cursor inside the loop since you are creating new cursors in the loop.

Basically, you were opening a cursor over and over again without closing.

while finished is False:
    print("Looping")
    time.sleep(120)
    cursor = conn2.execute(
        "SELECT IsFinished from dbo.Tracking where StoredProcedureName='dbo.StoredProcedure'")
    results = cursor.fetchall()
    if results[0][0] == True:
        finished = True
    print(results)
    cursor.close()
print(f"dbo.StoredProcedure finished in {time_diff(start_time)} seconds.")

answered Jan 19, 2018 at 0:16

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dmcoding Over a year ago

This is a good point, but it should be pointed out that the cursor isn't executing once. It's definitely a fix I need to put into the code, but that isn't addressing the problem of gridlock in my database. Gridlock occurs at the first conn2.execute()

Collectives™ on Stack Overflow

Python queries are getting gridlocked on SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related