search

superfly / litefs

FUSE-based file system for replicating SQLite databases across a cluster of machines
Apache License 2.0
3.76k stars 89 forks source link

Writing to the WAL while using EXCLUSIVE locking mode should be prevented #425

Closed benbjohnson closed 5 months ago

benbjohnson commented 5 months ago

There seems to be an issue with initializing WAL mode with a single CLI command before any other data has been stored:

$ sqlite3 /litefs/my.db "PRAGMA journal_mode = wal"
$ prisma migrate deploy
kentcdodds commented 5 months ago

Looks like the workaround we talked about doesn't work: https://github.com/epicweb-dev/epic-stack/issues/598#issuecomment-1903040325

Maybe running migrations at all when the database is in WAL mode won't work on LiteFS?

dchadcluff commented 5 months ago

I'm experiencing the same issue now. Migrations not working on LiteFS.

kentcdodds commented 5 months ago

I think if you disable WAL mode it'll probably start working. Then once @benbjohnson fixes this we can try turning it on again. I'm going to disable it on the Epic Stack for now.

benbjohnson commented 5 months ago

Just an update: I set up some test cases to issue the WAL change via the CLI and then make schema changes afterward on a separate connection but it's working fine in the test cases. I'm going to dig into the Prisma implementation to try to figure out if they're doing something unique with their connection.

kentcdodds commented 5 months ago

Thank you for putting time into this!

benbjohnson commented 5 months ago

Ok, it looks like the issue is that Prisma sets the PRAGMA locking_mode = EXCLUSIVE which circumvents how locking is done in WAL mode. LiteFS doesn't currently support exclusive locking mode on WAL because it's rarely used.

I'm actually not sure why they use that locking mode. From the SQLite docs, it doesn't seem like it's helpful except to block other processes from accessing the database. It looks like it was enabled 3 years ago as part of this commit.

All that being said, LiteFS should handle this more gracefully. I'll get that fixed up.

kentcdodds commented 5 months ago

Nice find! So is it safe to say that Prisma doesn't support WAL mode and a reasonable feature request would be to ask Prisma to support WAL mode?

benbjohnson commented 5 months ago

It's not that Prisma doesn't support WAL mode. The issue is that LiteFS acts as a passthrough file system but it determines transaction boundaries, in part, by the locking protocol. The EXCLUSIVE locking mode changes all that for the WAL so LiteFS doesn't demarcate the transaction boundaries correctly.

I think a feature request to either remove EXCLUSIVE locking mode or to make it optional would be reasonable. I can submit one once I dig a little deeper.

paolotiu commented 5 months ago

Thanks for this ben!

benbjohnson commented 5 months ago

I added a fix to prevent the transaction from occurring if the WRITE lock has not been acquired exclusively (#426). This prevents WAL mode from being used with EXCLUSIVE locking mode. I'll cut a release once I merge it in.

benbjohnson commented 5 months ago

A new release has been cut with the fix: v0.5.11