[SQLite 5] database is locked

[VaibhaveS]

Migration fails due to contention between the stream & apply processes. It retries for a while & make some progress however it fails and stops retrying when there is no-progress in the writeLSN.

2024-04-02 14:08:55.044 54931 DEBUG  ld_stream.c:1442          Flushed up to 0/64AF1228 in file "/home/shdhama/repos/oss/pgcopydb/Debug/date-24-04-02--time-14-07-53-all_types_base/cdc/000000010000000000000064.json.partial"
2024-04-02 14:08:55.045 54931 SQLite catalog.c:7387            [SQLite] update sentinel set startpos = $1, write_lsn = $2, flush_lsn = $3 where id = 1
2024-04-02 14:08:55.045 54931 SQLite catalog.c:7766            [SQLite] 0/64AF1228, 0/64AF1228, 0/64AF1228
2024-04-02 14:08:55.045 54931 SQLite catalog.c:7597            [SQLite 5]: database is locked, try again in 10ms
2024-04-02 14:08:55.053 54933 SQLite catalog.c:7387            [SQLite] insert into lsn_tracking(source, target) values($1, $2)
2024-04-02 14:08:55.054 54933 SQLite catalog.c:7766            [SQLite] 0/64AF1228, 1/2EF8B630
2024-04-02 14:08:55.055 54931 SQLite catalog.c:7597            [SQLite 5]: database is locked, try again in 19ms
2024-04-02 14:08:55.066 54933 SQLite catalog.c:7387            [SQLite] insert into lsn_tracking(source, target) values($1, $2)
2024-04-02 14:08:55.066 54933 SQLite catalog.c:7766            [SQLite] 0/64AF0868, 1/2EF8B630
2024-04-02 14:08:55.074 54931 SQLite catalog.c:7597            [SQLite 5]: database is locked, try again in 87ms

2024-04-02 14:14:30.584 93 WARN   ld_stream.c:526           Streaming got interrupted at 228/EE059D20, and did not make any progress from previous attempt, stopping now

[dimitri]

Is it related to #712 ? Can you check if that PR fixes it for you?

[VaibhaveS]

Hi, we have applied the patch in #712, this issue seems to be different

[dimitri]

Failing to reproduce in the lab, I got back to this issue and I now see traffic about the lsn_tracking SQLite table. This has been removed in https://github.com/dimitri/pgcopydb/pull/731. Can you test with that change and report back if you still see the problem?