adjust sqlite settings

[lyuboxa]

Since the addition of WAL, sqlite can handle multiple connections however this can often spur database locked errors. As part of the https://github.com/ConduitIO/conduit project we ran some tests and found that tx locking is more tolerable when transactions are not deferred, which is the default for sqlite thus setting busy_timeout=N[1] and _txlock=immediate[2] allows for concurrent access.

By allowing the tx lock to take place immediately, this allows for a form of serialization of writes in the span of the busy timeout, until when the error will return. In comparison a deferred tx will not trigger a lock until it reaches a statement which requires it, but by that time there may be a non-reconcilable tx in progress or data change and no retry is possible.

1. https://www.sqlite.org/pragma.html#pragma_busy_timeout
2. See https://pkg.go.dev/modernc.org/sqlite#Driver.Open, https://www.sqlite.org/lang_transaction.html