Solución:
Ser consciente del manejo de transacciones (autocommit, explícito e implícito) para su base de datos puede evitar que tenga que restaurar datos desde una copia de seguridad.
Las transacciones controlan las declaraciones de manipulación de datos para garantizar que sean atómicas. Ser “atómico” significa que la transacción se produce o no. La única forma de señalar la finalización de la transacción a la base de datos es utilizando un COMMIT o ROLLBACK declaración (según ANSI-92, que lamentablemente no incluía la sintaxis para crear / comenzar una transacción, por lo que es específica del proveedor). COMMIT aplica los cambios (si los hay) realizados dentro de la transacción. ROLLBACK ignora las acciones que tuvieron lugar dentro de la transacción; muy deseable cuando una instrucción UPDATE / DELETE hace algo no intencionado.
Por lo general, las declaraciones DML (Insertar, Actualizar, Eliminar) individuales se realizan en una transacción de confirmación automática; se confirman tan pronto como la declaración se completa con éxito. Lo que significa que no hay oportunidad de revertir la base de datos al estado anterior a la ejecución de la declaración en casos como el suyo. Cuando algo sale mal, la única opción de restauración disponible es reconstruir los datos a partir de una copia de seguridad (siempre que exista una). En MySQL, la confirmación automática es sobre de forma predeterminada para InnoDB – MyISAM no admite transacciones. Puede desactivarse usando:
SET autocommit = 0
Una transacción explícita es cuando las declaraciones se envuelven dentro de un bloque de código de transacción definido explícitamente; para MySQL, eso es START TRANSACTION. También requiere un hecho explícito COMMIT o ROLLBACK declaración al final de la transacción. Las transacciones anidadas están fuera del alcance de este tema.
Las transacciones implícitas son ligeramente diferentes de las explícitas. Las transacciones implícitas no requieren la definición explícita de una transacción. Sin embargo, al igual que las transacciones explícitas, requieren una COMMIT o ROLLBACK declaración a ser suministrada.
Conclusión
Las transacciones explícitas son la solución más ideal: requieren una declaración, COMMIT o ROLLBACK, para finalizar la transacción, y lo que está sucediendo se indica claramente para que otros lo lean en caso de que sea necesario. Las transacciones implícitas están bien si se trabaja con la base de datos de forma interactiva, pero COMMIT Las declaraciones solo deben especificarse una vez que los resultados se hayan probado y se haya determinado completamente que son válidos.
Eso significa que debes usar:
SET autocommit = 0;
START TRANSACTION;
UPDATE ...;
… y solo usa COMMIT; cuando los resultados sean correctos.
Dicho esto, las declaraciones UPDATE y DELETE generalmente solo devuelven el número de filas afectadas, no detalles específicos. Convierta dichas declaraciones en declaraciones SELECT y revise los resultados para asegurarse de que sean correctos previo para intentar la instrucción UPDATE / DELETE.
Apéndice
Las declaraciones DDL (lenguaje de definición de datos) se confirman automáticamente; no requieren una declaración COMMIT. IE: tablas, índices, procedimientos almacenados, bases de datos y declaraciones de creación o modificación de vistas.
En InnoDB tú tienes START TRANSACTION;, que en este motor es la forma recomendada oficialmente de realizar transacciones, en lugar de SET AUTOCOMMIT = 0; (no use SET AUTOCOMMIT = 0; para transacciones en InnoDB a menos que sea para optimizar solo lectura actas). Comprometerse con COMMIT;.
Es posible que desee utilizar SET AUTOCOMMIT = 0; en InnoDB con fines de prueba y no precisamente para transacciones.
En MyISAM usted no tiene START TRANSACTION;. En este motor, utilice SET AUTOCOMMIT = 0; para transacciones. Comprometerse con COMMIT; o SET AUTOCOMMIT = 1; (La diferencia se explica en el comentario de ejemplo de MyISAM a continuación). También puede realizar transacciones de esta manera en InnoDB.
Fuente: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit
Ejemplos de transacciones de uso general:
/* InnoDB */
START TRANSACTION;
INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');
COMMIT; /* SET AUTOCOMMIT = 1 might not set AUTOCOMMIT to its previous state */
/* MyISAM */
SET AUTOCOMMIT = 0;
INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');
SET AUTOCOMMIT = 1; /* COMMIT statement instead would not restore AUTOCOMMIT to 1 */
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
La forma correcta de usar LOCK TABLES y UNLOCK TABLES con tablas transaccionales, como tablas InnoDB, es comenzar una transacción con SET autocommit = 0 (no START TRANSACTION) seguido de LOCK TABLES, y no llamar a UNLOCK TABLES hasta que confirme la transacción explícitamente. Por ejemplo, si necesita escribir en la tabla t1 y leer en la tabla t2, puede hacer esto:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;