Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Modifying Data with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Introduction to Transactions

3: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?

Definitions

Autocommit - every statement you write gets saved to disk.
Seeding - populating a database for the first time.
Script file - a file containing SQL statements.

SQL Used

Switch autocommit off and begin a transaction:

BEGIN TRANSACTION;

Or simply:

BEGIN;

To save all results of the statements after the start of the transaction to disk:

COMMIT;

See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    [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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up