1
00:00:00,060 --> 00:00:07,560
‫In this lecture, we are going to discuss what is a database section, one of the most important things

2
00:00:07,560 --> 00:00:15,690
‫really to discuss this concept that you are going to see everywhere when working with databases.

3
00:00:16,410 --> 00:00:28,050
‫A transaction really is nothing but a collection of sequel queries that are treated as one unit of work.

4
00:00:28,360 --> 00:00:28,590
‫Mm-Hmm.

5
00:00:29,100 --> 00:00:38,670
‫And the reason we're treating this as one unit of work because the the nature of sequel or the structured

6
00:00:38,670 --> 00:00:44,940
‫query language is my data is is is structured, it has money tables.

7
00:00:44,940 --> 00:00:50,510
‫So it's very hard to do everything you want in one query.

8
00:00:50,520 --> 00:00:52,710
‫And sometimes it's impossible, right?

9
00:00:52,890 --> 00:00:59,240
‫So that you really need to do one or more queries to achieve what you would logically want.

10
00:00:59,250 --> 00:01:00,450
‫Add the application.

11
00:01:00,880 --> 00:01:02,280
‫I'm going to give example of that.

12
00:01:02,550 --> 00:01:06,030
‫What we mean by that, but effectively, that's what the transaction is.

13
00:01:06,030 --> 00:01:12,020
‫Use begin a transaction and then you do query through it four or five six seven seven eight nine 10

14
00:01:12,090 --> 00:01:15,270
‫and then you end the transaction and that's one unit of work.

15
00:01:15,510 --> 00:01:20,400
‫This is logically one thing that cannot be split and we're going to come to that.

16
00:01:20,610 --> 00:01:28,020
‫So an example of that is an account deposit if you want to really deposit some money from one account

17
00:01:28,020 --> 00:01:29,100
‫to another account.

18
00:01:30,210 --> 00:01:37,260
‫Then you really need to first select the money from the first account, check if that guy or gal has

19
00:01:37,260 --> 00:01:39,030
‫enough money in their account.

20
00:01:39,270 --> 00:01:45,390
‫And then once you select that, you do an update to that account to deduct that $100.

21
00:01:45,630 --> 00:01:54,150
‫And then the second update will be to deposit the the money in the other account, which is increase

22
00:01:54,420 --> 00:01:55,680
‫by $100.

23
00:01:55,710 --> 00:01:58,650
‫So I read Update Update.

24
00:01:59,550 --> 00:02:04,950
‫This is a transaction that is consisting of three different queried.

25
00:02:05,040 --> 00:02:06,570
‫Very, very important.

26
00:02:07,740 --> 00:02:10,650
‫So let's talk about the transaction life span.

27
00:02:11,430 --> 00:02:19,110
‫A transaction always begins with the key word begin that indicates to the database that, hey, you're

28
00:02:19,110 --> 00:02:22,950
‫about to start a brand new transaction with multiple queries in it.

29
00:02:23,160 --> 00:02:23,460
‫OK.

30
00:02:23,760 --> 00:02:30,030
‫And then a transaction when you are satisfied, will all the queries, everything that you write is

31
00:02:30,030 --> 00:02:30,390
‫not.

32
00:02:31,610 --> 00:02:40,190
‫Durable, in a sense, it didn't actually get persisted in the database unless you say, Hey, I'm satisfied.

33
00:02:40,460 --> 00:02:45,590
‫Please commit all the changes that I made during all the lifespan of the transaction.

34
00:02:45,980 --> 00:02:50,630
‫This commit tills the transaction to commit and to persist.

35
00:02:50,630 --> 00:02:51,760
‫This thing to disk.

36
00:02:51,770 --> 00:02:58,740
‫And there are so many things here are really questions that you can ask when it comes to commit, right?

37
00:02:59,600 --> 00:03:05,390
‫And think about the work that a database is doing to actually persist.

38
00:03:05,390 --> 00:03:06,200
‫This changes.

39
00:03:06,530 --> 00:03:13,910
‫Let's say you execute 2000 queries as a database maintainer if you build a database.

40
00:03:14,000 --> 00:03:15,020
‫How would you do it?

41
00:03:15,260 --> 00:03:20,330
‫Yes, the one you as a student listening to this, how would you build it?

