Introduction to Transactions3:14 with Andrew Chalkley
When seeding or populating a database for the first time, you will have lots of data to add. But what happens when there's an error in the middle of that process?
Autocommit - every statement you write gets saved to disk.
Seeding - populating a database for the first time.
Script file - a file containing SQL statements.
Switch autocommit off and begin a transaction:
To save all results of the statements after the start of the transaction to disk:
See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.
[MUSIC] 0:00 Let's talk about commitment. 0:04 When you're in a relationship with a database, 0:06 you need to be committed, especially when you're modifying data. 0:08 When you write a statement that performs one of the CRUD operations, 0:12 you need to be certain that the SQL statement that you write and 0:16 run is exactly what you want to get executed. 0:19 In a production environment, it can be difficult to revert to earlier state. 0:23 This is because in most circumstances your statements are run in autocommit mode, 0:27 meaning what you execute gets committed or saved to disk. 0:32 Most databases have autocommit switched on by default. 0:37 The operations we've been doing in this course are autocommit, 0:40 meaning the results of our statements get saved to disk. 0:44 You may not want this to happen all of the time. 0:48 Especially when you're doing something where you're wanting to do 0:51 batch operations. 0:55 Meaning you want to do multiple statements that get executed together. 0:56 Imagine you are populating a database for the first time. 1:01 This is know as seeding. 1:04 Running multiple statements are normally prepared in a SQL file. 1:06 This is also known as a script file. 1:10 Let's say we're entering six statements all in one go to populate a table. 1:12 Image after the third statement, my computer crashes or 1:17 is unplugged by accident. 1:20 The database would have changes from my first three statements but 1:21 the last three wouldn't be in there. 1:25 Once my computer had rebooted, 1:27 if I run the statements again, it would either error or create duplicate records. 1:29 I could manually edit the script not to include those first statements 1:34 after inspecting the state of the database and 1:38 then run the remaining statements, which is fine for a six line file. 1:41 But in the real world, 1:45 database seed scripts can contain hundreds if not thousands of statements. 1:47 Trying to deal with the issues that can occur from crashes and 1:51 other interruptions is a pain. 1:54 So, is there a way to disable autocommit, and 1:57 commit everything in one go at the end? 2:00 There is a way, and it's by using transactions. 2:03 Transactions aren't currently available in SQL Playgrounds and 2:06 you may never need to do them manually, yourself. 2:09 But it's an important concept to understand. 2:12 Here's a couple of INSERT statements. 2:14 We want to first switch off autocommit. 2:17 We do this by starting our script with BEGIN TRANSACTION. 2:20 This instructs the database that all statements after this 2:25 should be committed all in one go when we're ready. 2:28 In other words, autocommit is switched off. 2:32 Sometimes you may see this shortened to just the keyword BEGIN. 2:36 Then we have our statements. 2:39 And then when we're absolutely certain that the results of these statements 2:41 should be saved, we write COMMIT. 2:44 Once the commit statement has been issued, it can't be undone. 2:47 Unless you write the appropriate delete or update statements to undo it yourself. 2:51 Some databases don't have autocommit switched on, so 2:56 you may need to begin before and commit after every statement. 3:00 But in most cases, you don't. 3:04 You may see the keywords begin and commit in files generated by database backups. 3:06 If you see them, now you know what to do. 3:11
You need to sign up for Treehouse in order to download course files.Sign up