Aaron is communicating so clearly that my 10 year old daughter understands what he is talking about. Production is at the highest level I have ever seen. I first watched his MySQL course and I can see the progress Steve and Aaron are making. HP SQLite course is a pure masterpiece.Ivan Bunčić
Build with and integrate SQLite into your production applications.
I've made my test data available for you to use and follow along.
Wall mode. This is it y'all. The rest of the videos are very good and I put a lot of work into them. This is the most important thing for high performance SQLite is wall mode. We talked about rollback mode in the last video.
If we take a look at pragma journal journal mode in this database, you'll see it is set to wall, w a l. This is a persistent setting. So you set this once in your database and then everybody that connects to it, it's going to be wall mode. So let me recap rollback mode because we're gonna compare and contrast wall mode a little bit. Remember, in rollback mode, what happens is the database goes to write something.
Before it writes anything, it takes the page, which is a subset of the database, it takes a page and it sets it aside. That is the safe place for that original data. Then it writes a bunch of data into the database and then commits the transaction at which point the journal is disregarded. It is it is, ruined somehow, either deleted or truncated or filled with zeros or something. The act of nullifying that journal is what completes the commit process.
A commit is not fully committed until that journal is gone. That's rollback mode. Right ahead logging, There's the hint, right ahead logging is different. It leaves the database untouched. All of those pages are totally untouched and instead it creates a new file.
It creates the write ahead log and that's where it puts new rights. So instead of copying a page out and creating a new file and then writing in the database and then deleting the file, it doesn't do any of that. It just starts appending to the right ahead log. And so what happens then is at some point, at some point a checkpoint happens and that right ahead log is taken and merged with the database as it exists and then boom you have a new single database and then the write ahead log starts happening again. Right?
So every now and then, and it's configurable, every now and then a checkpoint happens and that's where it takes all the write ahead log data and cements it into the dot SQLite file or dot whatever you decide to name yours. That's called the checkpoint process. Now, why is this better? Well, it's a whole lot faster. That's for 1.
So instead of having to copy the file out and then delete all of that, we're just appending to the end. But this also means that, we can have concurrent readers along with our single writer. So our single writer, what it does is it appends to the end of the write ahead log file. But when the readers connect or open the database, when the readers start reading, they look at the write ahead log file and they say, alright. What is the last what is the last transaction that is visible to me?
What is the last transaction that I can see? And then it remembers that throughout the time that it's reading. And so it'll read through the database and through the write ahead log to make sure that everything is, it's it's seeing the entirety of the universe, but it will not go past its stop sign. So when it opens it up, it says, alright. The last transaction I saw was number 5.
And now a writer may come in and write transaction 6, 7, 8, 9, 10, 11. But the reader says, well, you know, I'm only able to see through number 5. And so you can have tons and tons of readers while your writers are working, so your concurrency goes way way up. And so your writers don't block your readers anymore. And so wall mode is the single greatest thing you can do to increase the throughput of your SQLite database.
And in the next video, we're gonna compare actual benchmarks between rollback mode and write ahead log mode.