Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
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.
Let's talk about committment.
When you're in a relationship with a database,
you need to be committed, especially when you're modifying data.
When you write a statement that performs one of the CRUD operations,
you need to be certain that the SQL statement that you write and
run is exactly what you want to get executed.
In a production environment, it can be difficult to revert to earlier state.
This is because in most circumstances your statements are run in autocommit mode,
meaning what you execute gets committed or saved to disk.
Most databases have autocommit switched on by default.
The operations we've been doing in this course are autocommit,
meaning the results of our statements get saved to disk.
You may not want this to happen all of the time.
Especially when you're doing something where you're wanting to do
Meaning you want to do multiple statements that get executed together.
Imagine you are populating a database for the first time.
This is know as seeding.
Running multiple statements are normally prepared in a SQL file.
This is also known as a script file.
Let's say we're entering six statements all in one go to populate a table.
Image after the third statement, my computer crashes or
is unplugged by accident.
The database would have changes from my first three statements but
the last three wouldn't be in there.
Once my computer had rebooted,
if I run the statements again, it would either error or create duplicate records.
I could manually edit the script not to include those first statements
after inspecting the state of the database and
then run the remaining statements, which is fine for a six line file.
But in the real world,
database seed scripts can contain hundreds if not thousands of statements.
Trying to deal with the issues that can occur from crashes and
other interruptions is a pain.
So, is there a way to disable autocommit, and
commit everything in one go at the end?
There is a way, and it's by using transactions.
Transactions aren't currently available in SQL Playgrounds and
you may never need to do them manually, yourself.
But it's an important concept to understand.
Here's a couple of INSERT statements.
We want to first switch off autocommit.
We do this by starting our script with BEGIN TRANSACTION.
This instructs the database that all statements after this
should be committed all in one go when we're ready.
In other words, autocommit is switched off.
Sometimes you may see this shortened to just the keyword BEGIN.
Then we have our statements.
And then when we're absolutely certain that the results of these statements
should be saved, we write COMMIT.
Once the commit statement has been issued, it can't be undone.
Unless you write the appropriate delete or update statements to undo it yourself.
Some databases don't have autocommit switched on, so
you may need to begin before and commit after every statement.
But in most cases, you don't.
You may see the keywords begin and commit in files generated by database backups.
If you see them, now you know what to do.
You need to sign up for Treehouse in order to download course files.Sign up