superfly/litefs

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

Closed this issue · 11 comments

benbjohnson commented

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

Looks like the workaround we talked about doesn't work: epicweb-dev/epic-stack#598 (comment)

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

dchadcluff commented

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

  • 👍1
kentcdodds commented

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.

  • 👍2
benbjohnson commented

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

Thank you for putting time into this!

benbjohnson commented

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.

  • ❤️1
kentcdodds commented

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

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.

  • ❤️1
paolotiu commented

Thanks for this ben!

benbjohnson commented

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

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

  • ❤️3