SQLite database is locked error

[dukhaSlayer]

By default, SQLite doesn't support multiple connections. It can be enabled either with a shared cache or write-ahead log feature.

Default connection # for Hikari: DEFAULT_POOL_SIZE = 10, which under the load produces exceptions: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

I'd advise doing

config.setMaximumPoolSize(1);

in org.asamk.signal.manager.storage.Database#getHikariDataSource or trying enabling WAL with PRAGMA (not sure it will solve the issue completely though, if two connections will try to write simultaneously).

More info: https://stackoverflow.com/questions/10325683/can-i-read-and-write-to-a-sqlite-database-concurrently-from-multiple-connections

[dukhaSlayer]

Also wanted to ask why it is using IMMEDIATE transaction mode? :)

        sqliteConfig.setTransactionMode(SQLiteConfig.TransactionMode.IMMEDIATE);

[GooseBravo101]

Unfortunately setting max pool size to 1 makes things worse, or just reveals a bigger problem. Instead of randomly raising the exception, it just times out at first big query like key value store

Lately i am having a lot of SQLITE_BUSY exceptions in signal-cli, my application stops at least once a day because db was busy, and doesn't want to recover. I tried hiding all db communications in synchronized environment, didn't help, but i noticed that it usually times out at setting autocommit to false, trying to not use autocommit=false/commit pair didn't help either, it still breaks time after time

[AsamK]

sqlite does support multiple read connections at the same time, though every write will block the database. Setting the pool size to 1 will prevent any parallel read connections. IMMEDIATE transaction mode is used so that explicitly started transactions are immediately started as write transactions. The sqlite default behavior would be to start transactions as readonly and upgrade them to write transactions when an update statement is executed, which can lead to unpredictable behavior if the upgrade fails. It times out at setting autocommit to false because that's when the explicit write transaction is started. Disabling that can lead to data corruption due to not using a transaction for updating data.

Enabling WAL might help here. I've enabled WAL mode in e11e0930209e330649af182eaa0317d2d987d4e8, can you try the latest master if it works better?

[GooseBravo101]

Ok Now it just completely freezes without obvious exception Still receiving some envelopes, but not receiving messages. Eventually it starts spitting this error, and after that complete freeze Exception: org.signal.libsignal.protocol.DuplicateMessageException: message with old counter 1 / 0 (ProtocolDuplicateMessageException)

Note that i am running signal-cli in dbus daemon mode, maybe there is something broken there

[dukhaSlayer]

So,

Setting the pool size to 1 will prevent any parallel read connections.

Not really. If we don't use SQLiteConfig.TransactionMode.IMMEDIATE - we might have multiple read connections.

which can lead to unpredictable behavior if the upgrade fails.

I'm not sure why the behavior is unpredictable. When the upgrade fails it will be as if the transaction didn't happen, i.e. data will stay the same. Maybe I'm missing something and you mean some Whisper protocol side effects?

Now, in this ticket https://github.com/AsamK/signal-cli/issues/1675 (sorry, I've created two of them, I just thought the problem is smaller in the beginning) I described the issue.