1
00:00:00,360 --> 00:00:01,470
Rollback command.

2
00:00:02,690 --> 00:00:09,110
Now, the Roback command is the transactional command used to undo transactions that have not already

3
00:00:09,110 --> 00:00:11,120
been saved in the database.

4
00:00:12,110 --> 00:00:19,280
This command can only be used to undo transactions since the last commit or rollback command was issued.

5
00:00:20,930 --> 00:00:26,480
So why don't we jump over Hamas to learn, roll back with an example.

6
00:00:26,900 --> 00:00:27,830
All right, so here we are.

7
00:00:28,370 --> 00:00:35,540
Transactional control commands are only used with a dismal commands, such as insert update and delete

8
00:00:36,440 --> 00:00:37,160
only.

9
00:00:37,400 --> 00:00:37,750
Right.

10
00:00:38,360 --> 00:00:44,720
They cannot be used while creating tables or dropping them because these operations are automatically

11
00:00:44,720 --> 00:00:46,010
committed to the database.

12
00:00:46,640 --> 00:00:52,550
So in this example, we're going to use the update command with transactional control commands.

13
00:00:54,420 --> 00:00:56,850
So here's the data of the person table.

14
00:00:58,510 --> 00:01:01,930
So we're going to change the first name Terry to Tom.

15
00:01:03,800 --> 00:01:11,720
We will use business entity ID to handle person and Terrys business entity ID is to.

16
00:01:12,880 --> 00:01:18,880
Now, we have learned this update statement, and as soon as we execute it, Terri will change to Tom.

17
00:01:19,570 --> 00:01:20,260
So that's it.

18
00:01:20,450 --> 00:01:21,460
It's been changed.

19
00:01:24,050 --> 00:01:27,050
So now let's do this process with transactional commands.

20
00:01:32,210 --> 00:01:40,580
The transaction is started by Biggin Tran or Begin Transaction Command, then build the query, so let's

21
00:01:40,580 --> 00:01:42,530
change the first name to Terry again.

22
00:01:44,700 --> 00:01:49,620
When we execute the query, we will see the message one row is affected.

23
00:01:50,490 --> 00:01:54,780
And if we check the person table, we will see that Tom has been changed to Terry.

24
00:01:56,970 --> 00:02:01,140
So now let's open a new query and try to retrieve data from the person table.

25
00:02:02,100 --> 00:02:08,130
And will not be able to retrieve data that that we've begun the transaction on while the transactions

26
00:02:08,130 --> 00:02:14,460
are still working, escarole server transaction will not allow other users who want to retrieve the

27
00:02:14,460 --> 00:02:17,220
row that has I.D. of two.

28
00:02:19,830 --> 00:02:27,900
The records that transactional commands have been executed on are locked and waiting for roll back or

29
00:02:27,900 --> 00:02:29,050
commit commands.

30
00:02:29,460 --> 00:02:30,340
See how that works.

31
00:02:30,750 --> 00:02:33,420
So the rollback command is used at the end of the query.

32
00:02:35,460 --> 00:02:41,340
And as soon as we ask you the rollback, the other user will be able to retrieve all the data, even

33
00:02:41,520 --> 00:02:42,660
these locked rows.

34
00:02:44,160 --> 00:02:49,350
And here's a clock record, it has been unlocked and retrieved.

35
00:02:51,700 --> 00:02:58,690
So when we run the rollback command, all changes will be canceled, school will change, too, from

36
00:02:58,690 --> 00:03:00,070
Terry to Tom again.

37
00:03:02,390 --> 00:03:09,380
Now, if there is an error in the transaction, the rollback command will be executed automatically

38
00:03:09,380 --> 00:03:11,540
and the process will be aborted.

39
00:03:11,900 --> 00:03:14,510
Remember what I said earlier, all or nothing?

40
00:03:16,980 --> 00:03:18,120
Commit command.

41
00:03:19,620 --> 00:03:25,170
So the command command is a transactional command used to save changes invoked by a transaction to the

42
00:03:25,170 --> 00:03:25,740
database.

43
00:03:26,960 --> 00:03:34,220
They commit command, saves all the transactions to the database since the last commit or rollback command.

44
00:03:36,060 --> 00:03:41,130
So let's begin the transaction again, and you're going to see that Tom has been changed to Terry,

