Multiple connections to SQLite DB

[sameersubudhi]

Actual behavior

Multiple DB connections to SQLite DB in blockchain-connector results in database is locked error.

2023-09-27T10:00:11.751 INFO [allEvents] Received chain_newBlock event, dispatching chainNewBlock signal.
2023-09-27T10:00:28.300 INFO [allEvents] Received chain_newBlock event, dispatching chainNewBlock signal.
2023-09-27T10:00:29.495 WARN [BROKER] Request 'connector.getAllPosValidators' is timed out.,[object Object]
2023-09-27T10:00:48.883 ERROR [util] Transaction query already complete, run with DEBUG=knex:tx for more info
2023-09-27T10:00:48.884 ERROR [util] select `block` from `blocks` where `id` = 'cfdb73b66dc262f8308eee66985c3e5d0f2293b69df5a4af14bf7f55808aee30' limit 1 - database is locked
2023-09-27T10:00:48.884 ERROR [util] insert into `blocks` (`block`, `id`, `timestamp`) values ('{"header":{"version":2,"timestamp":1695736163,"height":20449469,"previousBlockID":"49c895413d70761acd72f3bc34937cda8d68bfccf1d8712ab3dd390fccabde4a","stateRoot":"22d7c1fe1af14cc142422bdd7177d10a4ca96c1bf931da2ae4bc14c62de6fc2e","assetRoot":"91ca7170e168f03d369b2e0685d8f44cf88a95c0ba8383cf9d89f5a95aaae5f1","eventRoot":"46d2b6211e9a517107454119a74c0b322c6d1d0f24d7e31a8794cf3d2b92ba04","transactionRoot":"e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855","validatorsHash":"871a2ab9aa6677e6995cc3dd9d6a0652f30640cdc2d098d26a596df1b77054da","aggregateCommit":{"height":20449415,"aggregationBits":"","certificateSignature":""},"generatorAddress":"lsk9ywe4geqd8wkhnewwmd9tmb7rtznkoa9qquykz","maxHeightPrevoted":20449415,"maxHeightGenerated":20449452,"impliesMaxPrevotes":true,"signature":"432103625a3ad120eb7e09e8cb08dcde55b1094d73e85cb695a7df40cde0410a79467873c20b8950e90a8e5174f2fa486cf969a15e8bbfab578e116c1c1c2908","id":"5941c7d2b148570925dd61f1d22252db354aa1daf485894a0ea2fb9c7ebe6ea0"},"transactions":[],"assets":[{"module":"random","data":"0a105ccd26527cf4b13d4110034ed446b906"}]}', '5941c7d2b148570925dd61f1d22252db354aa1daf485894a0ea2fb9c7ebe6ea0', 1695736163) on conflict (`id`) do update set `block` = excluded.`block`, `id` = excluded.`id`, `timestamp` = excluded.`timestamp` - database is locked

/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/dialects/better-sqlite3/index.js:44
    const response = await statement.run(bindings);
                                     ^
SqliteError: insert into `blocks` (`block`, `id`, `timestamp`) values ('{"header":{"version":2,"timestamp":1695736163,"height":20449469,"previousBlockID":"49c895413d70761acd72f3bc34937cda8d68bfccf1d8712ab3dd390fccabde4a","stateRoot":"22d7c1fe1af14cc142422bdd7177d10a4ca96c1bf931da2ae4bc14c62de6fc2e","assetRoot":"91ca7170e168f03d369b2e0685d8f44cf88a95c0ba8383cf9d89f5a95aaae5f1","eventRoot":"46d2b6211e9a517107454119a74c0b322c6d1d0f24d7e31a8794cf3d2b92ba04","transactionRoot":"e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855","validatorsHash":"871a2ab9aa6677e6995cc3dd9d6a0652f30640cdc2d098d26a596df1b77054da","aggregateCommit":{"height":20449415,"aggregationBits":"","certificateSignature":""},"generatorAddress":"lsk9ywe4geqd8wkhnewwmd9tmb7rtznkoa9qquykz","maxHeightPrevoted":20449415,"maxHeightGenerated":20449452,"impliesMaxPrevotes":true,"signature":"432103625a3ad120eb7e09e8cb08dcde55b1094d73e85cb695a7df40cde0410a79467873c20b8950e90a8e5174f2fa486cf969a15e8bbfab578e116c1c1c2908","id":"5941c7d2b148570925dd61f1d22252db354aa1daf485894a0ea2fb9c7ebe6ea0"},"transactions":[],"assets":[{"module":"random","data":"0a105ccd26527cf4b13d4110034ed446b906"}]}', '5941c7d2b148570925dd61f1d22252db354aa1daf485894a0ea2fb9c7ebe6ea0', 1695736163) on conflict (`id`) do update set `block` = excluded.`block`, `id` = excluded.`id`, `timestamp` = excluded.`timestamp` - database is locked
    at Client_BetterSQLite3._query (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/dialects/better-sqlite3/index.js:44:38)
    at executeQuery (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_BetterSQLite3.query (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/client.js:154:12)
    at /home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/transaction.js:371:24
    at new Promise (<anonymous>)
    at trxClient.query (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/transaction.js:366:12)
    at Runner.query (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/runner.js:141:36)
    at ensureConnectionCallback (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
    at Runner.ensureConnection (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/runner.js:318:20)
    at runNextTicks (node:internal/process/task_queues:60:5)
    at process.processImmediate (node:internal/timers:447:9)
    at async Runner.run (/home/lisk/lisk-service/blockchain-connector/node_modules/knex/lib/execution/runner.js:30:19)
    at async Promise.all (index 0)
    at async Timeout.cacheBlocksFromWaitlist [as _onTimeout] (/home/lisk/lisk-service/blockchain-connector/shared/sdk/cache.js:72:3) {
  code: 'SQLITE_BUSY'
}

Expected behavior

Multiple connections to file-based DB (Sqlite) shouldn't be created in the blockchain-connector and only one connection should be used for SQLite DB interaction

Steps to reproduce

• Start Lisk Service against a Lisk Core testnet node

Which version(s) does this affect? (Environment, OS, etc...)

v0.7.0-rc.1