Why use exclusive locking mode in SQLite?

[benbjohnson]

Hello! I'm the author of a tool called LiteFS which does real-time SQLite replication and it uses the SQLite locking protocol to detect transaction boundaries. Because of this, we prevent use of the WAL journaling mode while exclusive locking mode is enabled since it removes intermediate locking during transactions.

I was surprised to find that Prisma always uses exclusive locking mode and I was wondering why? I tried to do some digging and it seems like it was added in PR #1479 which fixes issue #1118 (advisory locking). However, in the issue itself it states that SQLite is a single writer and doesn't need to do advisory locking.

Beyond my specific use case, exclusive locking can cause other issues such as blocking migrations while other processes are using the SQLite database.

Would you consider removing exclusive locking mode or making optional?

Thank you.

[Jolg42]

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!

[Jolg42]

@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!

[dikyarga]

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!

[Jolg42]

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!

[Jolg42]

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

[zackify]

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 😎

[janpio]

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