42
00:03:20,600 --> 00:03:27,500
‫Because everyone else that have built databases might have done it differently than you might have do

43
00:03:27,500 --> 00:03:28,040
‫it right.

44
00:03:28,250 --> 00:03:34,760
‫Because now if you commit like, what do you do every day for all these thousand queries, all these

45
00:03:34,760 --> 00:03:40,220
‫thousand changes, do you actually write to disk with every one of these changes?

46
00:03:40,340 --> 00:03:43,100
‫Or do you wait and put all of these in memory?

47
00:03:43,790 --> 00:03:47,810
‫And then once you commit, you write everything to disk.

48
00:03:48,050 --> 00:03:50,480
‫There's pros and cons to do everything on.

49
00:03:50,510 --> 00:03:53,540
‫The first one will make it faster, right?

50
00:03:53,690 --> 00:03:56,000
‫The second one would make it slower.

51
00:03:56,420 --> 00:03:56,610
‫Right?

52
00:03:56,900 --> 00:04:01,970
‫But at the end of the day, there are pros and cons to any of these things, right?

53
00:04:02,210 --> 00:04:06,140
‫And this is what I want you to do here while listening to this course.

54
00:04:06,500 --> 00:04:10,220
‫Always think about what is really happening behind the scene.

55
00:04:10,520 --> 00:04:17,150
‫The basic bare bones fundamentals is what we are preaching here.

56
00:04:18,200 --> 00:04:23,680
‫And obviously, sometimes things can doesn't necessarily go right, right?

57
00:04:23,720 --> 00:04:24,830
‫It's like, OK, I don't know.

58
00:04:25,310 --> 00:04:30,680
‫I guess you could have five 50000 queries in my transaction by bad idea, by the way, that the long

59
00:04:30,680 --> 00:04:32,600
‫transaction are not recommended in general.

60
00:04:33,020 --> 00:04:36,650
‫But now I'm just hey, only I need to roll back.

61
00:04:37,610 --> 00:04:38,450
‫What does that mean?

62
00:04:38,870 --> 00:04:39,290
‫Right?

63
00:04:39,710 --> 00:04:40,520
‫Roll back.

64
00:04:40,520 --> 00:04:42,620
‫That means, hey, all the changes.

65
00:04:43,040 --> 00:04:43,910
‫Forget about it.

66
00:04:44,850 --> 00:04:49,590
‫Forget that I made all these changes do not pass them, but wait a second.

67
00:04:50,100 --> 00:04:57,660
‫Now remember my question earlier about the thousand squares, all these thousand queries as you execute

68
00:04:57,660 --> 00:04:59,790
‫them, do you actually persist them to disk?

69
00:05:00,390 --> 00:05:06,180
‫Because if you did, then you have to go and undo all these work, which is a lot of work.

70
00:05:06,900 --> 00:05:15,930
‫The the the the second solution, right, where you write all these to memory and then commit to disk

71
00:05:16,440 --> 00:05:22,680
‫all of these changes and you can play with the solidity you can roll back will be faster because, hey,

72
00:05:22,710 --> 00:05:26,820
‫I didn't write anything, just flushing anything in the memory to destroy it.

73
00:05:27,270 --> 00:05:27,510
‫All right.

74
00:05:27,840 --> 00:05:30,330
‫This is what you play with rollback.

75
00:05:30,810 --> 00:05:31,170
‫Right.

76
00:05:31,170 --> 00:05:35,160
‫There is the undo redo space for all these changes as well.

77
00:05:35,160 --> 00:05:39,240
‫That's how databases implement all this thing really differently.

78
00:05:39,990 --> 00:05:45,420
‫But as I am telling you this, I want you to think about these things.

79
00:05:46,230 --> 00:05:48,750
‫Think about how the database might have implemented this.

80
00:05:48,750 --> 00:05:53,040
‫Things very critical that you don't read it.

81
00:05:53,040 --> 00:05:57,210
‫Don't read, don't read how things are implemented because you're going to get polluted.

82
00:05:57,450 --> 00:06:00,240
‫I want your original thoughts, right?

83
00:06:00,360 --> 00:06:01,170
‫Put it there.

84
00:06:01,740 --> 00:06:07,490
‫Let's have a discussion because one of you might have invented something that none of the computer scientist

85
00:06:07,890 --> 00:06:08,850
‫might have implemented.

