Problema al hacer UPDATE en innoDB desde un storedprocedure

Hice un StoredProcedure en Mysql que segun las solicitudes pendientes a cada almacen, determina la disponibilidad; el problema esque en algunos productos me marca el error: "Lock wait timeout exceeded; try restarting transaction" pero con otros productos si regresa el resultado. Al inicio tenia todas las consultas en un solo SELECT y hacia las operaciones, probe hacer los SELECT por separado, guardarlos en variables diferentes y hacer las operaciones con las variables al final; pero me sigue marcando el error.

StoredProcedure:

Código MySQL :

DELIMITER $$

DROP FUNCTION IF EXISTS `GetDisponibles` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GetDisponibles`(idp VARCHAR(45),ida INT) RETURNS int(11)
BEGIN

DECLARE Disponible INT;
DECLARE ReserCred INT;
DECLARE ReserCont INT;
SELECT COALESCE(SUM(VP.cantidad),0) FROM Venta V INNER JOIN VentaProducto VP ON V.id_venta=VP.id_venta LEFT JOIN Traspaso T ON V.id_venta=T.id_venta WHERE V.cancelado=0 AND VP.id_almacen_traspaso>0 AND VP.id_producto=idp AND VP.id_almacen_traspaso=ida AND T.id_venta IS NULL INTO ReserCred;
SELECT COALESCE(SUM(VP.cantidad),0) FROM Venta_Contado V INNER JOIN VentaProductocontado VP ON V.id_venta=VP.id_venta LEFT JOIN Traspaso T ON VP.id_venta=T.id_venta WHERE V.cancelado=0 AND VP.id_almacen_traspaso<>0  AND VP.id_producto=idp AND VP.id_almacen_traspaso=ida AND T.id_venta IS NULL INTO ReserCont;
SELECT (I.existencia-(ReserCred+ReserCont)) FROM Inventario I WHERE I.id_almacen=ida AND I.id_producto=idp INTO Disponible;
RETURN Disponible;

END $$

DELIMITER ;

y este lo necesito usar en este UPDATE:

Código MySQL :

UPDATE Inventario SET disponible=COALESCE(GetDisponibles('LITPOP3',1),0) WHERE id_almacen=1 AND id_producto='LITPOP3';

¿sera que tu consulta es demasiado pesada?

eso explicaría que a ciertos resultados (con menos filas) resulte y en otros no, prueba haciendo la consultas por partes y usa tablas temporales para alivianar la consulta

"Lock wait timeout exceeded; try restarting transaction"




PD: ¡¡INVOCO A MAIKEL!!

La cosa va asi:

Cuando una tienda hace un pedido y no tiene en su inventario solicita a <bodega / otra tienda> el producto, los cuales son atendidos por un traspaso, el cual se encarga de decrementar el inventario de uno e incrementar el inventario del otro; por lo que para saber cuantos productos hay en existencia se necesita saber cuantos pedidos hay sin ser atendidos, dicho de otra manera, cuantos traspasos faltan por hacer y segun los calculos del storeproceudure actualizar la nueva disponibilidad.

por eso te digo, supongo que la cunsulta en si debe ser compleja, y que cada select debe estar cruzado hasta con mi gata.

las tablas temporales se guardan en la ram por tanto son de mas facil acceso, esa tabla temporal llenala con la consulta mas cabrona que tengas y asi alivianaras el INSERT que de por si requiere mas recursos

una vestuve problemasconINSERT demasiado complejos y eso fue lo que hice


para mas informacion:


INVOCO A MAIKEL¡¡¡


basado en mi experiencia (ni mucha, ni poca) es lo que se me ocurre

Pues el problema no son las consultas, de hecho las mismas consultas las hago desde el modulo de venta para que las vendedoras sepan cuantos productos hay disponibles en otra tienda o almacen, para poder solicitar el traspaso a su tienda; de hecho estoy probando el storedprocedure y funciona:

Código MySQL :

SELECT GetDisponibles('ESTACR4900B',4);

Pero con el UPDATE ya no funciona:

Código MySQL :

UPDATE Inventario SET existencia=existencia-1,disponible=GetDisponibles('ESTACR4900B',4)-1 WHERE id_almacen=4 AND id_producto='ESTACR4900B';

Creo que el problema va mas con el bloqueo que hace innodb a la tabla inventario al hacer el UPDATE, lo hice en storedprocedure para evitar tener SELECT y UPDATE en la misma sentencia, pero creo que innodb bloquea antes de poder hacer el SELECT.

Lo que se me hace curioso es que solo es con algunos productos, porque hice pruebas con la copia que tengo en mi laptop y funciono todo bien con los productos que probe.

Disculpen mi error, no estaba hablando de un SP sino de una Funcion en Mysql. Acabo de programar otra funcion para corregir todos los inventarios, ya que al fallar la funcion anterior se hizo un desastre con los inventarios; y resulta que esta si funciono

UPDATE para corregir el inventario:

Código MySQL :

UPDATE Inventario I SET existencia=GetExistenciaMovimientos(I.id_producto,I.id_almacen,'2010-03-22 00:00:00','2010-03-29 00:00:00');

GetExistenciaMovimientos:

Código MySQL :

DELIMITER $$

DROP FUNCTION IF EXISTS `GetExistenciaMovimientos` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GetExistenciaMovimientos`(idp VARCHAR(45),ida INT,fechai VARCHAR(20),fechaf VARCHAR(20)) RETURNS int(11)
BEGIN


