Rollback command.
Now, the Roback command is the transactional command used to undo transactions that have not already
been saved in the database.
This command can only be used to undo transactions since the last commit or rollback command was issued.
So why don't we jump over Hamas to learn, roll back with an example.
All right, so here we are.
Transactional control commands are only used with a dismal commands, such as insert update and delete
only.
Right.
They cannot be used while creating tables or dropping them because these operations are automatically
committed to the database.
So in this example, we're going to use the update command with transactional control commands.
So here's the data of the person table.
So we're going to change the first name Terry to Tom.
We will use business entity ID to handle person and Terrys business entity ID is to.
Now, we have learned this update statement, and as soon as we execute it, Terri will change to Tom.
So that's it.
It's been changed.
So now let's do this process with transactional commands.
The transaction is started by Biggin Tran or Begin Transaction Command, then build the query, so let's
change the first name to Terry again.
When we execute the query, we will see the message one row is affected.
And if we check the person table, we will see that Tom has been changed to Terry.
So now let's open a new query and try to retrieve data from the person table.
And will not be able to retrieve data that that we've begun the transaction on while the transactions
are still working, escarole server transaction will not allow other users who want to retrieve the
row that has I.D. of two.
The records that transactional commands have been executed on are locked and waiting for roll back or
commit commands.
See how that works.
So the rollback command is used at the end of the query.
And as soon as we ask you the rollback, the other user will be able to retrieve all the data, even
these locked rows.
And here's a clock record, it has been unlocked and retrieved.
So when we run the rollback command, all changes will be canceled, school will change, too, from
Terry to Tom again.
Now, if there is an error in the transaction, the rollback command will be executed automatically
and the process will be aborted.
Remember what I said earlier, all or nothing?
Commit command.
So the command command is a transactional command used to save changes invoked by a transaction to the
database.
They commit command, saves all the transactions to the database since the last commit or rollback command.
So let's begin the transaction again, and you're going to see that Tom has been changed to Terry,
so begins a statement is waiting for rollback and the commitment.
And here you can see that the record has been locked for other users and the ROBACK command will cancel
the process, but the committee command will save it to the database.
Says, go ahead and execute, commit command.
We should see Tom has been changed, Terry.
And there it is.
Now, the row has been unlocked for any other user.
Now, if there is no error in the transaction, the commit command will be executed automatically in
the process, in successfully.
Save point command.
A save point is a point in a transaction when you can roll the transaction back to a certain point without
rolling back the entire transaction.
In this example, we will update first name, middle name and last name with a separate query.
So first name will be changed to Tom.
Middle name will be changed to Z.
And the last name will be changed to Walker.
So the Save Tran command is used to create a certain point.
They say first name save point will be, oh, save update statement for the first name.
The same middle name, same point will be save update statement for the middle name.
The save last name, Save Point will save the update statement for the last name.
Now, when we execute the query, three cells will be changed and three save points will be created.
And then the transaction will wait for the committee command to save or the rollback command to cancel
the process.
As you can see, the first name, the middle name and the last name fields have been changed.
So it is possible to roll back to a certain safe point, the role Bactrian Command is used for this
purpose.
So let's assume that we got an error while we were updating the last name and we don't want to cancel
the entire transaction.
So in this situation, we will specify the safe point as save middle name.
The statements which are coming after the same middle name point will be rolled back, so let's execute
the query and see what happens.
Yeah, as you can see, only last name has been aborted, the transaction has not been completed yet
because the ROBACK command has been run for only a certain point, which is the same last name point.
And the rest of the transaction has still been waiting for the rollback war, commit commands.
And as you can see, other users can't retrieve any data from this particular on the table.
Now, if we roll back the transaction, first name and middle name will be canceled and the process
will also be aborted.
So if we execute the query again and answered with the commit command, first name, middle name and
last name will be saved successfully.
Very good, so in this section, we have learned about Escorial transaction and in the following section
we will learn about schema objects.
See you there.