Now that we have settled on wall mode being the ideal way to run your SQLite database, we need to talk about one other pragma and that is the busy time out. So while you can have multiple readers reading that database up through the last transaction that they've seen in the write ahead log, you can still only have 1 writer writing to that write ahead log at a time. So anytime a writer is writing to the write ahead log, the other writers have to wait. And busy time out is how we can figure how long they can wait before giving up and throwing an error. By default, it's set to 0 which is not really what we want because we're fine waiting a certain amount of time to, to have our right go into the write of headlock.

But we don't wanna wait forever. So let's look at how we can configure that. I'm connected to this database in both panes because I want to begin a transaction over here. And over here, we're gonna check the pragma busy time out. And by default, it is set to 0.

So what happens if I do select star let's do select ID first name from users where ID equals 1. We're just gonna select the first user with a first name of l. That totally worked just fine. This transaction has not declared its intent to write and so it's just it's just reading. So we have no we have no locking problem here yet.

In fact, we can do update users set first name equal to, what's another letter, m where id equals 1 and we're totally, totally fine. We can continue to write even when this transaction is open. Now if we were to roll this transaction back and then we're gonna begin a different type of transaction. In the next video we're gonna talk about all the types of transactions, but now we're gonna begin an exclusive transaction. So we're gonna begin an exclusive transaction over there and then we're gonna try to update this again and let's set it to n just so we can see what's going on.

And it immediately fails. This is the dreaded SQLite busy error that says, hey. The database is locked. And you know who it's locked by? This guy.

This guy that has the transaction open and he's not doing anything with it. You open an exclusive transaction and then you just sit on it. That's pretty frustrating. So we can change the busy time out we never hit that. So now we have a busy timeout of 5 seconds.

And if we were to try to update this again, at least this time it's going to wait for 5 seconds before it says, that the database is locked. Now, single lite in reality can only support 1 concurrent writer at a time, but the writes should be very fast if you're not doing something silly like this. The rights should be very fast. So we're fine waiting a few milliseconds, a few 100 milliseconds to allow these rights to go through versus hitting a deadlock. Right.

So here, let's try this again. So if we were to update where ID equals 1, then we're gonna hop over into the other pane and try to roll back or close this exclusive transaction and we'll see the ideal scenario, play out. Here we go. 1, 2, 3, 4. Great.

So we we rolled this transaction back within 5 seconds which means this was like, finally I can I can, I can write my update to the database? And so if we were to select, let's just arrow up, we can select out and we see that it was updated to n. Having waited a few seconds, this transaction closed and then on the left this one was able to issue its update to the database. So you'll need to set your busy time out on, the connecting side because it is not a persistent, it's not a persistent pragma. So if we were to exit out and come back in and then hit prag ma busy time out, you're gonna see it's back to 0.

So this one is a per connection pragma. So likely on the application side you'll want to issue this pragma busy time out equals 5,000 every time you open a new database connection. In the next video we're gonna talk about these transactions because they can come back to bite you if you're not careful.