Take a look at the superhero table with the following records: id(INT), superhero_name(VARCHAR(30)), and first_appearance(INT).
id | superhero_name | first_appearance |
|---|---|---|
1 | Batman | 1939 |
2 | Spider-man | 1969 |
3 | Iron Man | 1963 |
First you add a new record with 'Captain America' (id = 4 and first_appearance = 1940), then 'Wonder Woman' (id = 5 and first_appearance = 1940), after that 'Thor' (id = 6 and first_appearance = 1962). Let's imagine that you want to insert several new records in this table, but you've changed your mind several times. You need to set a new SAVEPOINT before each insertion.
Then you roll back the transaction to a SAVEPOINT (SP3) so that the record 'Thor' would not be made permanent against a database.
Lastly, you commit the transaction.