‫That's almost he is one of the four acid properties that defines a relational database management system.
‫And I always say relational, but really it defines any database system, whether it's now SQL graph,
‫time based, really anything.
‫And Thomas, it is a very critical concept to understand how about we jump into this lecture and discuss
‫this beautiful concept?
‫So talk about automaticity.
‫Really all the queries in a transaction must succeed.
‫That is the definition of automaticity.
‫We talked about what a transaction is really in that transaction lecture, and we said that all the
‫queries in a given transaction is considered to be one unit of work.
‫And that kind of defines what, what, what automaticity really is, right?
‫It's like an atom.
‫And until now, an atom cannot be split.
‫I know there is nuclear fission that kind of contradicts that, but this thing was defined in 1970 and
‫I don't believe they discovered fission back then.
‫I might be wrong, but that's the way that automaticity comes into aura.
‫You treat this as an atom that cannot be split.
‫All the queries in a transaction must succeed if one query fail.
‫What happens if one query failed for any reason?
‫A failed constraint like the balance goes to minus one, minus negative, or a duplicate primary key
‫entry, right?
‫Or an actual invalid SQL syntax.
‫That is a failed query.
‫And what happens when you have one single?
‫Even if you had 100 successful queries in the same transaction, that one failure should roll back the
‫transaction immediately because I said things went wrong.
‫That's the rule of the.
‫So what happened?
‫If the database goes down during my transaction, I can't really roll back because the database goes
‫down.
‫What happened in this case?
‫If the database went down prior to a commit, you didn't really commit the transaction.
‫All the successful queries and the transactional should be rolled back.
‫All right.
‫Even if you had 100 successful queries in this transaction and you didn't really fail, you didn't fail,
‫but the database just crashed.
‫All right.
‫So the next query didn't really fail.
‫So the database will restart.
‫So once the database restarts, that's when the database should detect that, hey, there was a failure.
‫Technically, right, because my queries didn't fail.
‫But the database went down and I was made a transaction and I didn't commit these changes made by the
‫transaction.
‫If the database is actually making changes, it should be rolled back.
‫That's why I always want you to think about this as as you read this, as I as I talk.
‫What is the database doing in a transaction?
‫Is it actually writing to desk?
‫Right.
‫I'm not going to commit if what if I don't I don't commit.
‫Do we go back to disk and remove these changes?
‫All of this thing really is what makes or breaks a database.
‫And this goes back to most of the questions I receive really is what database should I choose?
‫Well, really, you need to understand how these databases work.
‫And based on your use case, make a choice.
‫There are databases that can be for comments, any right you make right in the in the queries and the
‫transaction they are actually writing to disk.
‫So they they assume you're going to commit.
‫So they are optimistic in that nature.
‫So when you actually, in fact commit, I don't need to do any work because I committed I actually wrote
‫everything to disk.
‫It will just write one one bit that says this transaction is committed and it's so fast.
‫Right.
‫Although the database is doesn't don't write anything.
‫So the queries are so fast executing because they always write to memory.
‫But the moment you want to commit, they have to go take everything that is in memory and then flushes
‫to disk and the commit will be slow in that regard.
‫But the roll back will be as absolutely fast.
‫You know, it all depends.
‫There is no right or wrong.
‫There is always a trade off.
‫Second example, this is an example I took in.
‫I talk about in my transaction lecture at Thomas.
‫So I have the same table account table balanced 2500 Account Idea one two.
‫We want to send money from $100 from account one to account two.
‫What do we do?
‫Very simple.
‫We need to debit this account and credit this account.
‫So -100 this, plus 100 this.
‫And we have to do it in a transaction because these two should happen at the same time.
‫Right.
‫So we start the transaction, we select the balance to check if this guy even have money.
‫And they do because the balance is greater than 100 because it's 1000, right?
‫Because that's the account one.
‫So yeah, let's go in and update the account.
‫Let's deduct 100 and.
‫And when
‫database crashed, I did one change and the second change I wanted to do, but the database crashed.
‫We restarted the database and now let's assume this is a bad, badly implemented database system.
‫This is what we see when we restart the system.
‫We see 900 here.
‫Where what?
‫What happened?
‫So in this case, we just lost $100 in thin air.
‫All right.
‫That's a really bad that's really bad.
‫Inconsistent.
‫This is an inconsistent view.
‫Not only you just lost actual money that you you have no idea where where it gone because of technology
‫effectively.
‫Right.
‫So after we restarted the machine, the first account has been debited by the other account has not
‫been credited.
‫Very bad, inconsistent view.
‫So lack of automaticity leads to inconsistencies.
‫And we're going to talk about consistencies in its own lecture.
‫This is really bad.
‫We just lost data.
‫The information is inconsistent.
‫We talked about that.
‫Anatomic transaction is a transaction that will roll back all the queries if one or more queries fail.
‫Well, in this situation, yeah, there is a failure, but it's not really our fault because my other
‫transaction, my other query sorry, didn't even execute.
‫So the database got restored.
‫So how do I know to roll back?
‫I don't know.
‫The database didn't even give it a chance to roll back.
‫I can issue the command rollback back so the database now is responsible to clean up this garbage.
‫It was okay.
‫There was a in a running transaction and he has to know that there is a transaction that has been running
‫and it was crashed.
‫Right.
‫So upon restart we need to clean up.
‫Right.
‫And let me let me tell you from my professional experience, right, I started working professionally
‫back in 2005 and I've seen roll back that takes an hour, over an hour actually for long transactions,
‫especially in SQL Server.
‫But I've seen rollbacks not actual roll back commit by the user.
‫No, actually the database says, wait, I can't even start the database before I roll back this this
‫stuff because it has to go and clean up all the garbage that has been written.
‫This half garbage that has been written.
‫Right.
‫And this is the garbage has been written here.
‫So we wrote that debits one account -100.
‫We need to change that 900 back to 1000 to get a consistent view.
‫And that's where all play some database.
‫I won't let you start the database before it's rolled back.
‫They they in recent patches and recent releases now say okay it's not really that bad, let them roll
‫rollback and let them continue working on other tables or other databases.
‫But this database now is just it's in a roll back state.
‫So CPU is getting hammered, right?
‫Memory is getting hammered.
‫All these things really.
‫As a result, the database should clean this up after we talked about this and we talked about how long
‫this could take.
‫Right.
‫And really, it depends on how much you wrote and how long was the transaction.
‫Long and transactions is generally a bad idea because of these things.
‫Let's do a quick summary on the terms of the items.
‫It is the idea of the transaction being one unit of work and that cannot be split.
‫That's the property of an item.
‫Please.
‫At least back in 1970, if they figured efficient, nuclear efficient, then this, this, this, this
‫is broken effectively.
‫But you get idea, right?
‫So you cannot really an atom should be one unit and cannot be split.
‫Right.
‫So if if you have a transaction with 100 queries, all these 100 quests should succeed.
‫If any of these 100 query fail, all the hundred or anything that is successful in the query should
‫be rolled back.
‫And not only that, but you should even in the case of a crash in the database.
‫So that's not an actual explicit failure by the user.
‫That should also roll back the changes.
‫That was that tomasetti lecture.
‫I'm going to see you in the next lecture.
‫Stay tuned.