Transac'on

 Management  and    
Concurrency  Control    

Compiled  By:  Yonas  H.  (MSc.)  

 
 Contents    
§ Transac'on  and  Transac'on  Support  
§ Concurrency  Control  
§ Problems  of  Concurrent  Sharing    
§ Concept  of  Serializability  
§ Concurrency  Control  Mechanism    
§ Database  Recovery  
§ Transac'on  and  Recovery  
§ Recovery  techniques  and  facili'es    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   2  
Management  
 What  is  a  Transac'on  ?    
§ Any  one  execu'on  of  a  user  program  in  a  DBMS    
§ A  Transac'on  is  a  mechanism  for  applying  the  desired  
modifica'ons/opera'ons  to  a  database.    
§ Changes  made  in  real  'me  to  a  database  are  called  
transac'ons.  
Examples    
§ ATM  transac'ons,  credit  card  approvals,  flight  
reserva'ons,  hotel  check-­‐in,  phone  calls,  supermarket  
canning,  academic  registra'on  and  billing.      

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   3  
Management  
 Transac'on    …..  
§ A  transac'on  could  be  composed  of  one  or  more  
database  and  non-­‐database  opera'ons.    
§ Transforms  database  from  one  consistent  state  
to  another,  although  consistency  may  be  violated  
during  transac'on.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   4  
Management  
 Business  transac'ons  and  Online  
transac'ons  
§  A  business  transac'on  is  an  interac'on  in  the  real  
world,  usually  between  an  enterprise  and  a  person,  
where  something  is  exchanged.  Example  :  market      
§ An  online  transac'on  is  the  execu'on  of  a  program  
that  performs  an  administra've  or  real-­‐'me  func'on,  
oQen  by  accessing  shared  data  sources,  usually  on  
behalf  of  an  online  user  (although  some  transac'ons  
are  run  offline  in  batch).    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   5  
Management  
 Transac'on  Processing  System    
§ A  system  that  manages  transac'ons  and  controls  
their  access  to  a  DBMS  is  called  a  TP  monitor.    
§ A  transac'on  processing  system  (TPS)  generally  
consists  of  a  TP  monitor,  one  or  more  DBMSs,  and  a  
set  of  applica'on  programs  containing  transac'on.      
§ In  database  field,  a  transac'on  is  a  group  of  logical  
opera'ons  that  must  all  succeed  or  fail  as  a  group.    
§ Systems  dedicated  to  suppor'ng  such  opera'ons  are  
known  as  transac'on  processing  systems.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   6  
Management  
 Transac'on..  
§ Transac'ons  can  be  started,  a:empted,  then  commi:ed  or  
aborted  via  data  manipula'on  commands  of  SQL.    
§ Can  have  one  of  the  two  outcomes  for  any  transac'on:    
– Success  -­‐  transac'on  commits  and  database  
reaches  a  new  consistent  state    
• CommiZed  transac'on  cannot  be  aborted  or  rolled  
back.    
– Failure  -­‐  transac'on  aborts,  and  database  must  be  
restored  to  consistent  state  before  it  started.    
– Such  a  transac'on  is  rolled  back  or  undone.      
§ Aborted  transac'on  that  is  rolled  back  can  be  restarted  later.    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   7  
Management  
 Transac'on..  
For  example  

§  when  doing  a  money  transfer,  if  the  money  was  debited  from  
one  account,  it  is  important  that  it  also  be  credited  to  the  
deposi'ng  account.    

§ Also,  transac'ons  should  not  interfere  with  each  other  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   8  
Management  
 What  are  the  issues  in  Transac'on  
Management?    
1.  scheduling  Several  Transac'ons.    
§ When  a  DBMS  executes  several  Transi'ons  
concurrently  ,  for  perform  reasons  ,  it  has  to  
interleave  the  ac'ons  of  several  transac'ons  ,  
while  giving  users  the  effect  of  running  their  
programs.  An  interleaved  execu'on  of  several  
transac'ons,  called  a  schedule.    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   9  
Management  
 Transac'on  Issues..  

2.  concurrency  control  

 how  the  DBMS  handles  concurrent  execu'ons?    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   10  
Management  
 Transac'on  Issues..  
3.  crash  recovery    
§  It  deals  with  how  a  DBMS  handles  par'al  
transac'on(  Incomplete  Transac'ons).    

§ The  DBMS  ensures  that  the  changes  made  by  

such  par'al  transac'ons  are  not  seen  by  other  
transac'ons.    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   11  
Management  
 ACID  proper'es  of  Transac'ons    

