Preview
Video Player
00:00
00:00
Introduction to Transactions
3:14When 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?
[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