Handle concurrent writes to sqlite db from multiple ansible-playbook runs

[sfromm]

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.

[sfromm]

sqlalchemy session merging: http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#merging

[sfromm]

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.

[sfromm]

Based on test results, this should be resolved. Thank you @skvidal.