TryGhost/knex-migrator

Sqlite and locks

kirrg001 opened this issue · 1 comments

By default, sqlite does not support read locks per row/table. That's why knex-migrator is unable to execute a proper lock on concurrent requests. Other migration tools e.g. knex inbuilt migrations have no handling for this as well. So what happens in Sqlite is that both processes would read the lock row and they write sequentielle into the row without having to wait. (because of e.g. none support of for update).

There is the WAL mode (write ahead) and there are exclusive transactions which might solve locking, but i haven't used any of them in the past. Exclusive transactions lock the whole file, not sure that is a good approach.

It's also written in the README, that sqlite won't support locks.

We haven't run into issues with this recently, closing until it becomes a problem 😄