§  A  transac'on  to  be  considered  as  a  valid  

transac'on  ,  it  should  exhibit  some  basic  features    
–    A  à Atomicity    

–  C  à Consistency    

–    I  à Isola'on    

–  D  à Durability    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   12  
Management  
 Atomicity    
§ Either  all  ac'ons  are  carried  out  or  none  are.    
§ Is  All  or  None  property    
§ a  transac'on  has  only  two  states,  Done  or  Never  
Started.      
§ Users  should  not  have  to  worry  about  the  effect  of  
incomplete  transac'ons  (say,  when  a  system  crash  
occurs).    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   13  
Management  
 Atomicity…    
§ Done  -­‐  a  transac'on  must  complete  successfully  and  
its  effect  should  be  visible  in  the  database.    
§ Never  Started  -­‐  If  a  transac'on  fails  during  execu'on  
then  all  its  modifica'ons  must  be  undone  to  bring  
back  the  database  to  the  last  consistent  state,  i.e.,  
remove  the  effect  of  failed  transac'on.    
§ No  state  between  Done  and  Never  Started  
§ Complete  all  or  none    /  commit  or  abort    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   14  
Management  
 Atomicity…    
§ Transac'ons  can  be  incomplete  for  three  kinds  of  reasons:    
1.  A  transac'on  can  be  aborted    
2.  Terminated  unsuccessfully    
3.  Some  anomaly  arises  during  execu'on.  

§ In  any  of  the  above  case  ,  the  transac'on  will  the  database  in  
an  inconsistent  state    
§ “  A  DBMS  ensures  transac'on  atomicity  by  undoing  the  
ac'ons  of  incomplete  transac'ons.”  To  be  able  to  do  this,  the  
DBMS  maintains  a  record,  called  the  log,  of  all  writes  to  the  
database.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   15  
Management  
 Consistency    
§ Each  transac'on  must  preserve  the  
consistency  of  the  database.    
§ Ensuring  this  property  of  a  transac'on  is  the  
responsibility  of  the  user.    
§ If  the  transac'on  code  is  correct  then  a  
transac'on,  at  the  end  of  its  execu'on,  must  
leave  the  database  consistent.    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   16  
Management  
 Isola'on      
§ A  transac'on  must  execute  without  
interference  from  other  concurrent  
transac'ons  and  its  intermediate  or  par'al  
modifica'ons  to  data  must  not  be  visible  to  
other  transac'ons.    
§ Transac'ons  do  not  affect  each  other.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   17  
Management  
 Isola'on..  
§ For  example,  if  two  transac'ons  T1  and  T2  are  
executed  concurrently,  the  net  effect  is  
guaranteed  to  be  equivalent  to  execu'ng  T1  
followed  by  execu'ng  T2  or  execu'ng  T2  
followed  by  execu'ng  T1.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   18  
Management  
 Durability  
§ The  effect  of  a  completed  transac'on  must  
persist  in  the  database,    
– i.e.,  its  updates  must  be  available  to  other  
transac'on  immediately  aQer  the  end  of  its  
execu'on,  and  is  should  not  be  affected  due  to  
failures  aQer  the  comple'on  of  the  transac'on.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   19  
Management  
 Durability…  
§ If  the  system  crashes  before  the  changes  
made  by  a  completed  transac'on  are  wriZen  
to  disk,  the  log  is  used  to  remember  and  
restore  these  changes  when  the  system  
restarts.    
§ WriZen  data  will  not  be  lost!  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   20  
Management  
 anager    
Transac ' on  
at
M
e s   tr an
• Coordin ates  with  the  
s ac'on     DBMS  Transac'on  Subsystem    
unic
• Comm  
scheduler
Scheduler    
• Implements  a  strategy  
Transac'on   Scheduler  /  
for  concurrency  control       Manager     Lock  Manager    
Buffer  Manager    
•  In  charge  of  transferring  
data  between  disk  storage   Buffer   Recovery  
and  main  memory     Manager     Manager    
Access  Manager    
• File  manager     Recovery  Manager    
does  not   Access   • Ensures  that  the  
directly   File  Manager     database  is  restored  to  
manage  the  
Manager    
the  right  state  before    a  
physical  input   failure  occurred        
and  output  of  
data,  rather  it   System   Database     File  Manager    
• Manipulates  the  
passes   Manager     and   System  
underlying  storage  files  
requests  on  to   Catalog     and  manages  the  
the  access   System  Manager     alloca'on  of  storage  
manager     • Appropriate  access  method  is  used  to  either  
space  on  disk      
read  or  write  data  into  the  system    manager    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   21  
Management  
 Ac'ons  of  a  transac'on    
