Transactions in sql server and ACID Tests Part 58 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

so welcome to presume technologies I am
Venky this is part 58 of signal server
in this session we'll learn about
transaction as a test before continuing
with the session I strongly recommend to
watch part 57 of this video series we
know that a transaction is a group of
database commands that are treated as a
single unit a successful transaction
must pass the acid test that is a
transaction must be atomic consistent
isolated and durable let's see what we
mean by each one of these a transaction
must be atomic
meaning all statements in the
transaction either completed
successfully or they were all rolled
back the tasks that the set of operation
represents a say they're accomplished or
not but in any case not left half done
let's see what we mean by this with an
example
I have two tables here TBL product and
TBL product sales TBL product is like an
inventory table which contains the
information about the products and the
quantity available now whenever we sell
a product an entry should also be made
into TBL product sales table so when we
sell a product two things should happen
first we should check the quantity
available let's say for example I am
selling 10 laptops
the first thing that I have to do here
is to check ok what's the quantity
available for laptops 90 and then from
that quantity available I will have to
deduct the 10 laptops that I'm selling
so we have to update this quantity
available - 90 - 10 80 and then we also
have to make an entry into this TBL
product sales table product ID is 1 and
the lab and and the quantity I am
selling is 10 ok so basically when we
sell you know a product two things
happening update TBL product table
insert a row into TBL product sales
table so that's what this transaction is
doing here the first statement updates
TBL product table and the next statement
inserts a row into TBL product sales
table and these two statements are
wrapped inside a transaction so the
Tomasz city of a transaction states that
both of these statements should be
treated as one unit either both of them
succeed if one of them fails the data
that's modified by the other statement
should be you know undone and rolled
back so that the database is in a state
that is similar to you know before we
have begin the transaction so either
both of them should succeed or none of
them okay that's what is the atomic city
of a transaction the transaction should
also be consistent
which means all data attached by the
transaction is left in a logically
consistent State for example if stock
available numbers are decremented from
TBL product table then there has to be
an Associated entry in TBL product sales
table the inventory can't just disappear
so for example if I am selling 10
laptops the first update statements
reduces that quantity from laptops
quantity available here and then let's
say when we are about to execute the
insert statement into t-bill products
sales table the Power has gone or
something has happened in that
circumstances you know a transaction
should undo the first statement and put
it back to 90 otherwise we cannot
account for those 10 laptops so that's
what consistency says all data touched
by the transaction is left in a
logically consistent State the
transaction should also be isolated what
we mean by this the transaction must
affect data without interfering with
other concurrent transactions or being
interfered with by them okay so if there
are two transactions transaction a and
transaction be you know transaction a
shouldn't interfere with transaction B
and similarly transaction B shouldn't
interfere with transaction a both of
them you know should work as a single
unit of work and they shouldn't
interfere with each other okay let's see
how transaction achieves isolation
I have this table here TBL product now
let's say you know this is one of the
transactions where I am updating the
stable TBL product set let's say
quantity available is equal to 350 where
product ID is equal to 1 and now let's
make this update statement part of the
transaction so begin transaction so now
when I execute this look at this a
transaction is now being processed on
TBL product table we haven't committed
this transaction or rolled it back so
this transaction is still being
processed now let's say there is another
person you know who connected to the
sequel server and he is also trying to
update the same table so update TBL so
he issued let's say another transaction
or let's just say he's trying to update
the quantity now before updating he
wants to select the data and see so he
is issuing a select statement so now
when I say select star from TBL product
when I execute this look at this it says
executing query but it it will never get
access to that table why because there
is a transaction that is you know still
being processed it's not either it's not
completed yet that's why other users by
default will not be able to see you know
the data that is being touched by the
other transactions so here there is one
transaction which is processing you know
a record from this table if you look at
this there are two records in this table
currently we are updating the first
record which means this transaction is
still being processed we haven't
committed a roll that back so now when I
say select star from TBL product it it
says query executing query but it's not
showing it's just waiting for the
transaction to complete on the other
hand let's cancel that and when I say
select star from TBL product where
product ID is equal to two I am
selecting a row where the product ID is
equal to two look at this the moment I
execute that I
mediately get that draw but then when I
try to get the row with ID is equal to
one that is being updated by the other
transaction in the other connection
window look at this it says executing
query so why am I not able to see that
because that true is being locked by the
database because another transaction is
currently being processed so this
transaction is not interfering with this
transaction here so the transactions
must be isolated so how does the
transactions achieve this isolation you
know basically using locking mechanism
we will talk about transaction isolation
levels in in the next session okay so
this isolation this actually prevents
transactions from making changes to data
based on uncommitted information for
examples changes you know to a record
that are subsequently rolled back most
databases use locking to maintain
transaction isolation okay so here I
haven't committed any data add you know
this transaction is still being
processed I haven't committed any data
yet that's why this user is not able to
access that record now if this user were
able to you know access that record that
product ID is equal to one then he might
see uncommitted data because this
transaction is still being processed I
didn't commit the data I didn't make the
changes permanent to the database so
this user might see uncommitted data and
he might make his decisions based on
that uncommitted data which I'm you know
I'm a later rollback so in which case
his decision might be wrong so when I
rolled back that he might see only the
rollback data that's why by default you
know if a transaction has locked a row
you know other users will not be able to
see that so a transaction should also be
isolated and a transaction should be
durable and this is pretty simple to
understand once it change is made to a
database it's permanent if a system
error or power failure occurs before a
set of come
manses complete those commands are
undone and the data is restored to its
original state
once the system begins running again so
let's say for example we are executing a
long-running transaction and let's say
half of the data is modified and then
all of a sudden there is a power failure
so when that happens when the power
comes back the database should be in
such a position it has to roll back and
undo the changes that it has already
done and leave the database in a
consistent and durable state that's what
durable means all right if you want to
receive email alerts when new videos are
uploaded please subscribe to my youtube
channel at youtube.com for / kud V&K 80
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