1
00:00:00,090 --> 00:00:09,210
‫That's almost he is one of the four acid properties that defines a relational database management system.

2
00:00:09,210 --> 00:00:17,100
‫And I always say relational, but really it defines any database system, whether it's now SQL graph,

3
00:00:17,220 --> 00:00:19,770
‫time based, really anything.

4
00:00:19,770 --> 00:00:26,580
‫And Thomas, it is a very critical concept to understand how about we jump into this lecture and discuss

5
00:00:26,580 --> 00:00:28,320
‫this beautiful concept?

6
00:00:28,320 --> 00:00:30,060
‫So talk about automaticity.

7
00:00:30,060 --> 00:00:35,370
‫Really all the queries in a transaction must succeed.

8
00:00:35,370 --> 00:00:39,030
‫That is the definition of automaticity.

9
00:00:39,030 --> 00:00:44,400
‫We talked about what a transaction is really in that transaction lecture, and we said that all the

10
00:00:44,400 --> 00:00:49,290
‫queries in a given transaction is considered to be one unit of work.

11
00:00:49,440 --> 00:00:53,430
‫And that kind of defines what, what, what automaticity really is, right?

12
00:00:53,460 --> 00:00:54,930
‫It's like an atom.

13
00:00:54,930 --> 00:01:00,960
‫And until now, an atom cannot be split.

14
00:01:00,960 --> 00:01:08,160
‫I know there is nuclear fission that kind of contradicts that, but this thing was defined in 1970 and

15
00:01:08,160 --> 00:01:10,980
‫I don't believe they discovered fission back then.

16
00:01:10,980 --> 00:01:14,190
‫I might be wrong, but that's the way that automaticity comes into aura.

17
00:01:14,250 --> 00:01:16,950
‫You treat this as an atom that cannot be split.

18
00:01:17,100 --> 00:01:22,230
‫All the queries in a transaction must succeed if one query fail.

19
00:01:22,230 --> 00:01:25,560
‫What happens if one query failed for any reason?

20
00:01:26,220 --> 00:01:34,200
‫A failed constraint like the balance goes to minus one, minus negative, or a duplicate primary key

21
00:01:34,200 --> 00:01:35,670
‫entry, right?

22
00:01:36,000 --> 00:01:40,260
‫Or an actual invalid SQL syntax.

23
00:01:41,990 --> 00:01:43,670
‫That is a failed query.

24
00:01:43,670 --> 00:01:46,010
‫And what happens when you have one single?

25
00:01:46,040 --> 00:01:56,450
‫Even if you had 100 successful queries in the same transaction, that one failure should roll back the

26
00:01:56,450 --> 00:02:00,710
‫transaction immediately because I said things went wrong.

27
00:02:00,740 --> 00:02:01,880
‫That's the rule of the.

28
00:02:03,590 --> 00:02:04,580
‫So what happened?

29
00:02:04,580 --> 00:02:10,760
‫If the database goes down during my transaction, I can't really roll back because the database goes

30
00:02:10,760 --> 00:02:11,360
‫down.

31
00:02:11,390 --> 00:02:12,890
‫What happened in this case?

32
00:02:13,190 --> 00:02:17,750
‫If the database went down prior to a commit, you didn't really commit the transaction.

33
00:02:18,500 --> 00:02:23,360
‫All the successful queries and the transactional should be rolled back.

34
00:02:23,390 --> 00:02:23,800
‫All right.

35
00:02:23,810 --> 00:02:30,200
‫Even if you had 100 successful queries in this transaction and you didn't really fail, you didn't fail,

36
00:02:30,200 --> 00:02:32,810
‫but the database just crashed.

37
00:02:33,260 --> 00:02:33,560
‫All right.

38
00:02:33,560 --> 00:02:35,480
‫So the next query didn't really fail.

39
00:02:36,110 --> 00:02:38,000
‫So the database will restart.

40
00:02:38,150 --> 00:02:47,450
‫So once the database restarts, that's when the database should detect that, hey, there was a failure.

