‫In this lecture, we are going to discuss what is a database section, one of the most important things
‫really to discuss this concept that you are going to see everywhere when working with databases.
‫A transaction really is nothing but a collection of sequel queries that are treated as one unit of work.
‫Mm-Hmm.
‫And the reason we're treating this as one unit of work because the the nature of sequel or the structured
‫query language is my data is is is structured, it has money tables.
‫So it's very hard to do everything you want in one query.
‫And sometimes it's impossible, right?
‫So that you really need to do one or more queries to achieve what you would logically want.
‫Add the application.
‫I'm going to give example of that.
‫What we mean by that, but effectively, that's what the transaction is.
‫Use begin a transaction and then you do query through it four or five six seven seven eight nine 10
‫and then you end the transaction and that's one unit of work.
‫This is logically one thing that cannot be split and we're going to come to that.
‫So an example of that is an account deposit if you want to really deposit some money from one account
‫to another account.
‫Then you really need to first select the money from the first account, check if that guy or gal has
‫enough money in their account.
‫And then once you select that, you do an update to that account to deduct that $100.
‫And then the second update will be to deposit the the money in the other account, which is increase
‫by $100.
‫So I read Update Update.
‫This is a transaction that is consisting of three different queried.
‫Very, very important.
‫So let's talk about the transaction life span.
‫A transaction always begins with the key word begin that indicates to the database that, hey, you're
‫about to start a brand new transaction with multiple queries in it.
‫OK.
‫And then a transaction when you are satisfied, will all the queries, everything that you write is
‫not.
‫Durable, in a sense, it didn't actually get persisted in the database unless you say, Hey, I'm satisfied.
‫Please commit all the changes that I made during all the lifespan of the transaction.
‫This commit tills the transaction to commit and to persist.
‫This thing to disk.
‫And there are so many things here are really questions that you can ask when it comes to commit, right?
‫And think about the work that a database is doing to actually persist.
‫This changes.
‫Let's say you execute 2000 queries as a database maintainer if you build a database.
‫How would you do it?
‫Yes, the one you as a student listening to this, how would you build it?
‫Because everyone else that have built databases might have done it differently than you might have do
‫it right.
‫Because now if you commit like, what do you do every day for all these thousand queries, all these
‫thousand changes, do you actually write to disk with every one of these changes?
‫Or do you wait and put all of these in memory?
‫And then once you commit, you write everything to disk.
‫There's pros and cons to do everything on.
‫The first one will make it faster, right?
‫The second one would make it slower.
‫Right?
‫But at the end of the day, there are pros and cons to any of these things, right?
‫And this is what I want you to do here while listening to this course.
‫Always think about what is really happening behind the scene.
‫The basic bare bones fundamentals is what we are preaching here.
‫And obviously, sometimes things can doesn't necessarily go right, right?
‫It's like, OK, I don't know.
‫I guess you could have five 50000 queries in my transaction by bad idea, by the way, that the long
‫transaction are not recommended in general.
‫But now I'm just hey, only I need to roll back.
‫What does that mean?
‫Right?
‫Roll back.
‫That means, hey, all the changes.
‫Forget about it.
‫Forget that I made all these changes do not pass them, but wait a second.
‫Now remember my question earlier about the thousand squares, all these thousand queries as you execute
‫them, do you actually persist them to disk?
‫Because if you did, then you have to go and undo all these work, which is a lot of work.
‫The the the the second solution, right, where you write all these to memory and then commit to disk
‫all of these changes and you can play with the solidity you can roll back will be faster because, hey,
‫I didn't write anything, just flushing anything in the memory to destroy it.
‫All right.
‫This is what you play with rollback.
‫Right.
‫There is the undo redo space for all these changes as well.
‫That's how databases implement all this thing really differently.
‫But as I am telling you this, I want you to think about these things.
‫Think about how the database might have implemented this.
‫Things very critical that you don't read it.
‫Don't read, don't read how things are implemented because you're going to get polluted.
‫I want your original thoughts, right?
‫Put it there.
‫Let's have a discussion because one of you might have invented something that none of the computer scientist
‫might have implemented.
‫Yeah.
‫All right.
‫So, oh yeah.
‫Obviously, these are done by the user.
‫Hey, I want to roll back right now.
‫Hey, I want to commend right now.
‫But things can go wrong, obviously.
‫Right transactions unexpected ending need.
‫Because, hey, what if I am in the middle of my transaction 20000 queries later on, I got a crush
‫that I just crushed.
‫What happened here?
‫You need to actually roll back that.
‫But the database crashed.
‫How do you actually roll back?
‫Well?
‫Well, the database, when it comes back, a better no to roll back this.
‫So that's another record path that you need to code to.
‫Yeah.
‫Databases are very hard to build.
‫So think about all these use cases and you can optimize your database and you do use case based on that.
‫Every database does it differently.
‫Oscars does it differently.
‫SQL Server does it differently.
‫MySQL does it differently.
‫Oracle does it differently.
‫Each one optimizes on on what they personally think.
‫The engineers who built it that way.
‫I think I'm going to optimize for crashes.
‫Oh, I think I'm going to optimize for commits.
‫That's what Postgres.
‫That, by the way, commits on impulse because are the fastest that you can get, right?
‫Because they do that in a way ways that such that any query that executed during the transaction, they
‫try to persist this change.
‫So Bosca's does a lot of Io, but their comments are beautiful.
‫Fast.
‫So.
‫That's another thing.
‫Right?
‫So another thing that I didn't mention here is what if during a commit I get a crash?
‫That's the scariest thing, right?
‫If I if you commit, if your comments are fast, then the chances that you're going to get a casual
‫low, that's impossible.
‫But if you commit are slow, I believe, if I'm not mistaken, SQL Server comments are slow.
‫If you have a large transaction in general, then chances that you're going to get a crash, do you
‫want to comment is larger and that's scary because, hey, I committed what happened now.
‫Did I?
‫Is it committed or not?
‫And that's the scary part.
‫Let's talk about the nature of transactions.
‫Usually, transaction are used to change and modify data.
‫That's that's what we always think about transaction.
‫But actually, it's perfectly normal.
‫To have a read only transaction.
‫Hey, I'm going to start the transaction, I'm just going to read and when you actually till the database
‫that it can optimize it itself such that, oh, this is a read only transaction.
‫And you might say, Hossein, what are you doing in a read only transaction, really?
‫It's like, what?
‫Why just just read without a transaction?
‫Have every query, be its own transaction?
‫No, actually, you want a transaction to maintain consistency.
‫Let's talk about that a little bit.
‫Let's give an example.
‫You want to generate a report and you want to get a consistent snapshot based on the time transaction.
‫What the transaction gives you.
‫Not only the beauty of this group of things that I need to do as you add one unit of work.
‫No, you can also get it as as a snapshot of the time of the start of the transaction.
‫I want anything that I read is based on that initial time.
‫That's the.
‫Power of a transaction reading, because anything you read, if something changed on the buy a concurrent
‫transaction, you don't care.
‫You want to be isolated and that's another thing.
‫Another thing we're going to talk about.
‫Yeah, we will learn more about this in the isolation section.
‫Lecture.
‫Stay tuned for that.
‫It's another example of how transaction works.
‫We talked about this in our earlier.
‫That's how this is a table, right account ID.
‫And we have a two fields.
‫I account I.D. and balance the balance for account I.D. one is $1000.
‫The balance for our account ID two is a $500, and we want to send $100 from account one to account
‫two before we proceed with a sly.
‫Build this in your head.
‫How would you build it?
‫Stop this recording and then do it yourself and then move on.
‫Obviously, the slides are available for you if you want to do it yourself, but effectively, here's
‫what do you do.
‫You begin a transaction.
‫Transaction number one.
‫And then we select the balance from account, the table, the account table, where it equal one.
‫So now I have a thousand right.
‫You check, first of all, in order to debit this account one, I need to check if this guy even have
‫enough money, right?
‫We don't want to go in balance.
‫And as part of the rules and the constraints that we put in place, right?
‫You can put this in the application like what we're doing right now and you can put this constraint
‫at the database level.
‫So that says, Hey, this account, this balance should never go negative, for example, because it
‫doesn't make sense to go negative, right?
‫Unless you build your application in a way that negative mean something, right?
‫And if you ever see data that is negative, that is indication of an inconsistent data.
‫And now I'm mentioning a few things off with the offeror properties as you notice, right, as we discuss
‫the transaction.
‫But keep in mind, that's what inconsistency in data means.
‫There are different kind of inconsistency that we're going to mention.
‫All right.
‫I'm good.
‫I have more than $100.
‫Let's debate my account update account, sit balance, sit balance, equal balance minus one minus 100.
‫What I'd equal one.
‫Beautiful.
‫Now we have nine thousand ninety nine thousand nine hundred dollars.
‫No, I want in the same transaction.
‫Update the account two to add $100.
‫There you go.
‫Once we do that, we're happy with the changes, we commit the transaction.
‫So both of these are actually physically written to to the to their desk, right?
‫And we can argue, where is it exactly written?
‫That's a different situation and different discussion.
‫So let's let's do a quick summary on this lecture.
‫What is a transaction?
‫The transaction is a collection of queries that are treated as a single unit to a fork.
‫Transaction could change data, or it could be only read only as well, right?
‫And a transaction is always started.
‫This is something I do mention a transaction is always thought of.
‫When you don't start the transaction, we start one on the in the database section, the debusk so.
‫So if you execute, I don't know.
‫Just a normal update statement or an insert on the on the back end the database start the transaction
‫implicitly and always immediately commits it, right?
‫So we're always in a transaction, whether you like it or not, some transaction are user defined and
‫some are actually built and implicitly defined by the system.
‫I'm going to see on the next lecture.
‫Let's jump there.