mercredi 20 mai 2015

SQLite queries freeze at times

During some runs of my Python script, SQLite queries that usually take on the order of 0.1 seconds take up to half-a-minute. I am seeking help in understanding why this happens and how to fix this situation.

My script spawns a number of threads using the threading module. Each thread performs a computation and stores the results of that computation in an SQLite table (re-creating the table if necessary). Each thread creates its own connection to the database. Since all threads write to the same table, race conditions are handled by locking provided by the threading module. Basically, each thread performs:

global myLock
... # performs computation
myLock.acquire()
... # creates connection and stores the results in the database
myLock.release()

Here is part of the script's output related to locking and database operations:

2015-05-20 18:02:35.388868 Thread-3: waiting for lock
2015-05-20 18:02:35.388923 Thread-3: got the lock!
2015-05-20 18:02:35.389040 Thread-3: DROP TABLE
2015-05-20 18:02:38.002893 Thread-8: waiting for lock
2015-05-20 18:02:57.269938 Thread-9: waiting for lock
2015-05-20 18:02:59.167253 Thread-7: waiting for lock
2015-05-20 18:02:59.638832 Thread-5: waiting for lock
2015-05-20 18:02:59.717258 Thread-3: COMMIT

Thread-3 took 24 seconds to drop a table consisting of 10 rows and 11 columns. Here is the output of another run of the same script:

2015-05-20 18:44:21.053596 Thread-3: waiting for lock
2015-05-20 18:44:21.053672 Thread-3: got the lock!
2015-05-20 18:44:21.053803 Thread-3: DROP TABLE
2015-05-20 18:44:21.132392 Thread-3: COMMIT

Here dropping the table took 0.08 seconds. Not sure if this is related, but the script is run with sudo privileges. Please let me know if there is more relevant information that I can provide.

Aucun commentaire:

Enregistrer un commentaire