Handle concurrent writes to sqlite db from multiple ansible-playbook runs
Closed this issue · 3 comments
http://www.sqlite.org/faq.html#q5
http://trac.buildbot.net/ticket/2005
If one is using sqlite as the engine for sqlalchemy and have multiple concurrent playbook runs, one may run into a locking problem with sqlite. See the links above for information on how sqlite handles multiple processes trying to write concurrently.
Various notes:
- sqlalchemy uses a built-in sqlite3 driver.
- sqlalchemy sets a default timeout of 30s. It is possible this is used to backoff in case of a SQLITE_BUSY event.
If timeout is not used to handle backoffs or it is insufficient, one possibility is to write to a temporary database that is specific to a playbook run. Then, ansible-report could merge the session db into the main db for processing and reporting. It is possible to do this directly in sqlite with http://www.sqlite.org/lang_attach.html. However, since we use sqlalchemy for database abstraction, the implementation would have to be sqlalchemy friendly.
sqlalchemy session merging: http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#merging
Seems like buildbot has a decent solution: catch the exception OperationalError, sleep for a bit, and try again. If that fails again, back off a bit more and try again.