1 00:00:00,460 --> 00:00:01,490 This video introduces the concepts 2 00:00:01,970 --> 00:00:04,280 of transactions and interact actions with database systems. 3 00:00:04,760 --> 00:00:06,530 Transactions are a very important concept. 4 00:00:07,590 --> 00:00:08,940 The concept of transactions is 5 00:00:09,160 --> 00:00:11,540 actually motivated by two completely independent concerns. 6 00:00:12,380 --> 00:00:13,090 One has to do with concurrent 7 00:00:13,740 --> 00:00:15,530 access to the database by multiple clients 8 00:00:16,170 --> 00:00:17,130 and the other has to do 9 00:00:17,270 --> 00:00:19,520 with having a system that is resilient to system failures. 10 00:00:20,520 --> 00:00:22,930 So we're going to talk about each of these in detail in turn. 11 00:00:24,160 --> 00:00:24,870 Let's take a look at the software 12 00:00:25,430 --> 00:00:27,190 structure of how database systems are used. 13 00:00:27,800 --> 00:00:29,770 We have the data itself usually stored on disc. 14 00:00:30,440 --> 00:00:31,620 And then we have the database management 15 00:00:32,080 --> 00:00:35,070 system, or DBMS, that controls interactions with the data. 16 00:00:36,020 --> 00:00:37,830 Often there's additional software above 17 00:00:38,120 --> 00:00:41,090 the DBMS, maybe an application server or web server. 18 00:00:42,110 --> 00:00:43,280 And then interacting with that 19 00:00:43,610 --> 00:00:45,120 might be a human user or 20 00:00:45,360 --> 00:00:47,360 additional software, and the 21 00:00:47,720 --> 00:00:49,010 types of operations that these 22 00:00:49,200 --> 00:00:50,350 users or the software will 23 00:00:50,630 --> 00:00:51,410 be issuing to the database 24 00:00:52,280 --> 00:00:53,300 are the things we've looked at such 25 00:00:53,540 --> 00:00:54,600 as select command and sequel 26 00:00:55,020 --> 00:00:56,480 or update commands, creating tables, 27 00:00:57,320 --> 00:01:00,050 creating and dropping indexes, maybe a help command, a delete command. 28 00:01:00,780 --> 00:01:02,420 So each of the 29 00:01:02,670 --> 00:01:04,160 clients or the software applications 30 00:01:05,070 --> 00:01:06,400 will be issuing these types 31 00:01:06,920 --> 00:01:08,340 of commands for the database, and 32 00:01:08,440 --> 00:01:09,740 most importantly, they will be 33 00:01:09,870 --> 00:01:11,380 issuing them concurrently and we 34 00:01:11,580 --> 00:01:12,550 might have, you know, a 35 00:01:12,840 --> 00:01:14,610 database with one user, ten hundreds 36 00:01:15,010 --> 00:01:17,350 or even thousands of users at the same time. 37 00:01:18,670 --> 00:01:19,510 Let's look at the kind of 38 00:01:19,590 --> 00:01:20,440 difficulties that we can get 39 00:01:20,600 --> 00:01:21,900 into when multiple clients are 40 00:01:22,080 --> 00:01:23,940 interacting with a database at the same time. 41 00:01:24,800 --> 00:01:25,970 We'll be focusing mostly on 42 00:01:26,340 --> 00:01:28,540 sequel statements of modifications and 43 00:01:28,650 --> 00:01:30,060 some queries that are being issued by clients. 44 00:01:30,640 --> 00:01:33,620 And we'll look at some different levels where inconsistency can occur. 45 00:01:34,330 --> 00:01:35,270 We're going to focus our first example 46 00:01:35,680 --> 00:01:36,790 just on a single attribute 47 00:01:37,480 --> 00:01:38,560 and how it can have problems when 48 00:01:38,930 --> 00:01:40,830 multiple clients are working on the same attribute. 49 00:01:41,630 --> 00:01:42,460 So let's say we have two clients, 50 00:01:42,890 --> 00:01:44,380 one is issuing a statement that's 51 00:01:44,760 --> 00:01:46,510 increasing Stanford's enrollment by 1000. 52 00:01:47,110 --> 00:01:48,660 The second client, around 53 00:01:48,970 --> 00:01:50,240 the same time is issuing a 54 00:01:50,470 --> 00:01:52,360 statement that's increasing Stanford's enrollment by 1500. 55 00:01:53,070 --> 00:01:54,130 So we have in 56 00:01:54,380 --> 00:01:56,100 the database our college table here. 57 00:01:57,090 --> 00:01:58,350 And somewhere in this college table 58 00:01:58,670 --> 00:02:00,120 we actually have Stanford's enrollment. 59 00:02:01,410 --> 00:02:03,050 Now the way database systems work 60 00:02:03,480 --> 00:02:05,660 is when we are modifying a value in the database . 61 00:02:06,220 --> 00:02:07,830 Effectively, the system first gets 62 00:02:08,100 --> 00:02:09,280 the value, then it modifies 63 00:02:09,900 --> 00:02:11,240 the value, and then it 64 00:02:11,330 --> 00:02:12,900 puts the modified value back in the database. 65 00:02:13,910 --> 00:02:15,640 So here's the value that we're working on. 66 00:02:16,300 --> 00:02:18,080 So, client S1 will 67 00:02:18,420 --> 00:02:20,940 fetch the value, add one thousand to it, and put it back. 68 00:02:21,440 --> 00:02:22,540 Client S2 will do something 69 00:02:22,730 --> 00:02:24,720 similar, but adding 1,500 instead. 70 00:02:26,080 --> 00:02:27,350 Let's suppose Standford starts out 71 00:02:27,580 --> 00:02:29,180 with an enrollment of 15,000 72 00:02:29,310 --> 00:02:31,000 and these two statements are executed concurrently. 73 00:02:32,160 --> 00:02:33,710 What are the final values that we might have? 74 00:02:34,620 --> 00:02:35,640 Well, if one of them runs 75 00:02:35,940 --> 00:02:37,550 before the other completely, then it 76 00:02:37,690 --> 00:02:39,680 will add 2,500 and we will get 17,500. 77 00:02:39,950 --> 00:02:43,770 On the other hand 78 00:02:44,490 --> 00:02:46,410 if they really can interleave their 79 00:02:47,030 --> 00:02:48,840 get, modifies, and puts, then 80 00:02:49,010 --> 00:02:50,380 it is possible that we'll 81 00:02:50,630 --> 00:02:52,320 instead only add a 82 00:02:52,400 --> 00:02:53,900 1,000, because the 1,500 gets 83 00:02:54,340 --> 00:02:56,950 lost, or we could only add 1,500. 84 00:02:57,590 --> 00:02:58,700 So there are three 85 00:02:58,950 --> 00:03:00,680 possible final values if there's 86 00:03:00,930 --> 00:03:03,480 interleaving of the operations that modify the value. 87 00:03:04,040 --> 00:03:04,530 Of course we are going to see 88 00:03:04,880 --> 00:03:06,300 that there are mechanisms in the 89 00:03:06,370 --> 00:03:07,580 database to avoid this, but 90 00:03:07,740 --> 00:03:10,390 I want to motivate the reason that we have to have those mechanisms. 91 00:03:11,730 --> 00:03:12,420 Now let's look at an 92 00:03:12,500 --> 00:03:13,660 example where the inconsistency 93 00:03:14,420 --> 00:03:15,560 actually occurs at the tuple level. 94 00:03:16,340 --> 00:03:17,070 Again, we're going to have two 95 00:03:17,300 --> 00:03:18,220 clients that are both 96 00:03:18,450 --> 00:03:19,400 issuing statements to the database 97 00:03:20,390 --> 00:03:21,760 that will have some conflicting behavior. 98 00:03:22,730 --> 00:03:24,320 In this case they're both modifying 99 00:03:24,870 --> 00:03:27,080 the apply record for the 100 00:03:27,240 --> 00:03:28,640 student with ID 123. 101 00:03:28,980 --> 00:03:30,240 The first client is trying 102 00:03:31,010 --> 00:03:33,080 to change that apply record to have a major that see us. 103 00:03:33,630 --> 00:03:34,930 Well the second is modifying a 104 00:03:34,970 --> 00:03:35,990 different attribute of the 105 00:03:36,060 --> 00:03:38,690 same record, saying that the decision should be yes. 106 00:03:39,670 --> 00:03:42,500 Now let's take a look at again, so we'll have our table here. 107 00:03:43,020 --> 00:03:45,600 And now, in this case, we're looking at an entire tuple. 108 00:03:45,960 --> 00:03:46,870 Let's say that this is 109 00:03:47,310 --> 00:03:49,130 student 123 and so 110 00:03:49,300 --> 00:03:50,450 we have say, the major 111 00:03:51,030 --> 00:03:52,590 in here and the decision in here. 112 00:03:53,430 --> 00:03:55,870 Now I've mentioned already that 113 00:03:55,980 --> 00:03:57,400 the databases tend to use 114 00:03:57,790 --> 00:04:00,470 a sort of get, modify, 115 00:04:01,200 --> 00:04:03,110 put, and we 116 00:04:03,230 --> 00:04:04,070 talked about that at the 117 00:04:04,390 --> 00:04:06,640 attribute level previously, but 118 00:04:07,010 --> 00:04:08,140 in fact the reality is 119 00:04:08,300 --> 00:04:09,610 that it does occur at 120 00:04:09,690 --> 00:04:10,580 the tuple level, in fact sometimes 121 00:04:11,000 --> 00:04:13,520 it occurs at the entire page or disc lock level. 122 00:04:14,310 --> 00:04:15,620 So let's suppose that it 123 00:04:15,780 --> 00:04:17,350 occurs at least at the tuple level. 124 00:04:18,090 --> 00:04:18,990 So, again, we can see the 125 00:04:19,270 --> 00:04:20,500 same problem, then, when we 126 00:04:20,600 --> 00:04:22,100 have the two clients. 127 00:04:22,540 --> 00:04:23,580 Each will be performing 128 00:04:24,160 --> 00:04:25,570 a get, modify, put if they 129 00:04:25,760 --> 00:04:27,570 do it interleaved, then it's 130 00:04:27,760 --> 00:04:29,390 possible that we will see both changes. 131 00:04:30,050 --> 00:04:32,070 Whoops, then it 132 00:04:32,190 --> 00:04:33,960 is possible that we'll see both changes. 133 00:04:34,570 --> 00:04:36,110 We'll correctly get the 134 00:04:36,170 --> 00:04:37,250 major reset and the 135 00:04:37,310 --> 00:04:38,550 decision reset but it's 136 00:04:38,720 --> 00:04:40,120 also possible we'd only 137 00:04:40,190 --> 00:04:42,240 see one of the two changes and it could be either one. 138 00:04:43,210 --> 00:04:44,680 So again, we need some mechanism 139 00:04:45,020 --> 00:04:45,990 to insure that we have consistent 140 00:04:46,600 --> 00:04:48,860 updates to the database that would give us what we expect. 141 00:04:49,570 --> 00:04:50,640 In this case, we would probably 142 00:04:51,080 --> 00:04:54,120 expect both changes to be persistent in the database. 143 00:04:55,360 --> 00:04:57,890 Now, let's go one step further and look at table level inconsistency. 144 00:04:59,200 --> 00:05:00,040 Again we have our two clients, 145 00:05:01,300 --> 00:05:03,560 and they're submitting statements to the database around the same time. 146 00:05:04,270 --> 00:05:06,030 One of them is 147 00:05:06,390 --> 00:05:07,650 modifying the applied table 148 00:05:08,290 --> 00:05:09,440 and it's setting the decision for 149 00:05:09,540 --> 00:05:11,270 applications TS for every 150 00:05:11,540 --> 00:05:13,260 student who's GPA is greater than 3.9. 151 00:05:14,590 --> 00:05:16,000 The other statement, occurring at about 152 00:05:16,130 --> 00:05:17,760 the same time, is modifying the 153 00:05:17,830 --> 00:05:19,650 student table; we've decided to 154 00:05:19,880 --> 00:05:21,090 increase the GPA of every 155 00:05:21,370 --> 00:05:22,760 student who comes from a large high school. 156 00:05:23,540 --> 00:05:25,110 So here we have the apply table. 157 00:05:26,710 --> 00:05:27,910 We have the student table. 158 00:05:30,410 --> 00:05:32,630 We have the first 159 00:05:32,980 --> 00:05:34,770 client S1 working on 160 00:05:34,960 --> 00:05:36,240 the apply table, but the 161 00:05:36,800 --> 00:05:38,100 conditions in which apply 162 00:05:38,460 --> 00:05:41,580 tables are updated depend on probing the student table. 163 00:05:42,400 --> 00:05:43,890 Meanwhile we have client S2 164 00:05:44,560 --> 00:05:46,180 that's modifying the student table. 165 00:05:46,910 --> 00:05:48,230 So what happens in the 166 00:05:48,590 --> 00:05:50,780 apply table can depend on 167 00:05:51,100 --> 00:05:52,580 whether it occurs before or 168 00:05:52,700 --> 00:05:53,700 after or during the modifications 169 00:05:54,390 --> 00:05:55,420 of the student table. 170 00:05:56,080 --> 00:05:57,210 So you know, students would certainly 171 00:05:57,520 --> 00:05:58,900 prefer if their GPA is 172 00:05:59,130 --> 00:06:01,100 increased before the apply 173 00:06:01,490 --> 00:06:03,920 records are automatically accepted based on the GPA. 174 00:06:04,050 --> 00:06:05,970 So again, a notion of 175 00:06:06,130 --> 00:06:07,790 consistency here would be 176 00:06:07,940 --> 00:06:09,580 that we understand that either 177 00:06:09,870 --> 00:06:11,700 all the GPA's are modified first 178 00:06:12,210 --> 00:06:13,400 and then the acceptances are made 179 00:06:13,780 --> 00:06:14,910 or vice versa and we'll 180 00:06:15,000 --> 00:06:16,210 again see mechanisms that will 181 00:06:16,360 --> 00:06:18,220 help us enforce that. 182 00:06:18,410 --> 00:06:19,520 As a final example we'll consider 183 00:06:20,100 --> 00:06:22,040 clients that are interacting with multiple tables again. 184 00:06:22,660 --> 00:06:23,500 But in this case we 185 00:06:23,550 --> 00:06:26,320 also have multiple statements that are playing into the situation. 186 00:06:27,020 --> 00:06:28,350 Specifically we'll see an example, 187 00:06:28,940 --> 00:06:30,230 where we will want a 188 00:06:30,380 --> 00:06:32,310 one statement to not occur 189 00:06:32,920 --> 00:06:35,480 concurrently even between the statements from another client. 190 00:06:35,840 --> 00:06:36,790 So let me just show you the example. 191 00:06:37,270 --> 00:06:38,390 So here again we have two clients. 192 00:06:38,790 --> 00:06:39,870 I'll label them C1 and C2 193 00:06:40,030 --> 00:06:42,720 now, since the first one has two statements. 194 00:06:43,970 --> 00:06:45,090 So what the first client is 195 00:06:45,210 --> 00:06:46,510 doing is moving records from 196 00:06:46,700 --> 00:06:48,920 the apply table to an archive table. 197 00:06:49,290 --> 00:06:51,020 Specifically, it looks in 198 00:06:51,250 --> 00:06:52,600 the apply table for records 199 00:06:53,030 --> 00:06:54,470 where the decision is no 200 00:06:55,100 --> 00:06:56,420 and it inserts those into an archive. 201 00:06:57,320 --> 00:06:58,900 And then in a second statement, and 202 00:06:59,030 --> 00:06:59,790 this is really the only way 203 00:06:59,840 --> 00:07:02,560 to do it, it deletes those tuples from the apply relation. 204 00:07:03,770 --> 00:07:05,320 The second statement... the second 205 00:07:05,670 --> 00:07:07,170 client, I'm sorry, just happens 206 00:07:07,430 --> 00:07:08,480 to want to count the number 207 00:07:08,740 --> 00:07:09,690 of tuples in the apply 208 00:07:10,140 --> 00:07:11,370 table and in the the archive table. 209 00:07:12,170 --> 00:07:13,270 So again we have the 210 00:07:13,400 --> 00:07:14,430 two tables that are... we're 211 00:07:14,630 --> 00:07:16,200 concerned with, the apply table 212 00:07:17,190 --> 00:07:20,080 and the archive table and what 213 00:07:20,540 --> 00:07:22,250 the first client is 214 00:07:22,650 --> 00:07:23,680 doing, is it's moving 215 00:07:24,230 --> 00:07:25,470 some tuples from apply to 216 00:07:25,610 --> 00:07:26,960 archive, and then in 217 00:07:27,140 --> 00:07:29,260 a second statement deleting those tuples from apply. 218 00:07:30,610 --> 00:07:32,470 Our second client-- I'll 219 00:07:32,720 --> 00:07:34,250 put this one in red--is counting 220 00:07:35,170 --> 00:07:37,600 from the apply and then counting from the archive. 221 00:07:38,520 --> 00:07:39,660 Now if we want the 222 00:07:39,740 --> 00:07:40,780 second client to see sort 223 00:07:40,800 --> 00:07:42,630 of a consistent state of 224 00:07:42,740 --> 00:07:43,850 the database, where we don't 225 00:07:44,130 --> 00:07:45,430 have records that are duplicated between 226 00:07:45,720 --> 00:07:47,180 apply and archive, then we 227 00:07:47,320 --> 00:07:48,720 will really want that second 228 00:07:49,490 --> 00:07:50,460 client to go either completely 229 00:07:50,960 --> 00:07:53,290 before, or completely after, the first client. 230 00:07:54,450 --> 00:07:55,680 So after that long sequence of 231 00:07:55,800 --> 00:07:56,930 examples, I hope you 232 00:07:57,020 --> 00:07:58,610 get the feeling of what we're looking for in concurrency. 233 00:07:59,790 --> 00:08:01,100 We have multiple clients interacting 234 00:08:01,560 --> 00:08:02,390 with the database at the same 235 00:08:02,640 --> 00:08:04,140 time, and if they 236 00:08:04,300 --> 00:08:06,370 have true interleaving of the 237 00:08:06,890 --> 00:08:08,300 commands that they're executing on 238 00:08:08,430 --> 00:08:09,880 the database, often update commands 239 00:08:10,270 --> 00:08:11,440 but some select commands as well, 240 00:08:12,060 --> 00:08:14,310 then we may get inconsistent or unexpected behavior. 241 00:08:15,300 --> 00:08:16,280 So what we'd like to have 242 00:08:16,670 --> 00:08:17,910 overall is the ability for 243 00:08:18,040 --> 00:08:19,540 clients to execute statements 244 00:08:19,890 --> 00:08:21,040 against the database and not 245 00:08:21,280 --> 00:08:23,730 have to worry about what other clients are doing at the same time. 246 00:08:24,390 --> 00:08:25,280 Specifically, a little more 247 00:08:25,470 --> 00:08:26,580 generally, we like the class 248 00:08:26,820 --> 00:08:27,740 to be able to execute a sequence 249 00:08:28,490 --> 00:08:30,110 of sequel statements, so that 250 00:08:30,310 --> 00:08:32,080 the client can at least act 251 00:08:32,260 --> 00:08:33,760 like those statements are running in isolation. 252 00:08:35,060 --> 00:08:37,280 Now there's one obvious solution to do this, right? 253 00:08:37,690 --> 00:08:38,990 Why don't we just execute them in isolation? 254 00:08:39,770 --> 00:08:40,900 This database system can take 255 00:08:41,150 --> 00:08:42,470 its client requests and just 256 00:08:42,720 --> 00:08:44,190 do them one at a time with no concurrency. 257 00:08:45,820 --> 00:08:48,780 On the other hand we really do want to enable concurrency whenever we can. 258 00:08:49,610 --> 00:08:51,090 Database systems are geared 259 00:08:51,390 --> 00:08:52,610 towards providing the highest possible 260 00:08:53,110 --> 00:08:55,620 performance, we talked about that way in the introduction into the course. 261 00:08:56,750 --> 00:08:58,180 And they typically do operate it 262 00:08:58,440 --> 00:09:00,370 in an environment where concurrency is possible. 263 00:09:00,950 --> 00:09:02,890 They may be working on a multi-processor system. 264 00:09:04,050 --> 00:09:06,420 They may be using even a multi-threaded system. 265 00:09:08,000 --> 00:09:09,630 And database systems, also as 266 00:09:09,740 --> 00:09:11,010 they access the database, tend to 267 00:09:11,090 --> 00:09:12,060 do a whole bunch of I/O so 268 00:09:13,050 --> 00:09:14,390 a system that provides asynchronous 269 00:09:15,130 --> 00:09:17,930 I/O can also run multiple things concurrently. 270 00:09:18,400 --> 00:09:21,390 It can do one thing while it's waiting for data to be fetched by another. 271 00:09:22,280 --> 00:09:23,390 So assuming that system 272 00:09:23,880 --> 00:09:25,370 supports concurrency, then we 273 00:09:25,510 --> 00:09:28,080 would like the database software to support it as well. 274 00:09:29,070 --> 00:09:31,020 As a very simple example, let's suppose 275 00:09:31,430 --> 00:09:32,690 we have five clients that are 276 00:09:32,780 --> 00:09:34,020 operating on the database and 277 00:09:34,260 --> 00:09:34,980 each one of them is operating 278 00:09:35,570 --> 00:09:36,990 on a completely different part of 279 00:09:37,130 --> 00:09:38,140 the database, and we certainly 280 00:09:38,540 --> 00:09:39,640 wouldn't want to force them 281 00:09:39,890 --> 00:09:41,780 to execute sequentially when they 282 00:09:41,950 --> 00:09:43,800 could execute in parallel without 283 00:09:44,660 --> 00:09:45,990 causing any inconsistency problems. 284 00:09:47,550 --> 00:09:49,910 Let's switch gears entirely now and talk about system failures. 285 00:09:50,630 --> 00:09:51,530 Again, we have our database system 286 00:09:52,010 --> 00:09:52,930 working with the data on disc, 287 00:09:53,020 --> 00:09:54,240 and let's suppose we just 288 00:09:54,410 --> 00:09:56,640 happen to be in the process of bulk loading our database. 289 00:09:57,140 --> 00:09:58,180 Maybe bringing in a large 290 00:09:58,510 --> 00:09:59,460 amount of data from an 291 00:09:59,970 --> 00:10:01,020 external source, say a set of 292 00:10:01,150 --> 00:10:02,700 files, and right in 293 00:10:02,920 --> 00:10:03,870 the middle of that bulk load 294 00:10:04,100 --> 00:10:06,400 we have a system crash or a system failure. 295 00:10:07,360 --> 00:10:08,620 It could be a software failure, 296 00:10:09,240 --> 00:10:10,470 could be a hardware failure, could 297 00:10:10,600 --> 00:10:12,260 be as simple as the power going out. 298 00:10:12,910 --> 00:10:14,480 So, if the database is, 299 00:10:14,760 --> 00:10:16,420 at that point, half loaded, so 300 00:10:16,590 --> 00:10:17,930 let's say half of this data 301 00:10:18,260 --> 00:10:19,150 has made its way to disc 302 00:10:19,550 --> 00:10:21,710 and the other half hasn't, what happens 303 00:10:22,080 --> 00:10:23,310 when the system comes back up? 304 00:10:23,500 --> 00:10:26,320 That leaves us in a rather unpleasant inconsistent state. 305 00:10:27,820 --> 00:10:28,950 Or maybe we're just executing 306 00:10:29,420 --> 00:10:30,290 some commands on an existing 307 00:10:30,750 --> 00:10:32,030 data, remember our example 308 00:10:32,540 --> 00:10:33,450 earlier, where we were moving 309 00:10:34,540 --> 00:10:37,390 tuples from an apply relation to an archive. 310 00:10:37,890 --> 00:10:39,380 So we have our relations here. 311 00:10:40,000 --> 00:10:41,280 We're in the process of moving some 312 00:10:41,450 --> 00:10:42,250 data and then once it's moved, 313 00:10:42,660 --> 00:10:44,140 we're going to delete the 314 00:10:44,200 --> 00:10:45,730 data that we moved, and then 315 00:10:45,940 --> 00:10:46,850 all of a sudden once again 316 00:10:47,140 --> 00:10:48,190 we have a crash or a 317 00:10:48,420 --> 00:10:50,380 failure of some type right in the middle of that move. 318 00:10:51,290 --> 00:10:52,340 So what do we do now? 319 00:10:52,670 --> 00:10:53,710 When the system comes up, how 320 00:10:53,990 --> 00:10:55,910 do we know what was moved and what wasn't? 321 00:10:57,290 --> 00:10:58,410 As a last example, let's just 322 00:10:58,650 --> 00:11:00,990 suppose we were performing a whole bunch of updates on the database. 323 00:11:01,820 --> 00:11:03,090 And as a reminder the way database 324 00:11:03,500 --> 00:11:04,670 systems perform updates is they 325 00:11:04,810 --> 00:11:06,680 bring some data from the disc into the memory. 326 00:11:07,180 --> 00:11:08,070 They modify it in memory 327 00:11:08,630 --> 00:11:10,270 and then eventually they write it back to disc. 328 00:11:10,930 --> 00:11:12,050 So let's suppose in the middle 329 00:11:12,300 --> 00:11:13,960 of that process again we have a system crash. 330 00:11:14,550 --> 00:11:16,730 That would again leave the database in an inconsistent state. 331 00:11:17,990 --> 00:11:19,350 So our overall goal for dealing 332 00:11:19,600 --> 00:11:21,090 with system failures is that 333 00:11:21,210 --> 00:11:22,360 when we want to do something 334 00:11:22,950 --> 00:11:24,280 on the database that needs 335 00:11:24,590 --> 00:11:25,350 to be done in an all or nothing 336 00:11:25,790 --> 00:11:26,950 fashion, we'd like to tell 337 00:11:27,180 --> 00:11:28,380 the system that we want to 338 00:11:28,570 --> 00:11:29,540 guarantee all or nothing execution 339 00:11:30,250 --> 00:11:31,580 for that particular set of 340 00:11:31,680 --> 00:11:33,200 operations on the database regardless 341 00:11:33,960 --> 00:11:36,050 of failures that might occur during the execution. 342 00:11:36,840 --> 00:11:38,130 So we've talked about problems 343 00:11:38,560 --> 00:11:39,730 with concurrency, we've talked about 344 00:11:39,980 --> 00:11:41,470 problems with system failures, and 345 00:11:42,040 --> 00:11:43,720 interestingly the exact same mechanism 346 00:11:44,330 --> 00:11:46,510 can be used to deal with both of those issues, 347 00:11:46,710 --> 00:11:49,150 and that mechanism is not surprisingly transactions. 348 00:11:51,020 --> 00:11:52,810 So overall, a transaction is 349 00:11:52,970 --> 00:11:53,790 a sequence of one or more 350 00:11:53,990 --> 00:11:55,480 operations that are treated as a unit. 351 00:11:56,170 --> 00:11:58,160 Specifically, each transaction appears 352 00:11:58,670 --> 00:12:00,300 to run in isolation, and furthermore, 353 00:12:00,960 --> 00:12:02,730 if the system fails, each transaction 354 00:12:03,530 --> 00:12:05,240 is either executed in its 355 00:12:05,760 --> 00:12:07,460 entirety or not all. 356 00:12:08,230 --> 00:12:08,960 In terms of the SQL 357 00:12:09,340 --> 00:12:10,930 standard, a transaction begins 358 00:12:11,290 --> 00:12:13,860 SQL statement is issued by a client to the database. 359 00:12:15,080 --> 00:12:16,610 When the commit command is 360 00:12:16,730 --> 00:12:17,940 issued that's a special key 361 00:12:18,130 --> 00:12:20,970 word, the current transaction ends and a new one begins. 362 00:12:22,350 --> 00:12:23,980 The current transaction also ends 363 00:12:24,240 --> 00:12:25,860 when its session with the database terminates. 364 00:12:26,710 --> 00:12:27,900 And finally there is a mode 365 00:12:28,280 --> 00:12:29,780 called autocommit and in this 366 00:12:29,990 --> 00:12:31,370 mode each statement, each SQL 367 00:12:31,660 --> 00:12:34,300 statement, is executed as a transaction on its own. 368 00:12:35,410 --> 00:12:38,140 So for each client, we can actually think about a time-line 369 00:12:38,400 --> 00:12:39,630 and the client will be 370 00:12:39,760 --> 00:12:40,920 executing along, it might 371 00:12:41,220 --> 00:12:42,230 say commit and that will 372 00:12:42,360 --> 00:12:44,430 commit anything that happened prior to this point. 373 00:12:45,180 --> 00:12:46,590 Then it might do some operations, maybe 374 00:12:46,820 --> 00:12:48,620 a select, an update, a 375 00:12:48,680 --> 00:12:49,730 delete, and then it 376 00:12:49,850 --> 00:12:51,890 says commit again, and when 377 00:12:52,110 --> 00:12:53,130 it says commit at this point 378 00:12:53,700 --> 00:12:55,160 that turns this amount of 379 00:12:55,280 --> 00:12:57,810 work into a single transaction that's treated as a unit. 380 00:12:58,630 --> 00:13:00,000 Maybe it'll update, maybe it'll 381 00:13:00,140 --> 00:13:02,090 create an index, and again 382 00:13:02,720 --> 00:13:05,250 maybe commit at this point in time 383 00:13:05,750 --> 00:13:06,880 and that, right here, will 384 00:13:07,160 --> 00:13:08,350 turn this into a transaction. 385 00:13:09,290 --> 00:13:10,250 And so we can see 386 00:13:10,530 --> 00:13:11,500 for each client, this is 387 00:13:11,590 --> 00:13:13,130 Client 1, their execution 388 00:13:14,190 --> 00:13:15,530 on the database is seen as 389 00:13:15,660 --> 00:13:18,610 a sequence of transactions, each of which has the properties that we describe. 390 00:13:19,120 --> 00:13:20,830 And then we may have a 391 00:13:21,240 --> 00:13:22,920 second client, that's also 392 00:13:23,280 --> 00:13:24,410 operating on the database, and 393 00:13:24,520 --> 00:13:26,060 then it will also have its set of transactions. 394 00:13:27,260 --> 00:13:28,510 In the next video, we'll give 395 00:13:28,740 --> 00:13:29,960 more details and a 396 00:13:30,080 --> 00:13:31,690 more formal treatment on the 397 00:13:31,840 --> 00:13:33,260 properties that are guaranteed by 398 00:13:33,430 --> 00:13:34,730 transactions, both how they 399 00:13:35,020 --> 00:13:36,500 operate individually and how 400 00:13:36,630 --> 00:13:37,930 they interact when multiple clients 401 00:13:38,460 --> 00:13:39,590 are executing on the database at 402 00:13:39,700 --> 00:13:41,480 the same time or when the system fails.