search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.92k stars 561 forks source link

Jupyter Notebook Kernel Interruption Never Closed Connection #1090

Closed adrianmonaghan closed 1 year ago

adrianmonaghan commented 2 years ago

Environment

Issue

I made a connection in a jupyter notebook. I ran the pandas function read_sql_query(), passing in the pydobc connection.

The query is a simple SELECT statement with a few joins, however I was missing a WITH (NOLOCK) on one of the tables, causing the table to be locked for the duration of the query. The query takes 2 or 3 minutes to run so it normally isn't an issue. I ran the query but I decided to switch what I was working on so I tried to interrupt the kernel via the jupyter notebook GUI. That was not working, so I went to the command prompt and hit ctrl+c several times until it stopped.

I was informed many hours later that the connection never closed and the table was never unlocked causing significant issues.

I know with certainty that this query works and does return data after a few minutes at most. It is also important to note that I was using no form of context management, and the connection object would never go out of scope and delete within the jupyter notebook.

Is this expected due to my forceful interruption of the kernel? If this isn't a problem what can I do in the future to ensure something like this doesn't happen again?

v-chojas commented 2 years ago

what can I do in the future to ensure something like this doesn't happen again?

Restarting the process will definitely close the connection.

pyODBC itself doesn't do anything with interrupts and the like; this is an application-level responsibility. In any case, pyODBC uses ODBC drivers in the default synchronous mode, and while ODBC does have an async mode, this is not supported by pyODBC nor all ODBC drivers.

adrianmonaghan commented 2 years ago

Restarting the process will definitely close the connection.

I'm sorry I misspoke. I didn't interrupt the kernel. I shut down the kernel. I'm pretty sure you are unable to restart the kernel if it is shutdown.

v-chojas commented 2 years ago

In other words, you restarted the machine and the database connection was still not closed? That does not make sense.

adrianmonaghan commented 2 years ago

I was using a VM so the machine never shut down. I opened a jupyter notebook ran the query. While the query was still running, I went into the command prompt that I opened the jupyter notebook, and interrupted and then shut down the terminal using a keyboard interrupt. It outputted in the terminal "Successfully shut down one kernel". If I am reading the logs correctly, I connected before the jupyter notebook kernel shut down, but it did not disconnect. I am unfortunately unable to do more research into this on my end.