41
00:02:47,600 --> 00:02:51,530
‫Technically, right, because my queries didn't fail.

42
00:02:51,530 --> 00:02:58,160
‫But the database went down and I was made a transaction and I didn't commit these changes made by the

43
00:02:58,160 --> 00:02:59,180
‫transaction.

44
00:02:59,240 --> 00:03:03,080
‫If the database is actually making changes, it should be rolled back.

45
00:03:03,260 --> 00:03:09,020
‫That's why I always want you to think about this as as you read this, as I as I talk.

46
00:03:09,740 --> 00:03:12,770
‫What is the database doing in a transaction?

47
00:03:12,770 --> 00:03:14,960
‫Is it actually writing to desk?

48
00:03:15,200 --> 00:03:15,470
‫Right.

49
00:03:15,800 --> 00:03:18,710
‫I'm not going to commit if what if I don't I don't commit.

50
00:03:18,740 --> 00:03:21,860
‫Do we go back to disk and remove these changes?

51
00:03:22,130 --> 00:03:27,980
‫All of this thing really is what makes or breaks a database.

52
00:03:28,250 --> 00:03:35,760
‫And this goes back to most of the questions I receive really is what database should I choose?

53
00:03:35,780 --> 00:03:39,170
‫Well, really, you need to understand how these databases work.

54
00:03:39,170 --> 00:03:44,570
‫And based on your use case, make a choice.

55
00:03:44,780 --> 00:03:52,820
‫There are databases that can be for comments, any right you make right in the in the queries and the

56
00:03:52,820 --> 00:03:54,710
‫transaction they are actually writing to disk.

57
00:03:54,710 --> 00:03:57,290
‫So they they assume you're going to commit.

58
00:03:57,680 --> 00:04:00,500
‫So they are optimistic in that nature.

59
00:04:00,680 --> 00:04:06,860
‫So when you actually, in fact commit, I don't need to do any work because I committed I actually wrote

60
00:04:06,860 --> 00:04:07,700
‫everything to disk.

61
00:04:07,730 --> 00:04:16,190
‫It will just write one one bit that says this transaction is committed and it's so fast.

62
00:04:16,790 --> 00:04:17,300
‫Right.

63
00:04:18,080 --> 00:04:21,470
‫Although the database is doesn't don't write anything.

64
00:04:21,470 --> 00:04:26,240
‫So the queries are so fast executing because they always write to memory.

65
00:04:26,240 --> 00:04:32,390
‫But the moment you want to commit, they have to go take everything that is in memory and then flushes

66
00:04:32,390 --> 00:04:36,740
‫to disk and the commit will be slow in that regard.

67
00:04:36,740 --> 00:04:40,160
‫But the roll back will be as absolutely fast.

68
00:04:41,480 --> 00:04:43,730
‫You know, it all depends.

69
00:04:44,780 --> 00:04:46,040
‫There is no right or wrong.

70
00:04:46,050 --> 00:04:47,260
‫There is always a trade off.

71
00:04:47,900 --> 00:04:50,900
‫Second example, this is an example I took in.

72
00:04:51,080 --> 00:04:55,700
‫I talk about in my transaction lecture at Thomas.

73
00:04:56,870 --> 00:05:02,080
‫So I have the same table account table balanced 2500 Account Idea one two.

74
00:05:02,090 --> 00:05:06,230
‫We want to send money from $100 from account one to account two.

75
00:05:06,260 --> 00:05:07,100
‫What do we do?

76
00:05:07,340 --> 00:05:08,240
‫Very simple.

77
00:05:08,240 --> 00:05:10,940
‫We need to debit this account and credit this account.

78
00:05:10,940 --> 00:05:13,840
‫So -100 this, plus 100 this.

79
00:05:13,850 --> 00:05:18,170
‫And we have to do it in a transaction because these two should happen at the same time.

80
00:05:18,170 --> 00:05:18,590
‫Right.

81
00:05:20,210 --> 00:05:26,000
‫So we start the transaction, we select the balance to check if this guy even have money.

