Of the criticisms you hear about SQLite a lot is that it's not ready for production. It's great for your little hobby app or your toy app or testing, but it's not ready for production. And the reason most commonly cited for that is that it only supports 1 concurrent writer. It can it can support many, many untold numbers of readers but you can only have 1 process writing to the database at once. That much is true, but it's not necessarily true that it's only good for toy apps or hobby apps.

You can support many tens, potentially hundreds of thousands of queries per second, hundreds of thousands of of reads per second with SQLite. And when it comes to writing, the writes should complete very, very quickly such that you can support many thousands of writes per second, if not tens of thousands depending on what exactly you're writing. So from the point of view, from the point of view of the user, it doesn't really matter that we can only have 1 writer at a time. Maybe we get a a few millisecond penalty while we're waiting for other writers to finish. But from the point of view of the user it it seems fine.

So don't necessarily take the fact that there can only be 1 concurrent writer. Don't take that as meaning it won't support the load for your application. If you have many thousands of requests per second, that's when you start getting into the area of, like, oh, maybe I do need to look at a different database. But that's many thousands of requests per second. So I think we're I think you're gonna be fine for a long time using SQLite as your database.

And we're gonna look in this module at tuning it to make sure that you're you have the best, configuration to support as many reads and writes as possible. And, in fact, we're gonna look at some benchmarking just to see how it works and that it works. But in this video, I want to talk specifically about the locking, the the pretty advanced locking that SQLite does to ensure that we're not writing half of a transaction, we're not reading a partially committed transaction. So the first state, there are 5 states, that So the first state, there are 5 states, that these locks escalate through from the bottom up. The first state is unlocked.

That's easy. No reading, no writing. That is the first state, unlocked. When we move up a level, we move into a shared lock. So a process will acquire a shared lock on the database and that means you can read it, I can read it, everyone can read it, but nobody can write to it.

So all of the readers are acquiring these shared locks. It's like, hey, we're all good. Everybody's reading. That's totally fine. Beyond that, then you moved in you move into a reserved lock state.

And what the reserved lock does, the reserved lock is when a process announces, hey. I'm going to write. I am going to write at some point, but I'm not writing yet. In the reserved lock state, new shared locks can be acquired and so we're just moving up barely. When we really move up into a next state, that is when we move into the pending state.

When we move into the pending state, no new readers can start. So nobody can acquire a new shared lock. In the pending state, we're waiting for all the open shared locks to go away. We're waiting for all the readers to finish up and no new readers can start. Once all the readers have gone away, all the shared locks are gone, we move into an exclusive lock and that is when it writes to the database and gets out of there as fast as possible.

Then all the shared locks come back in and we start the process again if there is another writer to the database. Now remember, this process is happening over the course of a handful of milliseconds depending on what you're actually writing. So in the next videos we're gonna look at the 2 different journaling modes and this is going to this is going to make a little bit Now remember, this all happens within a matter of milliseconds. It is super duper fast. This is going to inform what we talk about in the next 2 videos which is gonna be the rollback journal and the write ahead log.

This is going to help us contextualize and understand that. So don't feel like don't feel like you need to memorize all of this. This is more just for your edification and laying the groundwork for the things in the next videos which are going to affect our actual performance and are going to be something that you need to configure on your database.