Transacciones y procedmienos almacenados

[Pinty]

Hola a todos.

Estoy creando una base de datos para una aplicación web con MySql y tengo unas dudas con respecto a crear procedimientos almacenados o trasacciones.

Mi principal duda es cuando crear procedimientos almacenados y cuando transacciones. Si quiero ingresar un cliente nuevo en la tabla de clientes, que es mejor un procedimiento almacenado con un instert?? o una transaccion??. Nose muy bien cuando usar uno u otro, y por otro lado, una consulta select se define tambien como procedimiento almacenado??

Muchas gracias por adelantado a todos por vuestra ayuda.

Un saludo

Pinty

[gnzsoloyo]

Procedimientos almacenados y transacciones no son cosas excluyentes.
Los SP son para hacer tareas complejas de varias etapas, las cuales deben ejecutarse como un todo (atomicidad). Pero también pueden usarse para sentencias únicas simplemente para aumentar la seguridad ante sql-injection.
Ahora bien, un SP puede a su vez contener una transacción o estar contenido en una transacción.
Son cosas técnicamente diferentes.
Donde sí se requiere una transacción es cuando debes hacer uno o mas pasos de ABM (INSERT/UPDATE/DELETE), que deben completarse todos, o eliminarse todos.
No hay una forma más descriptiva que eso.

Posiblemente el mejor ejemplo sea un retiro de un cajero automático: Se actualizan muchas tablas (caja, cuenta, historial, cuentas de sucursal, movimientos internos del banco... son muchas), pero hasta que no terminó la última (dinero retirado del expendedor de billetes), la transacción no se considera terminada, y por tanto el rollback tiene que eliminar todas las etapas.

[Pinty]

Hola gnzsoloyo. En primer lugar muchas gracias por contestar.

Segun me comentas, las transacciones estan contenidas en un procedimiento almacenado y sirven para asegurarse que se cumplen todas las consultas que se hacen en su interior, por ejemplo 5 insert o se hacen todos o no se hace ninguno (espero no haberte entendido mal). Por lo que si por ejemplo solo quiero hacer un instert de un cliente nuevo, no necesito crear una transaccion, lo que deberia hacer es un insert y que me devuelva por ejemplo su idCliente para saber que la inserción se completó. Mi principal preocupación es como saber si el insert (por seguir con este ejemplo) se realizo correctamente o si los datos se perdieron por el camino.

Muchas gracias nuevamente

Un saludo

Pinty

[gnzsoloyo]

Recalco, que una transacción no está necesariamente contenida en un SP, sino que puede estar contenida, o bien durante una transacción puede llamarse a uno o múltiples SP diferentes.
Lo que MySQL no admite es crear transacciones anidadas.

Un SP es una rutina de ejecuciones en SQL (T-SQL o PL/SQL). Una transacción es un conjunto de procesos que se ejecutan en forma atómica, consistente, aislada y con resultados perdurables (ver ACID).

Pero las dos cosas son independientes del éxito o fracaso de las acciones invocadas, porque eso es una condición que el programador de SQL debe considerar al implementarla.

El éxito o fracaso de las operaciones ya tienen definidos sus resultados en los DBMS, y eso lo puedes encontrar tanto en los manuales de cada DBMS (el de MySQL, por caso), como en los del lenguaje de programación, si lo controlas desde la aplicación.
MySQL tiene varias formas de manejar esos detalles, y un post es algo largo para explicarlos, pero te resumo algunos casos:

- Los INSERT, UPDATE y DELETE generan un valor positivo (recuperable por el lenguaje usado), que representa la cantidad de registros afectados (generados, borrados o modificados).

- Los INSERT sobre tablas que usan auto incrementales afectan una variable de sesión accesible con la función de MySQL LAST_INSERT_ID(), que devuelve el valor generado (sólo si se ejecuta inmediatamente). Esta también puede ser "vista" por los lenguajes de programación.

- Los SELECT generan datos que se devuelven, pero también información en ciertas funciones (NUM_ROWS()), que pueden ser leídos.

- Los fallos de ejecución en un SP pueden generar excepciones capturables.

- Las lecturas de cursores de un SP pueden ser capturados por HANDLERs.

- Toda sentencia de MySQL fallida que genere un error, devuelve un código, un número y un mensaje de error.

Todo esos recursos se usan en programación de los SP, o bien en los lenguajes de programación, para el control y gestión de excepciones.

¿Se va entendiendo?

[Pinty]

Una explicación muy completa, la verdad que me ha quedado bastante claro lo que es una transaccion frente a un procedimiento almacenado. Tambien me has ayudado con el hecho de que la consulta update delete e insert devuelven el número de filas afectadas (algo que ya no recordaba), pero tienes razon, es una pregunta muy extensa como para explicarla por aqui. Tengo muchas dudas y me gustaria montar la base de datos lo mejor posible. Lo mejor es que me busque un manual e ir paso a paso entendiendo bien las cosas. Supongo que en la propia pagina de MySql encontrare mucha información, no obstante si conoces algun manual que consideres util te agradeceria que me lo comentases.

Una vez mas, muchas gracias por tu ayuda.

Un saludo

Pinty

[catpaw]

Hola disculpen me surgio una duda, muy interesante el tema:

Los INSERT, UPDATE y DELETE generan un valor positivo (recuperable por el lenguaje usado), que representa la cantidad de registros afectados (generados, borrados o modificados).

Yo por ejemplo para verificar el exito de las sentencias ocupo mysql_affected_rows>0 o !mysql_error() lo cual quiere decir que fue exitosa la sentencia, ahora estas comprobaciones sirven para

- LOAD DATA LOCAL INFILE y
- SELECT * INTO OUTFILE

???

Los fallos de ejecución en un SP pueden generar excepciones capturables.

y para este tipo de fallos en sentencia como podria generar excepciones que me permitieran hacer algo como un "pool de transacciones" es decir, si hay una transaccion en curso y llega otra y otra despues de la otra, como encolarlas o esto ya va implicito en el uso??

Gracias y disculpen la intromisión.

[gnzsoloyo]

mysql_affected_rows>0 o !mysql_error()

Eso es programación, no MySQL. Es tema OFF-TOPIC.

hay una transaccion en curso y llega otra y otra despues de la otra, como encolarlas o esto ya va implicito en el uso??

Va implícito. Lo administra el servidor de MySQL.
A nivel de aplicación tienes que manejar la concurrencia y los timeout, pero eso es tema de otro foro.