Why use exclusive locking mode in SQLite? about prisma-engines HOT 7 OPEN

@benbjohnson I have something you can try and that should work:

Try setting the following environment variable PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1? (any truthy value will do)
Since PRAGMA main.locking_mode=EXCLUSIVE is only set for the advisory locking, disabling it should do the trick.

See https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production#advisory-locking

I'm curious if that works well for you or not, let us know!

from prisma-engines.

Hi @benbjohnson!

Thanks for the question and investigation ✨

I'll add a reference to the SQLite docs about the locking_mode for some context, which is quite good!: https://www.sqlite.org/pragma.html#pragma_locking_mode

In NORMAL locking-mode (the default unless overridden at compile-time using SQLITE_DEFAULT_LOCKING_MODE), a database connection unlocks the database file at the conclusion of each read or write transaction. When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.

Database locks obtained by a connection in EXCLUSIVE mode may be released either by closing the database connection, or by setting the locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). Simply setting the locking-mode to NORMAL is not enough - locks are not released until the next time the database file is accessed.

There are three reasons to set the locking-mode to EXCLUSIVE.

- The application wants to prevent other processes from accessing the database file.
- The number of system calls for filesystem operations is reduced, possibly resulting in a small performance increase.
- [WAL](https://www.sqlite.org/wal.html) databases can be accessed in EXCLUSIVE mode without the use of shared memory. ([Additional information](https://www.sqlite.org/wal.html#noshm))

About why, we added advisory locking is because we wanted to have a safe / reliable execution of migrations. By default, multiple processes like prisma migrate dev or prisma migrate deploy could be running at the same time, as a result the migrations could be unpredictable and fail, and if that happens on a "production" database, that can cause some problems.

So by making sure that only one process can actually run migrations, we can guarantee a default safety.

Now, I don't think all use cases were thought of regarding SQLite at the time, the thinking at the time was how to manage a local SQLite database (without any replication), so your questions are great!

from prisma-engines.

hey @Jolg42

I tried adding PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1 and running LiteFS 0.5.11, it does resolve fuse: write(): wal error: wal header: cannot write to WAL header without WRITE lock, exclusive locking not allowed issue.

But I'm not sure what the consequence of disabling that is; if I run those prisma commands one at a time and only have one CI pipeline running at a time, it should be fine, right?

thank you!

from prisma-engines.

The advisory lock mechanism is only there to prevent concurrent use of Prisma Migrate (so prisma db push & prisma migrate * commands).
If you set the env var to disable the advisory locking, and you know that these commands will not run concurrently on your SQLite database, it will be fine!

from prisma-engines.

By the way, it looks like the error message is from superfly/litefs#426
So you can try using PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1 and running this SQL once

PRAGMA locking_mode = NORMAL

from prisma-engines.

Thank you for asking this @benbjohnson, just saved me from being stuck unable to migrate with prisma on litefs cloud :)

All I had to do was set that env variable and now prisma migrations work on my primary node.

It would be super awesome if prisma added json support to sqlite now that it can run migrations and everything else without issues on fly 😎

from prisma-engines.

That request is being tracked here: prisma/prisma#3786 Leave a 👍 reaction on the issue if you haven't.

from prisma-engines.