DECLARE ExistenciaAnterior INT;
DECLARE Entradas INT;
DECLARE Salidas INT;
DECLARE TraspasosEntradas INT;
DECLARE TraspasosSalida INT;
DECLARE Creditos INT;
DECLARE Contados INT;
DECLARE OrdenCompra INT;
DECLARE AjustesInventarioSalida INT;
DECLARE AjustesInventarioEntrada INT;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExistenciaAnterior = 0;
SELECT valornuevo FROM bitacora WHERE mensaje=idp AND fecha<=fechai AND tipo=CONCAT('UPDATE',ida)  ORDER BY idbitacora DESC LIMIT 1 INTO ExistenciaAnterior;
SELECT COALESCE(SUM(EP.Cantidad),0) FROM entrada E Inner Join entradaproducto EP ON E.id_entrada = EP.id_entrada Inner Join almacen A ON A.id_almacen = E.id_almacen AND E.id_almacen = A.id_almacen WHERE E.cancelada=0 AND EP.id_producto=idp  AND E.id_almacen=ida  AND E.Fecha_llegada >=fechai AND fecha_llegada<=fechaf INTO Entradas;
SELECT COALESCE(SUM(SP.cantidad),0) FROM Salida S Inner Join SalidaProducto SP ON S.id_salida=SP.id_salida Inner Join almacen A ON A.id_almacen = S.id_almacen WHERE S.status=1 AND SP.id_producto=idp  AND S.id_almacen=ida  AND S.Fecha_salida>=fechai AND fecha_salida<=fechaf INTO Salidas;
SELECT COALESCE(SUM(TP.cantidad),0) FROM traspaso AS T Inner Join almacen AS AL1 ON T.id_almacen_origen = AL1.id_almacen Inner Join almacen AS AL2 ON T.id_almacen_destino = AL2.id_almacen Inner Join traspasoproducto AS TP ON T.id_traspaso = TP.id_traspaso WHERE  TP.id_producto=idp  AND T.id_almacen_destino=ida  AND T.fecha_traspaso >= fechai AND fecha_traspaso<=fechaf INTO TraspasosEntradas;
SELECT COALESCE(SUM(TP.cantidad),0) FROM traspaso AS T Inner Join almacen AS AL1 ON T.id_almacen_origen = AL1.id_almacen Inner Join almacen AS AL2 ON T.id_almacen_destino = AL2.id_almacen Inner Join traspasoproducto AS TP ON T.id_traspaso = TP.id_traspaso WHERE  TP.id_producto=idp  AND T.id_almacen_origen=ida  AND T.fecha_traspaso >= fechai AND fecha_traspaso<=fechaf INTO TraspasosSalida;
SELECT COALESCE(SUM(VP.cantidad),0) FROM VentaProducto VP INNER JOIN Venta V ON VP.id_venta=V.id_venta INNER JOIN Almacen A ON A.id_almacen=VP.id_almacen WHERE V.cancelado=0 AND VP.id_producto=idp  AND VP.id_almacen=ida AND V.fecha_inicio>=fechai AND V.fecha_inicio<=fechaf INTO Creditos;
SELECT COALESCE(SUM(VP.cantidad),0) FROM VentaProductoContado VP INNER JOIN Venta_Contado V ON VP.id_venta=V.id_venta INNER JOIN Almacen A ON A.id_almacen=VP.id_almacen WHERE V.cancelado=0 AND VP.id_producto=idp  AND VP.id_almacen=ida AND V.fecha_inicio>=fechai AND V.fecha_inicio<=fechaf INTO Contados;
SELECT COALESCE(SUM(VP.cantidad),0) FROM VentaProductoOrdenCompra VP INNER JOIN Venta_OrdenCompra V ON VP.id_venta=V.id_venta INNER JOIN Empleado E ON E.id_empleado=V.id_empleado INNER JOIN Almacen A ON A.id_almacen=VP.id_almacen INNER JOIN Producto P ON VP.id_producto=P.id_producto INNER JOIN Cliente C ON V.id_cliente=C.id_cliente   WHERE V.aprovada=1 AND V.cancelado=0 AND  VP.id_producto=idp  AND VP.id_almacen=ida  AND V.Fecha>=fechai AND V.Fecha<=fechaf INTO OrdenCompra;
SELECT COALESCE(SUM(AIP.CANTIDAD),0) FROM Ajuste_Inventario AI INNER JOIN Almacen A ON AI.id_almacen=A.id_almacen INNER JOIN Ajuste_inventario_productos AIP ON AIP.id_ajuste=AI.id_ajuste WHERE AIP.tipo_ajuste='Salida' AND AIP.id_producto=idp AND A.id_almacen=ida  AND AI.fecha_captura>=fechai AND AI.fecha_captura<=fechaf INTO AjustesInventarioSalida;
SELECT COALESCE(SUM(AIP.CANTIDAD),0) FROM Ajuste_Inventario AI INNER JOIN Almacen A ON AI.id_almacen=A.id_almacen INNER JOIN Ajuste_inventario_productos AIP ON AIP.id_ajuste=AI.id_ajuste WHERE AIP.tipo_ajuste='Entrada' AND AIP.id_producto=idp AND A.id_almacen=ida  AND AI.fecha_captura>=fechai AND AI.fecha_captura<=fechaf INTO AjustesInventarioEntrada;

RETURN (ExistenciaAnterior+Entradas+TraspasosEntradas+AjustesInventarioEntrada)-(Salidas+TraspasosSalida+Creditos+Contados+OrdenCompra+AjustesInventarioSalida);

END $$

DELIMITER ;