Notes on updating & deleting data using SQL.
When we issue a SQL command that changes the database, it starts a transaction. A sequence of operations treated as a single logical piece of work. A transaction must comply to the ACID principles.
Whenever we issue a command like
DELETE, we are automatically starting a transaction.
We can also wrap multiple commands inside a bigger transaction.
Transaction are useful when we require all our commands operate on the same data state. A problem can occur when you execute a sequence of events. Half-way through, another use executs a command that changes the data your working on. Thus, half your commands executed on 1 state of data, the other half on a 2nd state of data. A transaction prevents this from happening.
We can undo a transaction using the
This can only be done when a transaction hasn't been committed.
By default, transactions are committed automatically so you will 1st need to turn this off.
Note, we are referring to the MySQL engine in this example. Other engines may differ.