SQL server concurrent transactions Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 70 of sequel server
tutorial in this video we'll discuss
what a transaction is the problems that
might arise when transactions are run
concurrently and the different
transaction isolation levels provided by
sequence server to address concurrency
side-effects first let's understand what
a transaction is a transaction is a
group of commands that change the data
stored in a database a transaction is
treated as a single unit of work a
transaction ensures that either all of
the commands succeed are 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 rolled back in this way transactions
maintain the integrity of data in the
database let's understand what a
transaction is with an example we have
an example of transaction here so this
transaction ensures that both the update
statements succeed or both of them fail
if there is a problem with any one of
the update statements so at the moment
here we have caught the accounts table
in that don't be a part I mean in the
table we have got two accounts mark and
Mary both of them have thousand dollars
balance and what we want to do is
transfer 100 dollars from mark to Mary
account so we have to update statements
here the first update treatment is going
to detect 100 dollars from mark account
look at the ID it's one that is Mark's
ID and the second update statement is
going to add that hundred dollars to
Mary's account now both of these update
statements are present within this begin
transaction and commit transaction block
and those statements are wrapped inside
this try block and we have a
corresponding catch block as well now if
both of these update statements execute
successfully then commit transaction
statement will be executed at that point
whatever changes these two update
statements made they are made permanent
in the database imagine this the first
update statement executed successfully
but when it tries to execute the second
update statement there is a problem and
it fails when it fails the control goes
to the catch block immediately and
within catch block we are rolling the
transaction
back and when it rolls the transaction
back what's going to happen it's going
to undo any changes that the first
update statement has done so this way
transactions maintain the integrity of
our database I have this same example
already typed here so at the moment both
mark and mary has got thousand dollars
balance let's transfer 100 dollars so
when we execute this and if both of the
update statements succeed then the
transaction will be committed look at
that transaction committed at this point
if we check the balances mark should
have 900 and Mary should have 1100 now
let's introduce a deliberate error so
I'm going to set the ID value to an end
where care value now ID column is an
integer column so when it tries to
execute this update statement it will
try to convert that value to an integer
it fails when it fails the control goes
to the catch block and this is going to
roll the transaction back now look at
the balances mark 900 Mary 1100 let's
execute this script and look at that it
says transaction rolled back and before
that it says one row effected so when it
executed this update statement it didn't
have any problem so it directed $100
from Mark's account so the balance
became 800 and then when it tried to
execute this statement there is a
problem so it went to this catch block
it ruled the transaction back so what
did it do for the change that the first
update statement has done it has undone
that so it has reverted the balance back
to 900 okay that way this transaction
here is ensuring that it's not half done
either all the statements exceed or none
of them this way transactions are
maintaining integrity of our databases
all right
we knew that databases of powerful
systems and are potentially used by many
users or applications at the same time
this means there are concurrent
transactions running all the time on a
database now allowing concurrent
transactions is essential for
performance but keep in mind they may
introduce concurrency
issues when two or more transactions are
working with the same data at the same
time and here we have some of the common
concurrency problems dirty reads lost
updates non-repeatable reads and phantom
reads will discuss these concurrency
problems in detail with examples in our
upcoming videos now one way to solve all
these concurrency problems is by
allowing only one user to execute only
one transaction at any point in time now
imagine what would happen with this kind
of an approach if you have a large
database with several users who want to
execute several transactions all those
transactions get queued and they may
have to wait a long time before they
could get a chance to execute their
transactions so you are getting poor
performance and the whole purpose of
having a powerful database system is
defeated
if you serialize access this way at this
point we might be thinking well then for
best performance why don't we allow all
transactions to execute concurrent
concurrently the problem with this
approach is that it may cause all sorts
of concurrency problems that is dirty
reads lost updates non-repeatable reads
phantom reads if two or more
transactions work with the same data at
the same time so to balance that sequel
server provides a different transaction
isolation level so we want to balance
concurrency problems versus performance
depending on our application needs and
for that sequel server provides all
these transaction isolation levels read
uncommitted read committed repeatable
read snapshot serializable the isolation
level that you choose for your
transaction defines the degree to which
one transaction must be isolated from
resources or data modifications made by
other transactions depending on the lye
solution level you have chosen you get
varying degrees of performance and
concurrency problems the table right
here has the list of isolation levels
along with concurrency side-effects so
on the Left we have the isolation levels
and here we have the concurrency
problems now if you look at read
uncommitted it has all concurrency side
effects whereas serializable it has no
concurrency side effects so if you
choose the lowest isolation level that
is read uncommitted it increases the
number of concurrent transactions that
can be executed at the same time but the
downside is you have all sorts of
concurrency issues on the other hand if
you choose the highest isolation level
that is serializable you will have no
concurrency side effects but the
downside is that this will reduce the
number of concurrent transactions that
can be executed at the same time if
those transactions work with the same
data so in our upcoming videos where
we'll discuss the concurrency problems
and transaction isolation levels in
detail with examples thank you for
listening and have a great day