4.8 Transactions and Rollbacks

00:00

all right, welcome back to Module four. Sequel programming to were in less than three concurrency and transactions, and we're in the first sub lesson, 3.2 transactions.

00:09

Now it's your transaction is a

00:13

method for us to combine sequel statements together into a single

00:17

group of logical work.

00:19

So if we take a look at the procedure that we coded in the previous lesson will see that we have two statements that need to run in order for the, uh,

00:29

the works to complete successfully. And if one of these fails, then the work has not completed successfully. For example, on Line 31 we update the salaries table and we set their current salary, too, and we manipulate the end date.

00:43

Now. What would happen if

00:44

this first statement ran? And then something went wrong? Table was locked and this insert into employee salary failed. Well, then we only did half the work. We updated the salary, but we didn't insert the new record. And now there's a logical gap in our salary table with respect to business rules. So how would we prevent that? Well,

01:04

my sequel on a lot of data base equal engines provide a way to deal with that in the form of transactions. Now, something to be aware of is that there are some sequel engines that do not support transactions, so you would not be able to use them in that in that database. Tension. So if you happen to be in a certain database engine, be sure to look up and

01:25

and ensure that it supports transactions.

01:27

So let's go ahead and start coating this. So what? We need to dio as we need to declare a new variable at the top

01:38

So we're going to declare

01:44

sequel Air.

01:47

Tiny ants. It's just a ***, but it's not allowed to be come as larger number. So it's safe space.

01:53

The fault falls.

01:56

So we're declaring this new sequel, Air Flag. That's a tiny ain't tiny ant and we've defaulted to false,

02:02

which will be zero.

02:07

So next we need to declare a continue handler

02:13

and this is all part of the transactions

02:15

transaction. Ah, syntax so that we can get it to work correctly.

02:19

Continue

02:22

handler

02:24

for a sequel exception

02:31

on DDE.

02:34

We're going to

02:36

set

02:38

sequel

02:40

there

02:44

equal to true.

02:49

So these two statements roll together.

02:53

Mmm. And we're saying that we have an air handler, and if that executes, we want to set this sequel Air flag too True.

03:00

Right?

03:04

So then right here

03:07

before

03:09

our sequel statements, we want to begin our transaction.

03:14

So we do that with a start

03:16

transaction command.

03:21

It's in my colon.

03:24

And that prevents it from

03:27

from my single from doing it the default way, which is it would

03:31

update and committed as soon as it ran the statement so it would update commit this. It would uncertain. Commit this.

03:38

So now it will send the database the data, but the database will not commit it

03:43

until it ah, gets the commit command.

03:49

So if we're going to do an if statement, if sequel

03:53

air

03:59

equal false,

04:00

then

04:03

commit

04:11

all right,

04:13

else

04:15

we're going to roll back.

04:17

That means it's going to undo what it tried to do.

04:24

And then we're also going to send out a statement from

04:30

we're gonna do. We're gonna send another air statement out in this case, but we're gonna do it a different way. We're not gonna actually,

04:36

uh, change the signal sequence state. So we're going to select this will cause the procedure. Just spit out a message. Still,

04:45

select

04:46

the transaction

04:49

filled

04:51

and rolled

04:53

back.

04:57

I'm going to see Michael on that.

05:00

And if

05:02

go ahead and close this up

05:10

and we still got that drop of exist, So we're just gonna rerun it

05:16

and it ran successfully.

05:19

So what is going on down here? Let's walk through this one more time because this procedure is starting to grow, as you may have noticed.

05:26

But if we break apart each component, it's each component by itself is not bad.

05:33

So

05:35

so we're doing all the stuff we did above.

05:39

But we added a sequel, Air Flag. We said It's the fault value to false.

05:46

The tiny in data type

05:46

is a

05:48

value that can either be true or false, which is 0104 false one for true. We said it's the fault of false

05:57

we declared a

05:59

continue handler.

06:01

And if that is,

06:03

if that occurs, if there's a sequel exception,

06:06

it sets our sequel Underscore Air Too true.

06:12

And then at the bottom. After we pass our checks, we start a transaction,

06:16

which tells us my sequel to not automatically commit the statements

06:21

and then runs the update statement

06:25

and the insert statement. Now, if very of both succeeded

06:29

sequel Air will still be false and then we can commit.

06:32

If sequel Air is true, then we're going to roll back. The statements were not going to commit anything.

06:39

We're gonna return it. We're gonna have the procedure return a message to the caller that the transaction filled and rolled back. Now we could have

06:46

raised our own signals. Signal State sequel, Ste era again. But in this instance, we decided just to return a message. The procedure will actually exit is if, though it did not have an heir,

06:58

so

07:00

and there you go. We have successfully inserted a transaction into our procedure.

07:08

And that's how you implement a transaction with a roll back so that you can help make sure that

07:15

either all your statements run or none of them run.