§ A  transac'on  can  also  be  defined  as  a  set  of  ac'ons  
that  are  par'ally  ordered.    
§ the  ac'ons  that  can  be  executed  by  a  transac'on  
include  :    
1. Reading  (  R(A)  à Reading  Object  A  from  memory)    
2. Wri'ng  (  W(A)  à wri'ng  object  A  into  memory)    
3. Abor'ng  (terminate  and  undo  all  the  ac'ons  carried  
out  thus  far)    
4. Commijng  (complete  successfully)    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   22  
Management  
  Transac'on  and  System  Concepts  
§ System  must  keep  track  of  each  transac'on  when  
starts,  terminates,  commits,  and/or  aborts      
– BEGIN_TRANSACTION  
– READ  or  WRITE  
– END_TRANSACTION  
– COMMIT_TRANSACTION  
– ROLLBACK  (or  ABORT)  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   23  
Management  
 Transac'on  Execu'on  States    

Figure : State transition diagram illustrating the states for transaction execution

Monday,  March  13,  23   Transac'ons  processing  and  concurrency  Management   24  

 Transac'on  Execu'on  States    
§ Ac've     § Aborted    
–  the  ini'al  state;   – AQer  the  transac'on  has  been  
– The  transac'on  stays  in   rolled  back  and  the  database  
this  state  while  it  is   restored  to  its  state  prior  to  
execu'ng     the  start  of  the  transac'on.  
§ Par'ally  CommiTed       – Two  op'ons  aQer  it  has  been  
–  aQer  the  final   aborted    
statement  has  been   • Restart  the  transac'on  -­‐  
executed     only  if    no  internal  logical  
§ Failed     error    
– AQer  the  discovery  that   • Kill  the  transac'on    
normal  execu'on  can   § CommiTed    
no  longer  proceed     –  aQer  successful  comple'on    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   25  
Management  
 State  of  a  Transac'on….    

§ A  transac'on  is  an  atomic  opera'on  from  the  

users’  perspec've.  But  it  has  a  collec'on  of  
opera'ons  and  it  can  have  a  number  of  states  
during  its  execu'on.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   26  
Management  
 State  of  a  Transac'on  …..  
§ A  transac'on  can  end  in  three  possible  ways.    
1. Successful  Termina'on:  when  a  transac'on  completes  the  
execu'on  of  all  opera'ons  in  it  and  reaches  the  COMMIT  
command.    
2. Suicidal  Termina'on:  when  the  transac'on  detects  an  error  
during  its  processing  and  decide  to  quick  itself  before  the  end  
of  the  transac'on  and  perform  a  ROLL  BACK    
3. Murderous  Termina'on:  When  the  DBMS  or  the  system  force  
the  execu'on  to  abort  for  any  reason.  And  hence,  rolled  back.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   27  
Management  
 Transac'on  Log    
§ Tracks  all  transac'ons  that  update  database    
§  may  be  used  by  ROLLBACK  command  
§ May  be  used  to  recover  from  system  failure    
§ Log  stores:    
– Record  for  beginning  of  transac'on    
– Each  SQL  statement    
• Opera'ons      
• Name  of  objects    
• Before  and  aQer  values  for  updated  fields    
• Pointers  for  pervious  and  next  entries    
– Commit  statement    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   28  
Management  
 Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   29  
Management  
 Ways  of  Transac'on  Execu'on    
§ There  are  two  ways  of  execu'ng  a  set  of  
transac'ons:      
(a)  Serially  
(b)  Concurrently      

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   30  
Management  
 Ways  of  Transac'on  Execu'on    
(a)  Serially:        
• transac'ons  are  executed  strictly  serially.    
• Transac'on  Ti  completes  and  writes  its  results  to  
the  database  then  only  the  next  transac'on  Tj  is  
scheduled  for  execu'on.    
• at  one  'me  there  is  only  one  transac'on  is  being  
executed  in  the  system.    
• The  data  is  not  shared  between  transac'ons  at  one  
specific  'me.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   31  
Management  
 Ways  of  Transac'on  Execu'on    
In  Serial  transac'on  execu'on,  
   
one  transac'on  being  executed  
does  not  interfere  the  execu'on  
of  any  other  transac'on.      

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   32  
Management  
 Ways  of  Transac'on  Execu'on  …  