86
00:06:09,270 --> 00:06:09,690
‫Yeah.

87
00:06:09,810 --> 00:06:10,080
‫All right.

88
00:06:10,530 --> 00:06:11,460
‫So, oh yeah.

89
00:06:11,850 --> 00:06:14,700
‫Obviously, these are done by the user.

90
00:06:14,700 --> 00:06:16,590
‫Hey, I want to roll back right now.

91
00:06:16,620 --> 00:06:18,000
‫Hey, I want to commend right now.

92
00:06:18,000 --> 00:06:20,580
‫But things can go wrong, obviously.

93
00:06:20,580 --> 00:06:23,760
‫Right transactions unexpected ending need.

94
00:06:23,970 --> 00:06:30,540
‫Because, hey, what if I am in the middle of my transaction 20000 queries later on, I got a crush

95
00:06:30,840 --> 00:06:31,620
‫that I just crushed.

96
00:06:31,920 --> 00:06:32,730
‫What happened here?

97
00:06:33,540 --> 00:06:35,580
‫You need to actually roll back that.

98
00:06:35,850 --> 00:06:37,050
‫But the database crashed.

99
00:06:37,260 --> 00:06:38,790
‫How do you actually roll back?

100
00:06:38,800 --> 00:06:39,210
‫Well?

101
00:06:39,230 --> 00:06:43,980
‫Well, the database, when it comes back, a better no to roll back this.

102
00:06:44,250 --> 00:06:47,070
‫So that's another record path that you need to code to.

103
00:06:47,280 --> 00:06:47,730
‫Yeah.

104
00:06:48,150 --> 00:06:50,340
‫Databases are very hard to build.

105
00:06:50,820 --> 00:06:58,140
‫So think about all these use cases and you can optimize your database and you do use case based on that.

106
00:06:58,350 --> 00:07:00,720
‫Every database does it differently.

107
00:07:00,820 --> 00:07:02,040
‫Oscars does it differently.

108
00:07:02,040 --> 00:07:03,330
‫SQL Server does it differently.

109
00:07:03,330 --> 00:07:04,590
‫MySQL does it differently.

110
00:07:04,740 --> 00:07:05,910
‫Oracle does it differently.

111
00:07:06,270 --> 00:07:10,170
‫Each one optimizes on on what they personally think.

112
00:07:10,170 --> 00:07:12,330
‫The engineers who built it that way.

113
00:07:12,570 --> 00:07:15,450
‫I think I'm going to optimize for crashes.

114
00:07:15,690 --> 00:07:17,550
‫Oh, I think I'm going to optimize for commits.

115
00:07:17,730 --> 00:07:18,480
‫That's what Postgres.

116
00:07:18,480 --> 00:07:24,080
‫That, by the way, commits on impulse because are the fastest that you can get, right?

117
00:07:24,540 --> 00:07:31,950
‫Because they do that in a way ways that such that any query that executed during the transaction, they

118
00:07:31,950 --> 00:07:33,630
‫try to persist this change.

119
00:07:33,990 --> 00:07:37,950
‫So Bosca's does a lot of Io, but their comments are beautiful.

120
00:07:37,980 --> 00:07:38,580
‫Fast.

121
00:07:38,850 --> 00:07:39,240
‫So.

122
00:07:40,610 --> 00:07:41,540
‫That's another thing.

123
00:07:42,140 --> 00:07:42,440
‫Right?

124
00:07:42,680 --> 00:07:47,750
‫So another thing that I didn't mention here is what if during a commit I get a crash?

125
00:07:48,410 --> 00:07:49,790
‫That's the scariest thing, right?

126
00:07:50,390 --> 00:07:55,220
‫If I if you commit, if your comments are fast, then the chances that you're going to get a casual

127
00:07:55,220 --> 00:07:56,630
‫low, that's impossible.

128
00:07:56,870 --> 00:08:02,480
‫But if you commit are slow, I believe, if I'm not mistaken, SQL Server comments are slow.

129
00:08:02,990 --> 00:08:08,780
‫If you have a large transaction in general, then chances that you're going to get a crash, do you

130
00:08:08,780 --> 00:08:13,790
‫want to comment is larger and that's scary because, hey, I committed what happened now.

131
00:08:14,210 --> 00:08:14,750
‫Did I?

