Table get locked when called an SQL Server SP from pyodbc Python

Question

I have a table I made for testing called test. I just want to see if my code works. My python code is very simple and only calls one SP

Here is my SQL Server SP script:

ALTER PROCEDURE [dbo].[TestService]   
AS
BEGIN
    SET NOCOUNT ON;

    declare @C1 int
    
    BEGIN TRANSACTION;   
        insert into Test (a1) values ('Service')
    COMMIT TRANSACTION; 

    SELECT @C1 = COUNT(*) FROM test (nolock)    

    SELECT GETDATE(), @C1 as t
END

and my python code is this:

import pyodbc 
import pandas as pd

#df_results.drop(axis=0, inplace=True)    

ConnectionString = "DRIVER={SQL Server};Server=Serv;Database=DB;User Id=user;Password=*****;"

conn = pyodbc.connect(ConnectionString)
df_results = pd.read_sql("EXEC TestService" , conn)
print(df_results)

Before running the python code I ran this select

SELECT * FROM Test (NoLock)

and the output was 4 records

I ran the python script and I got this output

0 2021-12-19 00:09:36.887  5

which means record been inserted and total number of records is 5

but when I run the

SELECT * FROM Test (NoLock)

I still get 4 records only

and when I try

SELECT * FROM Test

I get timed out.

How to fix that?

How does you can output have a "0" at the start? The code shown doesn't produce a spare 0? — Dale K, Dec 19 '21 at 05:37
@MitchWheat the Transaction was an attempt to find the cause of the lock. but it is not really needed — asmgx, Dec 19 '21 at 05:49
I'd question why you are using read_sql() to execute a stored proc! "Read SQL query or database table into a DataFrame." - https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html — Mitch Wheat, Dec 19 '21 at 07:22
Does this answer your question? [In Python, Using pyodbc, How Do You Perform Transactions?](https://stackoverflow.com/questions/1063770/in-python-using-pyodbc-how-do-you-perform-transactions) Switch `autocommit` to `true` — Charlieface, Dec 19 '21 at 11:04

score 1 · Accepted Answer · edited Dec 19 '21 at 08:44

1

read_sql won't commit the transaction. You need to explicitly commit it.

conn = pyodbc.connect(ConnectionString)
df_results = pd.read_sql("EXEC TestService" , conn)
print(df_results)
conn.commit()
conn.close()

edited Dec 19 '21 at 08:44

Dale K

25,246
15
42
71

answered Dec 19 '21 at 05:58

GoonerForLife

631
2
5

Table get locked when called an SQL Server SP from pyodbc Python

1 Answers1