1

I am trying to execute stored procedure from data bricks by using PYODBC connection, after all transactions happened, status is going to sleep mode. Please help me on that, I tried all the possibilities auto-commit and connection timeout etc. but nothing is working.

import pyodbc
import datetime
username = "usrname"
password = "password"
server = "server"
database_name = "dbname"
port = "1433"
conn=pyodbc.connect('Driver={ODBC Driver 17 for SQL server};SERVER=tcp:'+server+','+port+';DATABASE='+ database_name +';UID='+ username +';PWD='+ password)
#conn.timeout = 600
cursor=conn.cursor()
# conn.autocommit = True

sql = "set nocount on; exec proc_name"
print("Connection Started at "+str(datetime.datetime.now()))
cursor.execute(sql)
print("Connection closed at "+str(datetime.datetime.now()))
conn.commit()
cursor.close()
conn.close()

print(datetime.datetime.now())

Notebook is still in Running process please have a look on below pic

enter image description here

Status from database for that SPID initially status is RUNNABLE once all transaction got completed from proc(insertion, delete, update of data) status is updating as sleeping mode enter image description here

enter image description here

because of this sleeping status data bricks note book is not completing, it's keep on running. please help me out from issue. Thanks in Advance.

Suneel Kumar
  • 127
  • 1
  • 2
  • 8

1 Answers1

0

Blocking caused by a sleeping SPID that has an uncommitted transaction:

This type of blocking can often be identified by a SPID that is sleeping or awaiting a command, yet whose transaction nesting level (@@TRANCOUNT, open_transaction_count from sys.dm_exec_requests) is greater than zero.

This can occur if the application experiences a query timeout, or issues a cancel without also issuing the required number of ROLLBACK and/or COMMIT statements. When a SPID receives a query timeout or a cancel, it will terminate the current query and batch, but does not automatically roll back or commit the transaction.

The application is responsible for this, as SQL Server cannot assume that an entire transaction must be rolled back due to a single query being canceled. The query timeout or cancel will appear as an ATTENTION signal event for the SPID in the Extended Event session.

Refer - https://learn.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking#detailed-blocking-scenarios

Abhishek K
  • 3,047
  • 1
  • 6
  • 19
  • I'm using pyodbc that gets this "attention" packet without raising any exception whatsoever. Why would pyodbc do that? I use no query timeout – Eytan Naim Jan 04 '23 at 08:26