Bloqueo de tabla, transacciones o ambas?

[ocp001a]

Hola.

Hasta hoy he realizado muchos proyectos principalmente de tipo consulta o como mucho administración (alta, baja modificación) de usuarios y cosas sencillas por lo que usar BD tipo MyISAM me era suficiente.

pero ahora tengo un proyecto un tanto delicado (manejo de dinero) y he comenzado a manejar Innodb y a documentarme en transacciones.

Mi situación es la siguiente:

Teniendo una cuenta con x cantidad de dinero disponible, varios usuarios (entre 30 y 50 por lo menos) tomarán parte de ese dinero para comprar, por lo cual antes de hacer la compra debo verificar si hay suficiente dinero disponible en la cuenta. La concurrencia será pan de cada día.


En los manuales que he leído sobre transacciones siempre veo el ejemplo de una transferencia de dinero de A a B:
Si se completa quitar dinero a A,
Suma dinero a B,
si se completan ambas ejecuta.

Pero no he encontrado (seguramente he buscado mal) ejemplos concretos para mi caso.

Mi duda concreta es la siguiente:

Usar transacciones tipo:

Código PHP:

mysql_query("BEGIN");
if(($dinero=mysql_result(mysql_query("select disponible from cuentas where cuenta=$id"),0))>$cantidad){
       ///si hay más dinero disponible que la cantidad requerida realiza la operacion de compra
       //me conecto a un Web Service con otro sistema
      if(mysql_query("update cuentas set disponible=(disponible-$cantidad) where id=$id")){
             mysql_query("COMMIT");
             echo "Compra realizada";
      }
       else{
             mysql_query("ROLLBACK");
             echo "No fue posible realizar la compra";
      }
}else
      echo "No hay dinero suficiente";
 

¿Ésta forma es efectiva? ¿O es posible (como me figuro) que antes de hacer el update otro usuario lo haga?


Otra cosa que he pensado es hacer toda la operación en una sola sentencia tipo:

Código PHP:

mysql_query("IF (select (disponible from cuentas where id=$id)>$cantidad) THEN update cuentas set disponible=(disponible-$cantidad)");
//realizar la operación de compra por Web Service con otro sistema, si es exitosa termino, si no, devuelvo a la cuenta el dinero
 

O bien si definitivamente necesito bloquear completamente la tabla para realizar la operación.




Les agradezco su atención.

[Uncontroled_Duck]

Iniciado por ocp001a

[...] ¿Ésta forma es efectiva? ¿O es posible (como me figuro) que antes de hacer el update otro usuario lo haga?

O bien si definitivamente necesito bloquear completamente la tabla para realizar la operación.

De por si, InnoDB se encarga de bloquear el registro (no la tabla) que se está ejecutando en una session concreta.

Por lo que, sea transacción o no, hasta que no termina la transacción/operación no aparecerá reflejada para otro usuario. Pero otro usuario con su session si puede hacer operaciones simultáneamente en la misma tabla.

Ahora bien, como comentas, los ejemplos que hay, son de A a B, y si existe un error, no se completa la transacción. Pero esque esa es la base de esta operación. Todo lo que se quiera añadir son casos particulares.

El ejemplo básico es así:

Código PHP:

  mysql_query("START TRANSACTION");
mysql_query("UPDATE cuentas SET money = money-100 WHERE id = '1'");
mysql_query("UPDATE cuentas SET money = money+100 WHERE id = '2'");
 
if( mysql_error() )
{
    mysql_query("ROLLBACK");
}
else
{
    mysql_query("COMMIT");
} 
   

Por curiosidad he probado con operaciones entre el START y el COMMIT y se comporta perfectamente.

Ya sería adaptarlo a tu caso.

Un saludo,

[ocp001a]

Gracias por responder.

El hecho de que si se ejecutan todas las sentencias correctamente se aplique el COMMIT me queda claro, pero como en el primer caso que planteo hago la operación en dos sentencias separadas, me queda la inquietud de si puede pasar este caso de concurrencia:

0.001 ms: Usuario A necesita $100, consulta si hay disponibilidad, y sí, hay $150 disponibles.
0.002 ms: Usuario B necesita $80, consulta si hay disponibilidad, y sí, (todavía hay $150 disponibles).
0.003 ms: Usuario A actualiza y resta sus $100, ahora quedan $50 disponibles
0.004 ms: Usuario B acaba de consultar que quedan $150, así que actualiza y resta $80, pero como sólo quedaron $50, resulta en un -30 (y por tanto un fallo).

En el caso de una transferencia de A a B no hay ejemplo de concurrencia, sólo ejemplo de que si la conexión cayera o hubiera un fallo la operación se cancela, pero siempre pensando en una sola sesión.

Ahora bien, si la transacción junta ambas sentencias y las ejecuta prácticamente al mismo tiempo bloqueando la tabla para cada operación, estaría perfecto, pero he leído que las transacciones bloquean en diferentes modos las tablas, según sea consulta, actualización, borrado etc. y me queda el temor de que dos o más usuarios consultando al mismo tiempo el mismo saldo obtengan lecturas engañosas.

Perdón si mis preguntas resultan impertinentes, es sólo que este proyecto va a administrar grandes sumas de dinero y quiero estar completamente seguro de qué métodos debo emplear.

[Uncontroled_Duck]

Iniciado por ocp001a

[...] y me queda el temor de que dos o más usuarios consultando al mismo tiempo el mismo saldo obtengan lecturas engañosas.

Perdón si mis preguntas resultan impertinentes, es sólo que este proyecto va a administrar grandes sumas de dinero y quiero estar completamente seguro de qué métodos debo emplear.

En absoluto me parecen impertinentes y además es normal que quieras estar seguro de que la aplicación va a funcionar perfectamente.

A ver, con la pruebas que hice ayer, planteé un caso similar pero de una única transferencia.

Lo que pude observar, es que dentro de la transacción (start / commit) puedes hacer distintas consultas con lecturas reales de la base de datos. Con esto quiero decir que si alguien cambiara algo mientras el usuario estaría operando, podrías hacer una consulta antes de y otra después de...

Pongo como ejemplo el caso que has sugerido.

Código PHP:

0.001 ms: 
    Usuario A necesita $100, consulta si hay disponibilidad, 
    y sí, hay $150 disponibles.
 
0.002 ms:
    Usuario B necesita $80, consulta si hay disponibilidad, 
    y sí, (todavía hay $150 disponibles).
 
0.003 ms: 
    //Usuario A actualiza y resta sus $100, ahora quedan $50 disponibles
    Usuario A resta sus $100, ahora quedan $50 disponibles
    ---- Antes de confirmar la transacción
    Consultas si ahora esta la cantidad esperada $50
    Si es la esperada COMMIT;
    Si no es la esperada ROOLBACK; y notificas que hay un cambio.
 
0.004 ms: 
    Usuario B acaba de consultar que quedan $150, 
    //así que actualiza y resta $80,
    ---- Antes de confirmar la transacción
    Consultas si ahora esta la cantidad esperada $70
    Si es la esperada COMMIT;
    Si no es la esperada ROOLBACK; y notificas que hay un cambio.
    //pero como sólo quedaron $50, resulta en un -30 (y por tanto un fallo).
 

Sabemos que nuestra transacción no se hace visible al resto de usuarios hasta que no ejecutemos el COMMIT, pero dentro de la misma transacción si podemos hacer lecturas reales de las cantidades que hay en la DB (incluida nuestra transacción).

Si mientras tenemos abierta la transacción, alguien completa otra, nos aparecerá ese cambio inesperado en la consulta que haremos justo antes de confirmar dicha transacción. Lo cual nos da la oportunidad de actuar en consecuencia.

Todo esto se deduce a raíz de la documentación de MySql, libros que tengo al respecto y pruebas que he realizado con PHPUnit. Pero para verificar toda esta teoría no hay nada mejor que realizar las pruebas por ti mismo, con los casos que estimes oportunos.

PD.: Con PHPUnit puedes hacer este tipo de pruebas y ver como y donde falla la aplicación según el comportamiento que buscas.

Saludos,

[ocp001a]

Ok, muchas gracias, me has dado muy buenos tips, investigaré sobre PHPUnit