In the last video, I briefly introduced begin exclusive transaction. There are 3 transaction modes deferred, exclusive and immediate. In wall mode, which you want to be in wall mode, intermediate and exclusive are the same thing. Deferred is the default so you never have to write it. And immediate and exclusive are the same in wall mode so you can write either one.

Now what is the issue here? Let me show you the issue or the the potential problem that you'll need to, be very cognizant of on the application side. Over here, begin immediate, same as exclusive, begin immediate transaction. So we begin immediate transaction over here. Let's check the pragma pragma busy time out over here.

Let's go ahead and set that to 5,000. And then we're going to begin a deferred. But because deferred is the default, we don't have to write it. Begin deferred transaction. So this transaction is open.

It has not declared its intent to write to the database. This one is holding the exclusive write lock. So over here, we can do select ID name from users limit 1. That's fine. Whoops.

That's not fine. Select ID first name limit users. That's fine. Get out of here. Give me the box mode.

Alright. That's fine like I was trying to say. So we can go ahead and select. We're inside of a transaction. Try to write, SQLite is going to try to upgrade our lock into exclusive.

Now we have shown in the past video that that should wait 5 seconds because this has the exclusive lock on it and we've set the busy time out to 5 seconds. Right? But, what is actually gonna happen? So, if we update users set first name equal to I don't remember what letter we're on, so we're gonna go with a where id equals 1. This should wait 5 seconds, but it doesn't.

So when you're inside of a deferred transaction and you try to upgrade to an exclusive lock and the lock is already taken, you don't get your 5 second wait time. It just immediately throws SQLite busy. So let's get out of here and clear this. And if we were to begin an immediate, same as exclusive, begin an immediate transaction over here, from the very beginning we are declaring our intent to write to the database and so we do get that 5 second grace period where it says, okay, I'm gonna try, but alas, the database was locked. This is what you want.

This is definitely what you want because you don't want to be in the middle of a transaction and think, okay, well I know there's only 1 rider allowed but I have up to 5 seconds to do my rights. That's fine. And you try to upgrade to an exclusive lock and it immediately fails and you get a single light busy and your sentry starts blowing up and your users start complaining. So on the application side, whenever you connect to the database, you're going to want to set your transaction mode to immediate. Now, this depends and this varies based on your application side, whether that's rails or Laravel or Next JS or Django or something like that.

You'll need to look up for your specific framework how to connect to the database and how to make sure that all of the rights are wrapped in a begin immediate transaction. Most web frameworks wrap the rights in a transaction regardless, and so you're looking for the thing where you can set, alright, we want that to be an immediate transaction. I think the rails adapter for SQLite, I think that does that by default. I know in PHP, you can set a p d o attribute to set it to immediate. So you'll have to do a little bit of digging.

Sorry, I can't speak to all of them directly. You'll have to do a little bit of digging, but you want all of your rights to begin immediate transactions so that you do have that 5 second grace period and you don't get hosed trying to update a transaction in the middle of that transaction.