Thread by @AbzAaron

Learn 3 commands used to control SQL transactions.

A Thread 👇

First, what are transactions?

Transactions essentially group tasks into a single execution unit. If any of these tasks fail, the transaction is cancelled.

Transactions typically have 4 properties, which fall under the acronym ACID.

Each of these properties should be achieved for a database to be considered reliable.

To control these transactions, there are certain commands that can be used in SQL. Let's look at:

-- COMMIT
-- ROLLBACK
-- SAVEPOINT

The COMMIT command is for saving every transaction to the database

Below we delete rows where location is equal to "UK".

We then run COMMIT to ensure the changes are visible to other users

The ROLLBACK command will undo transactions that haven't been saved to the database since the last COMMIT or ROLLBACK command was run.

Here we delete some records, then immediately undo the changes.

The SAVEPOINT command is like a backup.

A SAVEPOINT is a point in a transaction to which you can later roll back. This avoids the need to rollback the entire transaction.

Here we are creating a SAVEPOINT and giving it a name.

If we want to ROLLBACK to a SAVEPOINT, we can run the following:

And that's all!

I actually forgot to include these in my cheatsheet, even though they're pretty important, so hopefully this gave you an of them.

Thanks for reading :)

*idea

I shouldn't tweet when I'm tired 😴