132
00:08:15,050 --> 00:08:16,160
‫Is it committed or not?

133
00:08:16,520 --> 00:08:17,690
‫And that's the scary part.

134
00:08:19,010 --> 00:08:20,720
‫Let's talk about the nature of transactions.

135
00:08:20,960 --> 00:08:25,400
‫Usually, transaction are used to change and modify data.

136
00:08:25,520 --> 00:08:28,220
‫That's that's what we always think about transaction.

137
00:08:28,220 --> 00:08:32,720
‫But actually, it's perfectly normal.

138
00:08:33,820 --> 00:08:35,800
‫To have a read only transaction.

139
00:08:36,190 --> 00:08:42,820
‫Hey, I'm going to start the transaction, I'm just going to read and when you actually till the database

140
00:08:42,820 --> 00:08:48,850
‫that it can optimize it itself such that, oh, this is a read only transaction.

141
00:08:49,570 --> 00:08:54,400
‫And you might say, Hossein, what are you doing in a read only transaction, really?

142
00:08:54,400 --> 00:08:55,190
‫It's like, what?

143
00:08:55,210 --> 00:08:58,030
‫Why just just read without a transaction?

144
00:08:58,690 --> 00:09:02,320
‫Have every query, be its own transaction?

145
00:09:02,860 --> 00:09:07,180
‫No, actually, you want a transaction to maintain consistency.

146
00:09:07,210 --> 00:09:08,200
‫Let's talk about that a little bit.

147
00:09:09,040 --> 00:09:09,940
‫Let's give an example.

148
00:09:10,480 --> 00:09:15,850
‫You want to generate a report and you want to get a consistent snapshot based on the time transaction.

149
00:09:15,880 --> 00:09:17,440
‫What the transaction gives you.

150
00:09:17,440 --> 00:09:22,570
‫Not only the beauty of this group of things that I need to do as you add one unit of work.

151
00:09:22,570 --> 00:09:28,660
‫No, you can also get it as as a snapshot of the time of the start of the transaction.

152
00:09:28,960 --> 00:09:32,950
‫I want anything that I read is based on that initial time.

153
00:09:33,940 --> 00:09:34,630
‫That's the.

154
00:09:35,530 --> 00:09:43,210
‫Power of a transaction reading, because anything you read, if something changed on the buy a concurrent

155
00:09:43,210 --> 00:09:45,110
‫transaction, you don't care.

156
00:09:45,160 --> 00:09:47,860
‫You want to be isolated and that's another thing.

157
00:09:47,890 --> 00:09:49,180
‫Another thing we're going to talk about.

158
00:09:49,270 --> 00:09:52,060
‫Yeah, we will learn more about this in the isolation section.

159
00:09:52,810 --> 00:09:53,410
‫Lecture.

160
00:09:53,800 --> 00:09:56,080
‫Stay tuned for that.

161
00:09:56,200 --> 00:09:58,720
‫It's another example of how transaction works.

162
00:09:58,720 --> 00:10:00,100
‫We talked about this in our earlier.

163
00:10:00,310 --> 00:10:03,390
‫That's how this is a table, right account ID.

164
00:10:03,580 --> 00:10:05,020
‫And we have a two fields.

165
00:10:05,020 --> 00:10:08,680
‫I account I.D. and balance the balance for account I.D. one is $1000.

166
00:10:09,010 --> 00:10:16,540
‫The balance for our account ID two is a $500, and we want to send $100 from account one to account

167
00:10:16,540 --> 00:10:19,540
‫two before we proceed with a sly.

168
00:10:19,960 --> 00:10:21,460
‫Build this in your head.

169
00:10:21,730 --> 00:10:23,740
‫How would you build it?

170
00:10:24,010 --> 00:10:28,330
‫Stop this recording and then do it yourself and then move on.

171
00:10:28,600 --> 00:10:36,280
‫Obviously, the slides are available for you if you want to do it yourself, but effectively, here's

172
00:10:36,280 --> 00:10:36,820
‫what do you do.

173
00:10:37,150 --> 00:10:38,470
‫You begin a transaction.

174
00:10:38,530 --> 00:10:39,520
‫Transaction number one.

175
00:10:39,880 --> 00:10:46,030
‫And then we select the balance from account, the table, the account table, where it equal one.

