Transacciones en Mysql

[luismaciasalejos]

Buenas!

Tengo un problema con el manejo de una transaccion, mis tablas son Innodb, tengo dos tablas temporales que se crean antes de abrir la transaccion.
El problema es que si yo aplico un rollback, no me hace la eliminacion de las actualizaciones realizadas.

El lenguaje es PHP y esta validando que no haya errores de sintaxis, por lo que todos los movimientos que se enlistan estan correctos, pero por una extraña razon al ejecutar el rollback este se ejecuta sin problemas y sin error pero simplemente no hace nada:

Saque un Log de todos los movimientos que se ejecutan para ver si alguien me puede ayudar a encontrar el problema:

[2012-06-21 22:55:22 Resource id #11 CREATE temporary TABLE tmp_polizadet2 (idpolizadet int unsigned auto_increment,
idcuentacontable int unsigned,
debe decimal (15,2),
haber decimal(15,2),
evaluado char(1),
primary key (idpolizadet));
[2012-06-21 22:55:22 Resource id #11 CREATE temporary TABLE tmp_polizadet (idpolizadet int unsigned auto_increment,
idcuentacontable int unsigned,
debe decimal (15,2),
haber decimal(15,2),
evaluado char(1),
primary key (idpolizadet));
[2012-06-21 22:55:22 Resource id #11 START TRANSACTION;
[2012-06-21 22:55:22 Resource id #11 INSERT INTO conta_poliza (idusuario, fechahora,estatus,idusuarioafecta,fechahoraafecta)
VALUES (1, '2012-06-21 22:55:22','A',1,now())
[2012-06-21 22:55:22 Resource id #11 INSERT INTO caja_movimiento (idcuenta, idpoliza, fechahora, ingreso,nombre,rfc,direccion,idcuentaautorizo,fech ahoraautorizo,piedepagina,idcolonia,telefono,email 1,email2,cp, idgrado, idcicloescolar)
values (1,55, '2012-06-21 22:55:22', '1', '','',' ',1,now(),'',0,'','','','', 2, 12)
[2012-06-21 22:55:22 Resource id #11 INSERT INTO al_alumnopago (idalumno, idcajamovimiento,fecha) VALUES ('5004',55,now())
[2012-06-21 22:55:22 Resource id #11 INSERT INTO al_alumnopagodet (idalumnopago, idtipopago, importe, uno, dos, tres)
VALUES ('43', '1', '5100', '', '', '');
[2012-06-21 22:55:22 Resource id #11 INSERT INTO caja_movimientodet (idcajamovimiento, idtipopago, importe, cheque, banco, referencia, nombre)
VALUES ('55', '1', '5100', '', '', '', '');
[2012-06-21 22:55:22 Resource id #11 UPDATE al_alumnocobro SET pagado = '1' WHERE idalumnocobro = '95';
[2012-06-21 22:55:22 Resource id #11 insert into al_alumnopagocobro (idalumnocobro,idalumnopago) values ('95',43);
[2012-06-21 22:55:22 Resource id #11 INSERT INTO conta_polizadet (idpoliza, idcuentaorigen, debe, haber)
SELECT 55, idcuentacontable, IF(importe >= 0, importe, '0'), IF(importe < 0, abs(importe), '0')
FROM al_alumnocobro
WHERE idalumnocobro = '95';
[2012-06-21 22:55:22 Resource id #11 update al_reinscripcion
set idcajamovimiento=55,
idgrupo = 4
where idreinscripcion=37
[2012-06-21 22:55:22 Resource id #11 DELETE FROM tmp_polizadet
[2012-06-21 22:55:22 Resource id #11 DELETE FROM tmp_polizadet2
[2012-06-21 22:55:22 Resource id #11 INSERT INTO tmp_polizadet (idcuentacontable, debe, haber, evaluado)
SELECT idcuentaorigen, debe, haber, '0'
FROM conta_polizadet
WHERE idpoliza = 55;
[2012-06-21 22:55:22 Resource id #11 SELECT idcuentacontable, debe, haber, evaluado
FROM tmp_polizadet;
[2012-06-21 22:55:22 Resource id #11 SELECT idcuentacontabledestino, tipodestino, porcentaje
FROM conta_parametrizacion
WHERE idcuentacontableorigen = 2 AND
tipoorigen = 'D';
[2012-06-21 22:55:22 Resource id #11 insert into tmp_polizadet2 (idcuentacontable, debe, haber, evaluado)
values (2, 5100.00, 0, '1');
[2012-06-21 22:55:22 Resource id #11 DELETE FROM tmp_polizadet;
[2012-06-21 22:55:22 Resource id #11 INSERT INTO tmp_polizadet SELECT * FROM tmp_polizadet2;
[2012-06-21 22:55:22 Resource id #11 DELETE FROM tmp_polizadet2;
[2012-06-21 22:55:22 Resource id #11 SELECT count(*) as conta FROM tmp_polizadet WHERE evaluado = '0';
[2012-06-21 22:55:22 Resource id #11 SELECT count(*) as conta FROM tmp_polizadet WHERE evaluado = '0';
[2012-06-21 22:55:22 Resource id #11 SELECT sum(debe) as debe, sum(haber) as haber FROM tmp_polizadet;
[2012-06-21 22:55:22 Resource id #11 SELECT conta_cuentacontable.cuenta, conta_polizadet.debe, conta_polizadet.haber
FROM conta_cuentacontable, conta_polizadet
WHERE conta_cuentacontable.idcuentacontable = conta_polizadet.idcuentaorigen AND
conta_polizadet.idpoliza = 55;
[2012-06-21 22:55:22 Resource id #11 SELECT texto FROM io_parametro WHERE nombre = 'AplicaContabilidad';
[2012-06-21 22:55:22 Resource id #11 ROLLBACK;

De antemano les agradezco toda su valiosa ayuda.

Saludos.

[quimfv]

De entrada podrias crear las temporales dentro de la transacción, pero no es ese el problema...

Estas seguro que las tablas son Innodb incluso las temporales...

SHOW CREATE TABLE tmp_polizadet2

http://forums.mysql.com/read.php?22,388258,388452

[wchiquito]

Hola luismaciasalejos,

En el siguiente ejemplo todo funciona como se espera, usé algunas de las estructuras que mostrabas así con algunos datos de prueba.

Código MySQL:

DROP TABLE IF EXISTS `al_alumnopagocobro`;
 
CREATE TABLE `al_alumnopagocobro` (
`idalumnocobro` char(2) not null,
`idalumnopago` int not null)
ENGINE=innodb;
 
DROP TEMPORARY TABLE IF EXISTS `tmp_polizadet`;
 
CREATE TEMPORARY TABLE `tmp_polizadet` (
`idpolizadet` int unsigned auto_increment,
`idcuentacontable` int unsigned,
`debe` decimal (15, 2),
`haber` decimal(15, 2),
`evaluado` char(1),
primary key (`idpolizadet`))
ENGINE=innodb;
 
DROP TEMPORARY TABLE IF EXISTS `tmp_polizadet2`;
 
CREATE TEMPORARY TABLE `tmp_polizadet2` (
`idpolizadet` int unsigned auto_increment,
`idcuentacontable` int unsigned,
`debe` decimal (15, 2),
`haber` decimal(15, 2),
`evaluado` char(1),
primary key (`idpolizadet`))
ENGINE=innodb;
 
start transaction;
 
insert into `al_alumnopagocobro` (`idalumnocobro`, `idalumnopago`)
values
('95', 43), ('96', 44);
 
insert into `tmp_polizadet` (`idcuentacontable`, `debe`, `haber`, `evaluado`)
values
(1, 100, 200, 's'), (2, 300, 400, 'n');
 
insert into `tmp_polizadet2` (`idcuentacontable`, `debe`, `haber`, `evaluado`)
values
(3, 500, 600, 'n'), (4, 700, 800, 's');
 
select
   `idalumnocobro`,
   `idalumnopago`
from
   `al_alumnopagocobro`;
 
select
   `idcuentacontable`,
   `debe`,
   `haber`,
   `evaluado`
from
   `tmp_polizadet`;
 
select
   `idcuentacontable`,
   `debe`,
   `haber`,
   `evaluado`
from
   `tmp_polizadet2`;
 
rollback;
 
select
   `idalumnocobro`,
   `idalumnopago`
from
   `al_alumnopagocobro`;
 
select
   `idcuentacontable`,
   `debe`,
   `haber`,
   `evaluado`
from
   `tmp_polizadet`;
 
select
   `idcuentacontable`,
   `debe`,
   `haber`,
   `evaluado`
from
   `tmp_polizadet2`;
 
DROP TEMPORARY TABLE IF EXISTS `tmp_polizadet2`;
DROP TEMPORARY TABLE IF EXISTS `tmp_polizadet`;
DROP TABLE IF EXISTS `al_alumnopagocobro`;
 

Cuando dices:

el rollback este se ejecuta sin problemas y sin error pero simplemente no hace nada.

El "no hace nada.", ¿significa qué los datos persisten en las tablas?

[luismaciasalejos]

Buenas!
Gracias por su respuestas!

Si quimfv, las tablas son innodb, todas las relacionadas, exceptuando las tmp que se crean, que no se si se crean como myisam o innodb (pero bueno, no importan porque son temporales).

Las cree afuera de la transaccion porque en la documentacion del mysql habla de que la instruccion CREATE TABLE, ALTER TABLE y TRUNCATE TABLE crean un commit antes de su ejecucion. De hecho por eso tengo los DELETE FROM tmp_polizadet y tmp_polizadet2 y no un TRUNCATE (por si la instruccion generaba un COMMIT sin importar que fueran temporales.

Hola wchiquito, si, el "no hace nada" es que las tablas que fueron insertadas permanecen con los datos de insercion. No deshace los cambios.

Supuse que era por que, como dije, el create table o el truncate me estaba haciendo un commit, de manera que saque la creacion de las temporales fuera de la transaccion y cambie los truncate por delete.

Los datos persisten.

Voy a hacer la prueba creando las temporales como innodb (por si el motor tiene por default MyIsam) y les cuento.

[luismaciasalejos]

que extraño!

Las tablas temporales cambiandolas a Innodb (create temporary table ... engine = innodb) la transaccion se ejecuta y aplica el ROLLBACK de manera correcta.

Muchas gracias por su tiempo, aunque mi neurona no entiende porque, si dice la documentacion que si hay tablas myisam estas ultimas no se aplicara el rollback...

[wchiquito]

Hola luismaciasalejos,

Algunos comentarios:

Es importante leer la documentación de la versión que usas de MySQL (asumo que usas la versión 5.5.x), esto porque pueden existir diferencias en los comportamientos.

0. Es importante el ENGINE de las tablas temporales para determinar si soportan transacciones.

1. En la documentación se menciona los siguiente 13.1.17. CREATE TABLE Syntax:

Note

CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.

Por lo que tus tablas temporales se pueden crear dentro de la transacción, sin embargo, personalmente también las crearía fuera a menos que por alguna causa en específico las necesitara dentro de la transacción.

2. Leer la sección 13.3.3. Statements That Cause an Implicit Commit

3. Dentro de una misma transacción puedes tener tablas MyISAM e InnoDB, luego de distintas operaciones (INSERT/UPDATE/DELETE) si aplicas un ROLLBACK solamente las tablas InnoDB serán afectadas, las MyISAM no.

Espero te sea útil.

[luismaciasalejos]

wchiquito. Gracias por tu tiempo en contestar. El asunto es que las tablas temporales yo las utilizaba en otro proceso, deseaba que actualizara e insertara en las tablas del sistema y las temporales me las llenara y dejara intactas para poder analizar y entregarle un reporte al usuario de que parte debia de parametrizar para evitar el problema.

Segun la documentacion, efectivamente es la version 5.5.2 (creo), menciona que las transacciones solo afectan a las tablas INNODB, por lo que las tablas MYISAM no se aplicaria un ROLLBACK.

Mi duda ahora es porque la transaccion no fue deshecha como debia?
Lo correco seria decir en la documentacion: "Si existe al menos una tabla (temporal o no) que no sea INNODB la transaccion no se podra garantizar que se ejecute."

Bueno, voy a hacer otras pruebas y enviare un correo a soporte de MySql a ver que me contestan, de cualquier manera los retroalimentare, porque seguro a mas de alguno le podra pasar lo mismo.

Saludos a todos y gracias nuevamente.