§ Good  things  about  serial  execu'on    
– Correct  execu'on  
 i.e.,  if  the  input  is  correct  then  output  will  be  correct.    
– Fast  execu'on  
• since  all  the  resources  are  available  to  the  ac've.    

§ The  worst  thing  about  serial  execu'on  is  very  

inefficient  resource  u'liza'on.  i.e.  reduced  
parallelism.    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   33  
Management  
 Ways  of  Transac'on  Execu'on  …  

§ NotaBons:        

–  Read(x)  àread  data  item  x  from  database    

–    Write(x)  àwrite  data  item  x  into  the  database      

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   34  
Management  
Example  of  a  serial  execu'on    
§ Suppose  data  items  X  =  10,  Y  =  6,  and  N  =1  and  T1  and  T2  are  
transac'ons.     Time   T   1 T   2
  read (X) {X = 10}    
T1   T2   X := X+N {X = 11}    
read (X)   read (X)  
X := X+N   X := X+N   write (X) {X = 11}    
write (X)   write (X)   read (Y) {Y = 6}    
read (Y)     Y := Y+N {Y = 7}    
Y := Y+N     write (Y) {Y = 7}    
  read (X) {X = 11}  
write (Y)    
  X := X+N {X = 12}  
  write (X)  

 
§ Final  values  of  X,  Y  at  the  end  of  T1  and  T2:    
– X  =  12  and  Y  =  7.    
– Thus  in  serial  execu'on  of  transac'on,  if  we  have  
two  transac'ons  
Monday,  March  13,  23  
Ti  apnd  
Transac'ons  
T and  c,  oncurrency  
rocessing  i+1
then  Ti+1  will  only  be   35  
Management  
(b)  Concurrently:    
§ is  the  reverse  of  serially  executable  transac'ons,  the  individual  
opera'ons  of  transac'ons,  i.e.,  reads  and  writes  are  interleaved  
in  some  order.    
Time   T1   T2   Improves  resource  
  read (X) {X = 10}    
  read (X) {X = 10}   u'liza'on,  
X := X+N {X = 11}    
  unfortunately  gives  
  X := X+N {X = 11}  

  write (X) {X = 11}     incorrect  result.      

  write (X) (X=11)  
read (Y) {Y = 6}    
Y := Y+N {Y = 7}    
  write (Y) {Y = 7}    

§ Final  values  at  the  end  of  T1  and  T2:  X  =  11,  and  Y  =  7.    
§ The  correct  value  of  X  is  12  but  in  concurrent  execu'on  X  =11,  
which  is  incorrect.  The  reason  for  this  error  is  incorrect  sharing  
of  X  by  T12  3  and  T2.       Transac'ons  processing  and  concurrency  
Monday,  March  13,   36  
Management  
 Ways  of  Transac'on  Execu'on  …  
§ In  serial  execu'on    
– T2  read  the  value  of  X  wriZen  by  T1  (i.e.,  11)    
§ In  concurrent  execu'on    
– T2  read  the  same  value  of  X  (i.e.,  10)  as  T1  did  and  the  
update  made  by  T1  was  overwriZen  by  T2’s  update.  
§ This  is  the  reason  the  final  value  of  X  is  one  less  than  
what  is  produced  by  serial  execu'on.    
–    
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   37  
Management  
 Ways  of  Transac'on  Execu'on  …  
§ But,  why  concurrent  execu'on?  Reasons:    
– Many  systems  have  an  independent  component  to  
handle  I/O  like  DMA  (Direct  Memory  Access)  module.    
– CPU  may  process  other  transac'ons  while  one  is  in  I/
O  opera'on    
– Improves  resource  u'liza'on    
– Increases  the  throughput  of  the  system.    

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   38  
Management  
 Concurrency  Problems  in  DBMS  
§ When  mul'ple  transac'ons  execute  concurrently  in  
an  uncontrolled  or  unrestricted  manner,  then  it  
might  lead  to  several  problems.  Such  problems  are  
called  as  concurrency  problems.  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   39  
Management  
 Concurrency  Problems  ….  
