Concurrencia y transacciones - script - ejemplo

From Ibbddunq

Contents 1 El esquema de BD 2 agregarEncomienda 3 pasarPeso 4 cantidadServiciosADestinos

El esquema de BD

  encomienda <nroEncomienda, cliente, peso>
  servicio <nroServicio, origen, destino, disponible, cargaTotal>
  encomiendaEnServicio <nroServicio, nroEncomienda>
  

agregarEncomienda

  procedure agregarEncomienda(elCliente, elPeso, elServicio)
  begin
    declare nroNuevaEnco integer;
    declare disponibleNuevo integer;
    declare cantEncomiendas integer;
    
    select max(nroEncomienda) + 1 from encomienda into nroNuevaEnco;
    select disponible - elPeso from servicio
     where nroServicio = elServicio
      into disponibleNuevo;
    select count(*) from encomiendaEnServicio where nroServicio = elServicio
      into cantEncomiendas;
    
    if (cantEncomiendas < 10) then
        insert into encomienda (nroEncomienda, cliente, peso)
        values (nroNuevaEnco, elCliente, elPeso);
        insert into encomiendaEnServicio (nroEncomienda, nroServicio)
        values (nroNuevaEnco, elServicio);
        update servicio set disponible = disponibleNuevo
         where nroServicio = elServicio;
    end if;
  end
  

con este stored procedure se puede

• mostrar un schedule de dos tx en el que el disponible de un servicio quede mal.
• llegar a la conclusión de qué condición deben tener dos ejecuciones concurrentes para que pueda haber anomalías de concurrencia: que estén agregando encomiendas en el mismo servicio.
• ver cómo tiene que ser un schedule para ser "malo": ambos deben leer el disponibleNuevo antes de que cualquiera de ellos haga update. Ver que si es serial, entonces naturalmente es "bueno", el problema está relacionado con cruces.
• corregir el update seteando disponible - elPeso, y sacando el select del servicio de arriba. Aclarar que toma el disponible en el momento que se ejecuta el update. Llegar a la conclusión de que se arregla la anomalía observada antes ...
• ... pero queda otra: puedo poner 11 encomiendas en un servicio. Esta la vamos a tener que solucionar reservando un recurso, o sea, que sea válido que yo diga "yo agarro un servicio y hasta que yo no termine nadie puede hacer nada con el servicio"

pasarPeso

   procedure pasarPeso (servDesde, servHacia, cuanto)
   begin
     declare nuevoDisponible;
   
     start transaction
     update servicio set disponible = disponible + cuanto 
      where nroServicio = servDesde;
     select disponible - cuanto from servicio 
      where nroServicio = servHacia
       into nuevoDisponible;
     
     if (nuevoDisponible >= 0) then
         update servicio set disponible = disponible - cuanto
          where nroServicio = servHacia;
         commit;
     else
         rollback;
     end if;
   end

Un ejemplo armadito: supongamos esta instancia de servicio (considerando solamente nro y peso)

  104, 500
  123, 200
  148, 33000
  149, 50
  150, 820

Veamos posibles transacciones concurrentes

  pasarPeso(desde 150, hacia 104, 320) con pasarPeso(desde 148, hacia 104, 400)

en este caso el 104 me puede quedar con disponible negativo, p.ej. ver este schedule

   150 --> 104 - 320                 148 --> 104 - 400
       start
       update del 150
       select del 104 (da 180)
                                       start
                                       update del 148
                                       select (da 100)
       if
       update del 104
          (deja 180)
       commit
       fin
                                       if
                                       update del 104 (deja -220)
                                       commit
                                       fin

obsérvese que en la 2da transacción, el valor de disponible que leyó el select no coincide con el que tomó el update, o el que tomaría en un select puesto en el mismo lugar del update. Esa diferencia es el non repeatable read.

  pasarPeso(desde 123, hacia 149, 120) con pasarPeso(desde 150, hacia 123, 240)

en este caso el 104 me puede quedar con disponible negativo, p.ej. ver este schedule

   123 --> 149 - 120                150 --> 123 - 240
       start
       update del 123 a 320
       select (da -70)
                                       start
                                       update del 150
                                       select del 123 
                                          (da ... ¿cuánto? ponele 80)
                                       if
                                       update del 123 ... debe esperar
       if
       rollback, 
         deja 200 en el 123
       fin
                                       ahora sí update ... oops a -40
                                       commit
                                       fin

sí, el rollback puede traer efectos cruzados extraños ... más sobre esto en SO. En IBD nos alcanza con ver que hay un problema, y con resolverlo en forma robusta usando lockeos.

(si da)

  pasarPeso(desde 123, hacia 149, 20) con pasarPeso(desde 149, hacia 123, 40)

este hacerlo cuando ya se haya hecho el lockeo

   123 --> 149 - 120                149 --> 123 - 240
       start
       update del 123 
                                       start
                                       update del 149
       select for update
         del 149 ... debe esperar
                                       select for update del 123 
                                          ... deadlock

cantidadServiciosADestinos

function cantidadServiciosADestinos(dest1, dest2) begin

   declare cantServ1, cantServ2;
   
   select count(*) from servicio where destino = dest1 into cantServ1;
   select count(*) from servicio where destino = dest2 into cantServ2;
   return cantServ1 + cantServ2;

end

ponele esta instancia de servicio (sólo nroServicio, origen, destino)

   104, La Plata, Brandsen
   148, Ranchos, Chascomus
   150, La Plata, Chascomus
   151, La Plata, Chascomus

y tengo dos tx concurrentes

   cantidadS...(Brandsen,Chascomus)
   primer select --> 1
                                  update servicio set destino = 'Brandsen'
                                  where nroServicio in (150,151);
   segundo select --> 1
   return --> ¡¡2!!
   

lo mismo con

   cantidadS...(Chascomus,Brandsen)

da 6.