82
00:05:26,000 --> 00:05:30,560
‫And they do because the balance is greater than 100 because it's 1000, right?

83
00:05:30,560 --> 00:05:31,850
‫Because that's the account one.

84
00:05:31,850 --> 00:05:34,360
‫So yeah, let's go in and update the account.

85
00:05:34,370 --> 00:05:37,040
‫Let's deduct 100 and.

86
00:05:38,070 --> 00:05:38,790
‫And when

87
00:05:41,670 --> 00:05:50,940
‫database crashed, I did one change and the second change I wanted to do, but the database crashed.

88
00:05:52,530 --> 00:05:58,890
‫We restarted the database and now let's assume this is a bad, badly implemented database system.

89
00:05:59,220 --> 00:06:01,590
‫This is what we see when we restart the system.

90
00:06:02,010 --> 00:06:03,600
‫We see 900 here.

91
00:06:05,400 --> 00:06:06,750
‫Where what?

92
00:06:07,830 --> 00:06:08,740
‫What happened?

93
00:06:08,760 --> 00:06:13,200
‫So in this case, we just lost $100 in thin air.

94
00:06:14,100 --> 00:06:14,640
‫All right.

95
00:06:14,640 --> 00:06:18,300
‫That's a really bad that's really bad.

96
00:06:18,330 --> 00:06:19,560
‫Inconsistent.

97
00:06:19,560 --> 00:06:21,110
‫This is an inconsistent view.

98
00:06:21,120 --> 00:06:27,990
‫Not only you just lost actual money that you you have no idea where where it gone because of technology

99
00:06:27,990 --> 00:06:28,530
‫effectively.

100
00:06:28,530 --> 00:06:28,860
‫Right.

101
00:06:28,950 --> 00:06:33,840
‫So after we restarted the machine, the first account has been debited by the other account has not

102
00:06:33,840 --> 00:06:34,640
‫been credited.

103
00:06:34,650 --> 00:06:36,780
‫Very bad, inconsistent view.

104
00:06:36,810 --> 00:06:41,700
‫So lack of automaticity leads to inconsistencies.

105
00:06:41,700 --> 00:06:45,960
‫And we're going to talk about consistencies in its own lecture.

106
00:06:48,540 --> 00:06:49,260
‫This is really bad.

107
00:06:49,260 --> 00:06:50,130
‫We just lost data.

108
00:06:50,220 --> 00:06:51,420
‫The information is inconsistent.

109
00:06:51,420 --> 00:06:52,170
‫We talked about that.

110
00:06:52,890 --> 00:06:58,470
‫Anatomic transaction is a transaction that will roll back all the queries if one or more queries fail.

111
00:06:58,470 --> 00:07:05,280
‫Well, in this situation, yeah, there is a failure, but it's not really our fault because my other

112
00:07:05,280 --> 00:07:09,180
‫transaction, my other query sorry, didn't even execute.

113
00:07:09,330 --> 00:07:10,800
‫So the database got restored.

114
00:07:10,800 --> 00:07:13,170
‫So how do I know to roll back?

115
00:07:13,170 --> 00:07:14,100
‫I don't know.

116
00:07:14,130 --> 00:07:16,680
‫The database didn't even give it a chance to roll back.

117
00:07:16,680 --> 00:07:22,440
‫I can issue the command rollback back so the database now is responsible to clean up this garbage.

118
00:07:22,440 --> 00:07:23,430
‫It was okay.

119
00:07:23,430 --> 00:07:29,880
‫There was a in a running transaction and he has to know that there is a transaction that has been running

120
00:07:29,880 --> 00:07:31,110
‫and it was crashed.

121
00:07:31,110 --> 00:07:31,620
‫Right.

122
00:07:31,620 --> 00:07:34,800
‫So upon restart we need to clean up.

123
00:07:34,920 --> 00:07:35,370
‫Right.

124
00:07:36,450 --> 00:07:42,000
‫And let me let me tell you from my professional experience, right, I started working professionally

