Transactions in sql server Part 57 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to presume technologies I am
venket this is part 57 of signal server
in this session we'll learn about
transactions in sequel server before
continuing with the session I strongly
recommend to watch part 56 of this video
series so what's a transaction a
transaction is a group of commands that
change the data stored in a database a
transaction is treated as a single unit
a transaction ensures that either all of
the commands succeed or none of them if
one of the commands in the transaction
fails all of the commands fail and any
data that was modified in the database
is then ruled back in this way
transactions maintain the integrity of
data in a database let's look at an
example I have this table TBL product
which has got information about laptops
and desktops at the moment the quantity
of laptops available is 100 now let's
say I want to change the quantity to 200
and to do that you know we are issuing
this update statement update TBL product
set quantity available is equal to 200
where product ID is equal to 1 now when
I execute the statement you know you can
see that one row effected when we select
the data back you should see the
quantity available is 200 now this
update statement is not part of any
transaction so as soon as you execute
this update statement the change is made
permanent to the database immediately on
the other hand you can make this update
statement belong to a transaction by
including begin transaction so now let's
try to change the quantity to 300 so now
this update statement is part of the
transaction when we execute these two
statements together look at this it says
one row affected now from within the
same vendor within the same connection
when I select the data back from the
table look at this it shows 300 but then
let's try to make another connection to
the sequel server so this is a different
connection now when I try to select the
data from the same table look at what's
going to happen
it says executing query and then if you
look at the product table it has got
only two rows to select that rows from
that table it should take less than a
second but if you look at this it's
almost more than ten seconds now it is
still executing that's because you have
initiated this transaction but you did
not either commit this or roll this back
because of which other users will not be
able to see that uncommitted data by
default and that's because sequel
servers default isolation level is read
committed meaning read only committed
data but here if you look at this there
is some uncommitted data in this TBL
product table that's why I'm not able to
see the data from that table so let's
cancel that and if I want to see the
data what I can do is basically set the
transaction isolation level to read
uncommitted data okay so let's do that
set transaction isolation level to read
uncommitted so let's execute this
command completed now if I execute the
select star from TBL product I am able
to see the read you know uncommitted
data now when I roll this transaction
back
execute this command completed
successfully now look at this when I
execute select star from TBL product
before we execute select star from TBL
product this is the previous data this
is that uncommitted 300 now when I
execute this query look at this the
change is undone so now whenever you're
updating sir delete statements whenever
these are part of a transaction now the
change is not made immediately permanent
to the database until you commit that
transaction so if you want to make the
change permanent you come with the
transaction if you want to undo the
change then you roll back the
transaction so for example let's say at
the minute you know the quantity
available for laptops is 200 let's try
to change it to 300 as part of this
transaction and then to make that change
permanent I basically have to say commit
transaction at this point the change is
made permanent to the database now
transaction processing follows very
simple steps you begin a transaction
using begin transaction and then you
process the database commands you might
have one or more sequel server commands
you know inserts updates and deletes and
then you check for any errors if there
are errors you roll the transaction back
otherwise you commit the transaction
let's look at an example I have two
tables here TBL physical address and TBL
mailing address now in reality let's say
we are shopping online most of the
e-commerce sites have mailing address
and you know billing address and most of
us will have the same mailing and
billing address along the same lines
here if we look at this we have physical
and a mailing address as look at this
employee 1 0 1 he has the same mailing
and physical address now let if you look
at the London you know the city spelling
London is misspelled here and obviously
I want to correct this to correct this I
have a stored procedure here and if you
look at the stored procedure it's a
pretty simple one all we are doing here
is we are updating mailing address table
and physical address table and we are
setting the city to
correct spelling okay and if you look at
these to update statements these are
wrapped between begin transaction and
commit transaction blocks and this block
is further wrapped between begin try and
end try and then following the try block
is the catch block where we are ruling
the transaction back we have spoken
about handling errors using try catch
blocks in the previous session of this
video series if you are new to error
handling please check that video first
okay so obviously when we execute the
stored procedure what's going to happen
it's going to execute the first update
statement and then it's going to execute
the second update statement and if there
are no errors the transaction will be
committed if there are errors whenever
you are executing this first or second
update statement what's going to happen
the control will immediately be
transferred to the cache block and
within the cache block we are rolling
the transaction back okay now since
these are two simple update statements
which you know both of these statements
should succeed and correct the city
spelling to London let's look at this so
I have these two tables physical address
and mailing address so if you look at
this at the minute the city spelling is
incorrect and we have the same stored
procedure that we have seen on the slide
which updates the mailing address and
the physical address tables and then in
addition we also have print transaction
committed just to print a message you
know to prove that it committed the
transaction so let's create this stored
procedure now and now before we exit
execute let's quickly check the data and
TBL physical address and mailing address
you know the spelling is incorrect so
let's execute the stored procedure so
one row affected one row affected so the
first one row affected is when we
updated the mailing address the second
one is when we updated physical address
and then we have that transaction
committed message printed by this print
statement which means the transaction
has been committed so now when we check
the physical and mailing address you
should see the spelling is now corrected
now let's do
let's try to introduce an error
intentionally if you look at the second
update it's the same stored procedure
except that the second update statement
has gone you know here we are setting
the city to London London okay basically
what we are trying to do here is if you
look at the column length the city
column length in TBL physical address
table so if you look at the city column
length it's ten characters but now I am
trying to update the physical address to
London London and London Space London is
definitely more than ten characters so
obviously when this update statement is
being executed we will get an error now
let's change the mailing address to
London one now when we change the stored
procedure let's change this so alter
procedure now when we execute the stored
procedure what's going to happen it
comes here it updates the mailing
address okay to London one which is fine
and then when it comes to updating the
physical address what's going to happen
okay the city column let this stand but
but the value is more than ten so this
is going to throw an exception so when
an exception occurs what's going to
happen the control immediately goes to
the catch block and the catch block is
ruling back the transaction so when we
roll the transaction back what's going
to happen whatever change that was made
by this first update statement is also
going to be undone so this update
statement changed it to London one but
then it will be rolled back so it will
remind London and then it will finally
print transaction rolled back so let's
see that in action let's execute this
you see that one row affected then it
says zero zero rows affected so it first
updated city to London one and then when
it was trying to do this we got that
zero rows affected so because there was
an error when we were trying to execute
this immediately the control will go to
the cache block rollback transaction and
you should see print you know
transaction rollback as a result of
which if you now select the data from
these two tables you
would see as if we didn't update
anything because the transaction is
rolled back so both of these statements
are treated as a single unit because the
transaction ensures either both of them
succeed or none of them and in this way
transactions maintain the integrity of
the database now we we discussed that a
transaction is a group of database
commands now what database commands are
part of a transaction is determined by
an asset test meaning the commands that
belong in a transaction have to be
atomic consistent isolated and durable
what we mean by this acid test we will
look at in the next video session on
this slide you can find resources for
asp.net c-sharp and sequel server
interview questions that's it for today
thank you for listening have a great day