Como plantear modelo asignacion elementos sin perder consistencia

[aquelQueTeObserva]

Buenas tardes, ante todo mis disculpas por molestarlos con un problema que quizas
para ustedes sea trivial, espero poder plantearlo de forma clara al menos.

Para simplificarlo y dar una base digamos que tenemos que representar operarios
ingresando y extrayendo material de un deposito para ello defini 3 tablas
como siguen:
(nota: motor MySQL ver 5.1.35-community)

CREATE TABLE IF NOT EXISTS `materiales` (
`idMaterial` int(10) unsigned NOT NULL AUTO_INCREMENT,
`denominacion` varchar(50) NOT NULL,
PRIMARY KEY (`idMaterial`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `operarios` (
`idOperario` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nombre` varchar(50) NOT NULL,
PRIMARY KEY (`idOperario`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `movimientosaterial` (
`idMovimientoMaterial` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idMaterial` int(10) unsigned NOT NULL,
`idOperario` int(10) unsigned NOT NULL,
`cantidad` double NOT NULL,
PRIMARY KEY (`idMovimientoMaterial`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Con los siguientes datos iniciales para prueba:

INSERT INTO `materiales` (`idMaterial`, `denominacion`) VALUES
(1, 'hierro');

INSERT INTO `operarios` (`idOperario`, `nombre`) VALUES
(1, 'Cecilia'),
(2, 'Facundo');

INSERT INTO `movimientosaterial` (`idMovimientoMaterial`, `idMaterial`, `idOperario`, `cantidad`) VALUES
(1, 1, 1, 1000);

Donde el registro de la tabla movimientosaterial representa el estado de un material en el deposito via
una consulta como:

-- consultamos el stock de hierro <=> idMaterial = 1
SELECT SUM(cantidad) AS sumatoria FROM movimientosaterial WHERE idMaterial = 1 GROUP BY idMaterial;
-- result: sumatoria = 1000

y un movimiento sera un INSERT de cantidad positiva para indicar un ingreso o negativa para indicar una extraccion
sobre la tabla movimientosaterial.

Ahora bien, el procedimiento nomral de extraccion consistiria en los siguientes pasos

1)consultar si hay stock del material requerido
2)si el stock alcanza para satisfacer el requerimiento proceder a extraerlo

Sentado el marco de trabajo llegamos finalmente al problema, como hacer para mantener la consistencia
cuando hay concurrencia, o sea, como evitar que se le asigne a 2 operarios material por encima del disponible?

Evidentemente hay cosas que no termino de entender pues inicialmente se me ocurrio que si lo hacia usando
transacciones no habria problemas, pero descubri (usando 2 consolas en paralelo) que puedo tener una lectura
incorrecta en la segunda si esta consulta la sumatoria antes de que le de COMMIT a la primera.

Se me ocurrio entonces que quizas lo correcto seria bloquear la tabla movimientosaterial mientras ejecuto
1) y 2) impidiendo que culquier otro proceso pueda operar con ella en el intervalo en que se ejecuta
pero como nunca utilize este procedimiento tengo mis reservas sobre como se comportara en definitiva
hacer una cosa asi (debo bloquear lectura, o sea que se bloqueara cualquier proceso de lectura como los de
analisis o cualquier otra cosa que toque la tabla inluidos INNER JOINs no?)

Hay una forma mas correcta de encarar el problema?
Si la solucion es por bloqueo de tablas, asi sea de forma muy simplificada, como se haria de forma correcta
y que consecuencias tendria?

Desde ya muchas gracias por su tiempo

[huesos52]

Se me ocurrio entonces que quizas lo correcto seria bloquear la tabla movimientosaterial mientras ejecuto
1) y 2) impidiendo que culquier otro proceso pueda operar con ella en el intervalo en que se ejecuta

Yo lo miraría bajo este mismo enfoque.

pero como nunca utilize este procedimiento tengo mis reservas sobre como se comportara en definitiva
hacer una cosa asi (debo bloquear lectura, o sea que se bloqueara cualquier proceso de lectura como los de
analisis o cualquier otra cosa que toque la tabla inluidos INNER JOINs no?)

pruebalo y nos cuentas. Al parecer tienes datos para realizar estas pruebas.

Son pocos los usuarios que utilizan mysql hasta este nivel, así que no es una pregunta tan trivial como crees.

Mira muy bien la documentación acerca de lock table.

http://dev.mysql.com/doc/refman/5.0/es/lock-tables.html

Nos cuentas como te va,

Un saludo.

[aquelQueTeObserva]

Ante todo muchas gracias por tu tiempo; me da un poco de seguridad enterarme que el problema en verdad no era tan sencillo de abordar (a veces tiendo a liarme un poco) y que alguien con experiencia en la materia avale la logica que propuse.

Aclaro de paso un poco por que lo plantee de esta forma (lo estoy consultando en varios foros a la vez y en algunos obtube una interpretacion curiosa de lo que plantee; asi que me explayo un poco asi entienden algunas cosas que obvie por me parecio que me estaba extendiendo demasiado); el planteo de llevar el stock de un material como susecion movimientos ingreso/egreso es por que esto tambien se precisa para hacer un seguimiento del material (desde que lote llego, a que lote se transfiere, etc), obvie las tablas y campos para poder llevar esta informacion solo para simplificar, pero (y disculpen si repito la idea), el stock de un material dado lo debo llevar etonces como una especie de libro diario (entrada/salida) cada entrada enlazada a lotes para trackear.

Ahora bien, si justamente de la lectura de como funciona el LOCK con tablas inodb (link que me pasastes, no me permitio escribirlo de nuevo), no me termina de convencer si este es el mejor metodo, voy a tener que hacer algunas pruebas para estar seguro.

Estoy en una etapa preliminar y me puedo dar el lujo de estudiar un poco las alternativas por suerte (por lo que no tengo datos reales ahun para probar ya que me toca a mi proponer el modelo para hacer lo que planean)

Estoy evaluando tambien utilizar un campo semaforo (o de lock en el registro de material (eventualmente el de lote asi es ahun mas restrictivo en el modelo completo)), pero antes de encarar por ese lado queria estar seguro de que no hubiese una tecnica o concepto superador;

Cuando concluya de decidir que es lo mas adecuado, vuelvo a postear (gracias por la invitacion a hacerlo), asi queda para referencia y discusión; nuevamente muchas gracias por tu respuesta.