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 😴
Share this Scrolly Tale with your friends.
A Scrolly Tale is a new way to read Twitter threads with a more visually immersive experience.
Discover more beautiful Scrolly Tales like this.