125
00:07:42,000 --> 00:07:50,910
‫back in 2005 and I've seen roll back that takes an hour, over an hour actually for long transactions,

126
00:07:50,910 --> 00:07:52,110
‫especially in SQL Server.

127
00:07:52,110 --> 00:07:57,240
‫But I've seen rollbacks not actual roll back commit by the user.

128
00:07:57,240 --> 00:08:04,500
‫No, actually the database says, wait, I can't even start the database before I roll back this this

129
00:08:04,500 --> 00:08:09,150
‫stuff because it has to go and clean up all the garbage that has been written.

130
00:08:09,150 --> 00:08:11,190
‫This half garbage that has been written.

131
00:08:11,190 --> 00:08:11,580
‫Right.

132
00:08:11,580 --> 00:08:13,590
‫And this is the garbage has been written here.

133
00:08:13,590 --> 00:08:18,150
‫So we wrote that debits one account -100.

134
00:08:18,150 --> 00:08:22,170
‫We need to change that 900 back to 1000 to get a consistent view.

135
00:08:22,380 --> 00:08:24,560
‫And that's where all play some database.

136
00:08:24,560 --> 00:08:27,860
‫I won't let you start the database before it's rolled back.

137
00:08:28,350 --> 00:08:35,310
‫They they in recent patches and recent releases now say okay it's not really that bad, let them roll

138
00:08:35,310 --> 00:08:38,910
‫rollback and let them continue working on other tables or other databases.

139
00:08:39,060 --> 00:08:42,870
‫But this database now is just it's in a roll back state.

140
00:08:43,050 --> 00:08:46,170
‫So CPU is getting hammered, right?

141
00:08:46,170 --> 00:08:47,700
‫Memory is getting hammered.

142
00:08:48,060 --> 00:08:49,440
‫All these things really.

143
00:08:49,890 --> 00:08:56,310
‫As a result, the database should clean this up after we talked about this and we talked about how long

144
00:08:56,310 --> 00:08:57,210
‫this could take.

145
00:08:57,450 --> 00:08:57,840
‫Right.

146
00:08:58,260 --> 00:09:02,640
‫And really, it depends on how much you wrote and how long was the transaction.

147
00:09:02,670 --> 00:09:06,420
‫Long and transactions is generally a bad idea because of these things.

148
00:09:06,680 --> 00:09:08,830
‫Let's do a quick summary on the terms of the items.

149
00:09:08,850 --> 00:09:14,700
‫It is the idea of the transaction being one unit of work and that cannot be split.

150
00:09:14,700 --> 00:09:16,380
‫That's the property of an item.

151
00:09:16,420 --> 00:09:16,950
‫Please.

152
00:09:17,130 --> 00:09:25,110
‫At least back in 1970, if they figured efficient, nuclear efficient, then this, this, this, this

153
00:09:25,110 --> 00:09:27,090
‫is broken effectively.

154
00:09:27,090 --> 00:09:28,310
‫But you get idea, right?

155
00:09:28,320 --> 00:09:33,120
‫So you cannot really an atom should be one unit and cannot be split.

156
00:09:33,120 --> 00:09:33,330
‫Right.

157
00:09:33,360 --> 00:09:38,580
‫So if if you have a transaction with 100 queries, all these 100 quests should succeed.

158
00:09:38,580 --> 00:09:44,550
‫If any of these 100 query fail, all the hundred or anything that is successful in the query should

159
00:09:44,550 --> 00:09:45,300
‫be rolled back.

160
00:09:45,300 --> 00:09:50,160
‫And not only that, but you should even in the case of a crash in the database.

161
00:09:50,820 --> 00:09:53,790
‫So that's not an actual explicit failure by the user.

162
00:09:54,120 --> 00:09:57,840
‫That should also roll back the changes.

163
00:09:58,500 --> 00:10:00,030
‫That was that tomasetti lecture.

164
00:10:00,300 --> 00:10:02,910
‫I'm going to see you in the next lecture.

165
00:10:03,420 --> 00:10:03,930
‫Stay tuned.