tipos - transacciones sql

MySQL: Transacciones vs Tablas de bloqueo

Question

Estoy un poco confundido con las transacciones y las tablas de bloqueo para garantizar la integridad de la base de datos y asegurarme de que SELECT y UPDATE permanecen sincronizados y ninguna otra conexión interfiere con él. Necesito:

SELECT * FROM table WHERE (...) LIMIT 1

if (condition passes) {
   // Update row I got from the select 
   UPDATE table SET column = "value" WHERE (...)

   ... other logic (including INSERT some data) ...
}

Necesito asegurarme de que ninguna otra consulta interfiera y realice el mismo SELECT (leyendo el ''valor anterior'' antes de que la conexión termine de actualizar la fila.

Sé que puedo LOCK TABLES table para asegurarme de que solo 1 conexión está haciendo esto a la vez, y desbloquearlo cuando termine, pero eso parece exagerado. ¿Lo mismo sucedería en una transacción (asegurándose de que ninguna otra conexión intente el mismo proceso mientras otra todavía está procesando)? ¿O un SELECT ... FOR UPDATE o SELECT ... LOCK IN SHARE MODE será mejor?

Answer 1

Bloquear tablas impide que otros usuarios de bases de datos afecten a las filas / tablas que ha bloqueado. Pero los bloqueos, por sí mismos, NO garantizarán que su lógica salga en un estado consistente.

Piensa en un sistema bancario. Cuando paga una factura en línea, hay al menos dos cuentas afectadas por la transacción: su cuenta, de la cual se toma el dinero. Y la cuenta del receptor, en la que se transfiere el dinero. Y la cuenta del banco, en la que felizmente depositarán todas las tarifas de servicio cargadas en la transacción. Dado (como todo el mundo sabe en la actualidad) que los bancos son extraordinariamente estúpidos, digamos que su sistema funciona así:

$balance = "GET BALANCE FROM your ACCOUNT";
if ($balance < $amount_being_paid) {
    charge_huge_overdraft_fees();
}
$balance = $balance - $amount_being paid;
UPDATE your ACCOUNT SET BALANCE = $balance;

$balance = "GET BALANCE FROM receiver ACCOUNT"
charge_insane_transaction_fee();
$balance = $balance + $amount_being_paid
UPDATE receiver ACCOUNT SET BALANCE = $balance

Ahora, sin bloqueos ni transacciones, este sistema es vulnerable a varias condiciones de carrera, la mayor de las cuales es la realización de pagos múltiples en su cuenta, o la cuenta del receptor en paralelo. Si bien su código ha recuperado su saldo y está haciendo huge_overdraft_fees () y lo que sea, es muy posible que algún otro pago ejecute el mismo tipo de código en paralelo. Recuperarán su saldo (digamos, $ 100), realizarán sus transacciones (saquen los $ 20 que están pagando y los $ 30 que les están gastando), y ahora ambos caminos de código tienen dos saldos diferentes: $ 80 y $ 70. Dependiendo de cuáles finalizan, terminará con cualquiera de esos dos saldos en su cuenta, en lugar de los $ 50 que debería haber terminado ($ 100 - $ 20 - $ 30). En este caso, "error bancario a su favor".

Ahora, digamos que usas bloqueos. Su pago de factura ($ 20) llega primero a la tubería, por lo que gana y bloquea el registro de su cuenta. Ahora tiene uso exclusivo y puede deducir los $ 20 del saldo, y devolver el nuevo saldo en paz ... y su cuenta termina con $ 80 como se esperaba. Pero ... uhoh ... Intentas actualizar la cuenta del receptor, y está bloqueado, y bloqueado más de lo que permite el código, agotando tu transacción ... Estamos lidiando con bancos estúpidos, así que en lugar de tener el error correcto manejo, el código simplemente saca una exit() , y tus $ 20 desaparecen en una nube de electrones. Ahora está ganando $ 20, y aún le debe $ 20 al receptor, y su teléfono se vuelve a embargar.

Entonces ... ingrese transacciones. Empieza una transacción, debita su cuenta $ 20, intenta darle crédito al receptor con $ 20 ... y algo explota de nuevo. Pero esta vez, en lugar de exit() , el código solo puede rollback , y poof, tus $ 20 se vuelven a agregar mágicamente a tu cuenta.

Al final, se reduce a esto:

Los bloqueos evitan que nadie más interfiera con los registros de la base de datos con los que está tratando. Las transacciones evitan que los errores "posteriores" interfieran con las acciones "anteriores" que haya realizado. Ninguno de los dos puede garantizar que las cosas funcionen bien al final. Pero juntos, lo hacen.

en la lección de mañana: The Joy of Deadlocks.

Answer 2

Desea un SELECT ... FOR UPDATE o SELECT ... LOCK IN SHARE MODE dentro de una transacción, como usted dijo, ya que normalmente los SELECT, sin importar si están en una transacción o no, no bloquearán una tabla. El que elija dependerá de si desea que otras transacciones puedan leer esa fila mientras su transacción está en progreso.

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

START TRANSACTION WITH CONSISTENT SNAPSHOT no le servirá, ya que otras transacciones aún pueden aparecer y modificar esa fila. Esto se menciona justo en la parte superior del enlace a continuación.

Si otras sesiones actualizan simultáneamente la misma tabla, [...] puede ver la tabla en un estado que nunca existió en la base de datos.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Answer 3

Los conceptos de transacción y bloqueos son diferentes. Sin embargo, la transacción usó bloqueos para ayudarlo a seguir los principios de ACID. Si desea que la tabla evite que otros lean / escriban en el mismo punto de tiempo mientras lee / escribe, necesita un bloqueo para hacer esto. Si desea asegurarse de la integridad y consistencia de los datos, es mejor que use las transacciones. Creo que los conceptos combinados de niveles de aislamiento en las transacciones con bloqueos. Busque los niveles de aislamiento de las transacciones, SERIALIZE debe ser el nivel que desee.

Answer 4

Te confunden con bloqueo y transacción. Son dos cosas diferentes en RMDB. El bloqueo impide las operaciones simultáneas mientras que la transacción se centra en el aislamiento de datos. Echa un vistazo a this excelente artículo para la aclaración y una solución elegante.

Answer 5

Tuve un problema similar al intentar un IF NOT EXISTS ... y luego realizar un INSERT que causó una condición de carrera cuando varios hilos estaban actualizando la misma tabla.

Encontré la solución al problema aquí: Cómo escribir INSERT IF NOT EXISTS consultas en SQL estándar

Me doy cuenta de que esto no responde directamente a su pregunta, pero el mismo principio de realizar una comprobación e insertar como una sola declaración es muy útil; deberías poder modificarlo para realizar tu actualización.

Answer 6

Yo usaría un

START TRANSACTION WITH CONSISTENT SNAPSHOT;

para empezar, y un

COMMIT;

para terminar con.

Cualquier cosa que haga en el medio está aislada de los otros usuarios de su base de datos si su motor de almacenamiento admite transacciones (que es InnoDB).