45
00:03:41,880 --> 00:03:47,550
so begins a statement is waiting for rollback and the commitment.

46
00:03:49,520 --> 00:03:57,590
And here you can see that the record has been locked for other users and the ROBACK command will cancel

47
00:03:57,590 --> 00:04:01,280
the process, but the committee command will save it to the database.

48
00:04:05,710 --> 00:04:08,200
Says, go ahead and execute, commit command.

49
00:04:09,280 --> 00:04:12,040
We should see Tom has been changed, Terry.

50
00:04:15,140 --> 00:04:16,040
And there it is.

51
00:04:18,290 --> 00:04:21,260
Now, the row has been unlocked for any other user.

52
00:04:23,120 --> 00:04:28,610
Now, if there is no error in the transaction, the commit command will be executed automatically in

53
00:04:28,610 --> 00:04:30,560
the process, in successfully.

54
00:04:32,410 --> 00:04:33,700
Save point command.

55
00:04:34,990 --> 00:04:42,190
A save point is a point in a transaction when you can roll the transaction back to a certain point without

56
00:04:42,190 --> 00:04:45,160
rolling back the entire transaction.

57
00:04:48,460 --> 00:04:55,420
In this example, we will update first name, middle name and last name with a separate query.

58
00:04:57,110 --> 00:04:58,940
So first name will be changed to Tom.

59
00:05:00,120 --> 00:05:02,570
Middle name will be changed to Z.

60
00:05:11,180 --> 00:05:13,790
And the last name will be changed to Walker.

61
00:05:17,460 --> 00:05:21,600
So the Save Tran command is used to create a certain point.

62
00:05:24,210 --> 00:05:30,330
They say first name save point will be, oh, save update statement for the first name.

63
00:05:34,390 --> 00:05:39,700
The same middle name, same point will be save update statement for the middle name.

64
00:05:42,170 --> 00:05:47,510
The save last name, Save Point will save the update statement for the last name.

65
00:05:56,930 --> 00:06:03,680
Now, when we execute the query, three cells will be changed and three save points will be created.

66
00:06:04,790 --> 00:06:12,200
And then the transaction will wait for the committee command to save or the rollback command to cancel

67
00:06:12,200 --> 00:06:12,860
the process.

68
00:06:15,720 --> 00:06:20,320
As you can see, the first name, the middle name and the last name fields have been changed.

69
00:06:21,390 --> 00:06:27,270
So it is possible to roll back to a certain safe point, the role Bactrian Command is used for this

70
00:06:27,270 --> 00:06:27,680
purpose.

71
00:06:28,350 --> 00:06:34,020
So let's assume that we got an error while we were updating the last name and we don't want to cancel

72
00:06:34,020 --> 00:06:35,740
the entire transaction.

73
00:06:36,660 --> 00:06:41,310
So in this situation, we will specify the safe point as save middle name.

74
00:06:42,200 --> 00:06:48,410
The statements which are coming after the same middle name point will be rolled back, so let's execute

75
00:06:48,410 --> 00:06:49,750
the query and see what happens.

76
00:06:52,600 --> 00:06:59,830
Yeah, as you can see, only last name has been aborted, the transaction has not been completed yet

77
00:06:59,830 --> 00:07:06,820
because the ROBACK command has been run for only a certain point, which is the same last name point.

78
00:07:07,810 --> 00:07:12,840
And the rest of the transaction has still been waiting for the rollback war, commit commands.

79
00:07:13,810 --> 00:07:18,790
And as you can see, other users can't retrieve any data from this particular on the table.

80
00:07:21,110 --> 00:07:28,070
Now, if we roll back the transaction, first name and middle name will be canceled and the process

81
00:07:28,070 --> 00:07:29,240
will also be aborted.

82
00:07:38,230 --> 00:07:44,080
So if we execute the query again and answered with the commit command, first name, middle name and

83
00:07:44,080 --> 00:07:46,360
last name will be saved successfully.

84
00:07:49,460 --> 00:07:55,880
Very good, so in this section, we have learned about Escorial transaction and in the following section

85
00:07:55,880 --> 00:07:58,580
we will learn about schema objects.

86
00:07:59,170 --> 00:07:59,890
See you there.