§ Dirty  Read  Problem  –  Write-­‐Read  Conflict  
– Reading  the  data  wriZen  by  an  uncommiZed  
transac'on  
– There  is  always  a  chance  that  the  uncommiZed  
transac'on  might  roll  back  later.  
– uncommiZed  transac'on  might  make  other  
transac'ons  read  a  value  that  does  not  even  exist.  
This  leads  to  inconsistency  of  the  database.  
§ NOTE  :  Dirty  read  does  not  lead  to  inconsistency  always.    
– It  becomes  problema'c  only  when  the  uncommiZed  
transac'on  fails  and  roll  backs  later  due  to  some  reason.  
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   40  
Management  
 Concurrency  Problems  ….  

Here,  
• T1  reads  the  value  of  A.   • T2  reads  the  dirty  value  of  A  wriTen  by  the  
• T1  updates  the  value  of  A  in  the  buffer.   uncommiTed  transac'on  T1.  
• T2  reads  the  value  of  A  from  the  buffer.   • T1  fails  in  later  stages  and  roll  backs.  
• T2  writes  the  updated  the  value  of  A.   • Thus,  the  value  that  T2  read  now  stands  to  be  
• T2   commits.   Transac'ons  processing  aincorrect.   Therefore,  database  becomes   41  
nd  concurrency  
Monday,  March  13,  23  
• T1  fails  in  later  stages  and  rolls  back.   Management   inconsistent  
 Concurrency  Problems  ….  
§  Unrepeatable  Read  Problem  -­‐  Write-­‐Read  Conflict  

– This  problem  occurs  when  a  transac'on  gets  to  

read  unrepeated  i.e.  different  values  of  the  same  
variable  in  its  different  read  opera'ons  even  when  
it  has  not  updated  its  value  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   42  
Management  
 Concurrency  Problems  ….  

T1  reads  the  value  of  X  (=  10  say).  

T2  reads  the  value  of  X  (=  10).  
T1  updates  the  value  of  X  (from  10  to  15  say)  in  the  buffer.  
T2  again  reads  the  value  of  X  (but  =  15).  
In  this  example,  
T2  gets  to  read  a  different  value  of  X  in  its  second  reading.  
T2  wonders   how  the  value  of  X  got  changed  because  
Transac'ons  processing  and  concurrency  
Monday,  March  13,  23   according  to  it,  Management  
it  is  running  in  isola'on.   43  
 Concurrency  Problems  ….  
§ Lost  Update  Problem  -­‐  Write  –  Write  Conflict  

– This  problem  occurs  when  mul'ple  transac'ons  

execute  concurrently  and  updates  from  one  or  
more  transac'ons  get  lost  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   44  
Management  
 Concurrency  Problems  ….  

//Lost  update  problem  

NOTE-­‐  
• This  problem  occurs  whenever  
there  is  a  write-­‐write  conflict.  
• In  write-­‐write  conflict,  there  are  
two  writes  one  by  each  transac'on  
on  the  same  data  item  without  any  
Here,  
T1  reads  the  value  of  A  (=  10  say).   read  in  the  middle.  
T2  updates  the  value  to  A  (=  15  say)  in  the  buffer.  
T2  does  blind  write  A  =  25  (write  without  read)  in  the  buffer.  
T2  commits.  
When  T1  commits,  it  writes  A  =  25  in  the  database.  
In  this  example,  
T1  writes  the  over  wriTen  value  of  X  in  the  database.  
Thus,  Transac'ons  
update  fprom   T1  and  
rocessing   gets   lost.  
concurrency  
Monday,  March  13,  23   45  
Management  
 Concurrency  Problems  ….  
§ Phantom  Read  Problem    

– This  problem  occurs  when  a  transac'on  reads  

some  variable  from  the  buffer  and  when  it  reads  
the  same  variable  later,  it  finds  that  the  variable  
does  not  exist.  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   46  
Management  
 Concurrency  Problems  ….  
Here,  
• T1  reads  X.  
• T2  reads  X.  
• T1  deletes  X.  
• T2  tries  reading  X  but  
does  not  find  it.  
   

In  this  example,  
• T2  finds  that  there  does  not  exist  any  variable  X  when  it  tries  reading  
X  again.  
• T2  wonders  who  deleted  the  variable  X  because  according  to  it,  it  is  
running  in  isola'on.  
Monday,  March  13,  23  
Transac'ons  processing  and  concurrency  
47  
Management  
 Ways  of  Transac'on  Execu'on  …  
§ Advantages  of  using  the  concurrency  control    

– Wai'ng  'me  will  be  decreased.  

– Response  'me  will  decrease.  

– Resource  u'liza'on  will  increase.  

– System  performance  &  Efficiency  is  increased.  

Transac'ons  processing  and  concurrency  

Monday,  March  13,  23   48  
Management