search

mkleehammer / pyodbc

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

Access Blocking on Close #816

Closed chancyk closed 3 years ago

chancyk commented 4 years ago

Environment

Issue

Issuing a close() on the connection will sometimes block for 5+ seconds. Inserting a 1 second sleep before the close though seems to circumvent the problem. Could there possibly be some kind of thread contention occurring? I'm not sure about how to proceed with debugging this further, but I'm happy to dig if anyone has suggestions.

v-chojas commented 4 years ago

What is it doing when it's blocked? High CPU usage? Disk activity? Does this happen when connecting and then immediately disconnecting, or is it only after some activity on the connection? If so, does it happen after both read-only and read-write operations? It's hard to say without seeing some more details.

chancyk commented 4 years ago

@v-chojas

No apparent disk usage. CPU sits at about 1-2%. I'm doing database reads only, and in my reproducible case it's after the connection is only open for about 200ms, though I'm not sure it's isolated to those short connection times. It is after executing a query.

The pause is almost exactly 15.15 seconds each time (+/- 0.01 or so). Python just blocks until it eventually goes through. Adding the 1s sleep does seem to reduce the occurrence of the issue by quite a bit though.

I don't have time tonight, but I might see if I can create a reproducible example tomorrow.

gordthompson commented 4 years ago

Other potentially helpful pieces of information:

  1. Is the database file on a local hard drive or a network share?
  2. If it is on a network share are there (potentially) other instances of the Access Database Engine working with the database file at the same time?

There are at least some cases where the Access Database Engine will introduce a delay of about 5 seconds following the execution of a query, but those cases usually involve updates to the database, not simple reads. Details here.

keitherskine commented 3 years ago

Closed due to inactivity. Feel free to re-open with current information if necessary.