176
00:10:46,330 --> 00:10:48,380
‫So now I have a thousand right.

177
00:10:48,850 --> 00:10:56,080
‫You check, first of all, in order to debit this account one, I need to check if this guy even have

178
00:10:56,080 --> 00:10:57,040
‫enough money, right?

179
00:10:57,370 --> 00:10:58,690
‫We don't want to go in balance.

180
00:10:58,930 --> 00:11:06,730
‫And as part of the rules and the constraints that we put in place, right?

181
00:11:06,880 --> 00:11:11,770
‫You can put this in the application like what we're doing right now and you can put this constraint

182
00:11:11,770 --> 00:11:13,030
‫at the database level.

183
00:11:13,510 --> 00:11:18,580
‫So that says, Hey, this account, this balance should never go negative, for example, because it

184
00:11:18,580 --> 00:11:20,140
‫doesn't make sense to go negative, right?

185
00:11:20,530 --> 00:11:25,060
‫Unless you build your application in a way that negative mean something, right?

186
00:11:25,660 --> 00:11:32,230
‫And if you ever see data that is negative, that is indication of an inconsistent data.

187
00:11:32,470 --> 00:11:37,960
‫And now I'm mentioning a few things off with the offeror properties as you notice, right, as we discuss

188
00:11:37,960 --> 00:11:38,710
‫the transaction.

189
00:11:38,950 --> 00:11:42,490
‫But keep in mind, that's what inconsistency in data means.

190
00:11:42,850 --> 00:11:45,460
‫There are different kind of inconsistency that we're going to mention.

191
00:11:46,120 --> 00:11:46,420
‫All right.

192
00:11:46,780 --> 00:11:47,230
‫I'm good.

193
00:11:48,230 --> 00:11:50,000
‫I have more than $100.

194
00:11:50,360 --> 00:11:57,250
‫Let's debate my account update account, sit balance, sit balance, equal balance minus one minus 100.

195
00:11:57,410 --> 00:11:58,460
‫What I'd equal one.

196
00:11:59,270 --> 00:11:59,990
‫Beautiful.

197
00:12:00,410 --> 00:12:04,130
‫Now we have nine thousand ninety nine thousand nine hundred dollars.

198
00:12:04,540 --> 00:12:07,700
‫No, I want in the same transaction.

199
00:12:08,060 --> 00:12:12,830
‫Update the account two to add $100.

200
00:12:14,880 --> 00:12:15,470
‫There you go.

201
00:12:16,070 --> 00:12:20,660
‫Once we do that, we're happy with the changes, we commit the transaction.

202
00:12:20,960 --> 00:12:28,490
‫So both of these are actually physically written to to the to their desk, right?

203
00:12:28,490 --> 00:12:32,150
‫And we can argue, where is it exactly written?

204
00:12:32,180 --> 00:12:35,990
‫That's a different situation and different discussion.

205
00:12:36,140 --> 00:12:39,080
‫So let's let's do a quick summary on this lecture.

206
00:12:39,200 --> 00:12:40,580
‫What is a transaction?

207
00:12:40,620 --> 00:12:46,700
‫The transaction is a collection of queries that are treated as a single unit to a fork.

208
00:12:46,850 --> 00:12:51,470
‫Transaction could change data, or it could be only read only as well, right?

209
00:12:51,770 --> 00:12:57,370
‫And a transaction is always started.

210
00:12:57,380 --> 00:13:00,350
‫This is something I do mention a transaction is always thought of.

211
00:13:00,350 --> 00:13:06,440
‫When you don't start the transaction, we start one on the in the database section, the debusk so.

212
00:13:06,710 --> 00:13:08,090
‫So if you execute, I don't know.

213
00:13:08,210 --> 00:13:15,080
‫Just a normal update statement or an insert on the on the back end the database start the transaction

214
00:13:15,080 --> 00:13:17,960
‫implicitly and always immediately commits it, right?

215
00:13:18,140 --> 00:13:24,290
‫So we're always in a transaction, whether you like it or not, some transaction are user defined and

216
00:13:24,290 --> 00:13:30,380
‫some are actually built and implicitly defined by the system.

217
00:13:31,010 --> 00:13:33,020
‫I'm going to see on the next lecture.

218
00:13:33,170 --> 00:13:34,190
‫Let's jump there.