Download Administración Base de Datos SQL
Document related concepts
Transcript
Administración Base de Datos SQL Profesor René Sandoval Ingeniero (E) Informática rene2160@gmail.com Introducción Definición de Transacciones Una transacción es una serie de operaciones que se realizan como una unidad lógica de trabajo. Las transacciones permiten asegurar un cierto nivel de integridad y de recuperabilidad de los datos. El registro de transacciones , que debe tener toda base de datos, guarda un registro de todas las transacciones que realizan cualquier tipo de modificación (inserción, eliminación o actualización) en la base de datos. Los motores de base de datos utilizan este registro para recuperar datos en caso de que se produzcan errores o fallos en el sistema. La integridad de una transacción depende en parte del programador de SQL. El programador debe saber el momento en que debe iniciar y finalizar la transacción y el orden en que debe llevar a cabo las modificaciones de los datos para asegurar la consistencia lógica y del significado de los datos 2º Semestre 2010 Adm. Base de Datos SQL 2 Introducción Propiedades de las Transacciones (ACID) Una transacción debe cumplir 4 requisitos para poder considerarla válida. Estos requisitos se conocen como propiedades ACID (Atomicidad, Consistencia, Aislamiento (isolation) y Durabilidad. Atomicidad: Asegurar que todas las modificaciones de datos de una transacción se completen como un grupo si la transacción tiene éxito, o que no se lleve a cabo ninguna de ellas si no lo tiene. Consistencia: Significa que todos los datos permanecen en un estado consistente -que la integridad de los datos queda preservada- una vez finalizada una transacción, independiente si la transacción fue un éxito o no. Aislamiento: Significa que los efectos de cada transacción son iguales que si esa transacción fuera la única del sistema; en otras palabras; las modificaciones realizadas por una transacción están aisladas de las realizadas por cualquier otra transacción concurrente. Durabilidad: Significa que, una vez confirmada una transacción, sus efectos son permanentes en la base de datos, incluso en caso de fallo. 2º Semestre 2010 Adm. Base de Datos SQL 3 Introducción Modo de Transacciones Las transacciones pueden comenzar en tres modos diferentes: confirmación automática, explícita o implícita. NOTA: el modo predeterminado de SQL SERVER es confirmación automática. Modo de Confirmación Automática Cada instrucción T-SQL se confirma cuando concluye; con este modo no hacen falta instrucciones adicionales para controlar las transacciones. Este modo resulta útil cuando en SQL SERVER manejamos las bases de datos desde un analizador de consultas. 2º Semestre 2010 Adm. Base de Datos SQL 4 Introducción Modo de Transacciones Modo de Transacciones Explicitas Este modo de transacciones es el utilizado con más frecuencia al programar aplicaciones y en los procedimientos almacenados, desencadenadores y las secuencias de comandos. Cuando se ejecute un grupo de instrucciones para llevar a cabo una tarea puede que haga falta determinar los puntos en que debería iniciarse y concluir la transacción de modo que todo el grupo de instrucciones tenga éxito o se desahaga. Una transacción explicita se especifica utilizando instrucciones T-SQL. 2º Semestre 2010 Adm. Base de Datos SQL 5 Introducción Modo de Transacciones Modo de Transacciones Explicitas – Instrucciones T-SQL Inicio de una Transacción: para dar inicio a una transacción se utiliza el comando BEGIN TRAN. Nomenclatura: BEGIN TRAN nombre_transacción Confirmación de una transacción: para confirmar el conjunto de instrucciones definidas en una transacción se utiliza el comando COMMIT TRAN. Nomenclatura: COMMIT TRAN nombre_transacción. Deshacer una transacción: para deshacer todas las instrucciones que se encuentran en una transacción se utiliza el comando ROLLBACK. Nomenclatura: ROLLBACK TRAN nombre_transacción 2º Semestre 2010 Adm. Base de Datos SQL 6 Introducción Modo de Transacciones Modo de Transacciones Explicitas – EJEMPLO Ejemplo 1: Cree una tabla denominada Prueba que se base en las siguientes tuplas: Prueba (cod:numero; descripcion:tira(10)). Basándose en esta tabla cree una transacción para insertar dos campos en ella y confirme su ingreso. Para esto utilice los comandos T-SQL de transacciones. --Para Crear la Tabla CREATE TABLE Prueba( cod numeric, descripcion nvarchar(10) ) --TRANSACCIONPARA PARAINSERTAR INSERTARDOS DOSFILAS FILAS --TRANSACCION BEGINTRAN TRANinsertar insertar BEGIN insertinto intoPrueba Pruebavalues values(1,'Prueba (1,'Prueba1') 1') insert insertinto intoPrueba Pruebavalues values(2,'Prueba (2,'Prueba2') 2') insert select**from fromPrueba Prueba select COMMITTRAN TRANinsertar insertar COMMIT 2º Semestre 2010 Adm. Base de Datos SQL 7 Introducción Modo de Transacciones Modo de Transacciones Explicitas – EJEMPLO Ejemplo 2: Utilizando la misma tabla definida anteriormente, realice una transacción que permita ingresar dos nuevos registros y dentro de la transacción liste los valores de la tabla. Después de listar no almacene los datos y deshaga la transacción. Liste por ultimo nuevamente la tabla después de haber cancelado la transacción. --TRANSACCIONPARA PARAINSERTAR INSERTARDOS DOSFILAS FILAS --TRANSACCION BEGINTRAN TRANinsertar_1 insertar_1 BEGIN insertinto intoPrueba Pruebavalues values(3,'Prueba (3,'Prueba3') 3') insert insertinto intoPrueba Pruebavalues values(4,'Prueba (4,'Prueba4') 4') insert select**from fromPrueba Prueba select ROLLBACKtran traninsertar_1 insertar_1 ROLLBACK select**from fromPrueba Prueba select 2º Semestre 2010 Adm. Base de Datos SQL 8 Introducción Modo de Transacciones Modo de Transacciones Explicitas – Transacciones Anidadas SQL Server permite transacciones anidadas, o transacciones situadas dentro de otras transacciones. En las transacciones anidadas hay que confirmar explícitamente cada transacción interior para que SQL SERVER sepa que ha concluido una transacción interior y pueda liberar los recursos empleados por esa transacción una vez confirmada la transacción exterior. Si la transacción exterior no logra confirmar, ninguna de las transacciones interiores se confirma, y se deshacen tanto las transacciones interiores como la exterior. En cambio, si se confirma la transacción exterior se confirman todas las interiores. 2º Semestre 2010 Adm. Base de Datos SQL 9 Introducción Modo de Transacciones Modo de Transacciones Explicitas – Ejemplo Transacciones Anidadas Ejemplo 3: Utilizando la tabla creada anteriormente. Realice una transacción anidada en las cuales las instrucciones de la exterior sean ingresar dos filas y listar, y en la interior que se modifique la descripción de la fila con cod = 1 y que se elimine la fila con cod = 2. BEGIN TRAN exterior BEGIN TRAN interior Update Prueba SET descripcion = 'N Prueba 1' where cod = 1 Delete from Prueba where cod = 2 select * from prueba COMMIT TRAN interior insert into Prueba values (3,'Prueba 3') insert into Prueba values (4,'Prueba 4') Select * from Prueba COMMIT TRAN exterior select * from prueba 2º Semestre 2010 Adm. Base de Datos SQL 10 Introducción Modo de Transacciones Modo de Transacciones Explicitas – Ejemplo Transacciones Anidadas Ejemplo 4: Utilizando el mismo ejemplo anterior. Cancele la Transacción exterior y evalué el resultado logrado. BEGIN TRAN exterior BEGIN TRAN interior Update Prueba SET descripcion = 'N Prueba 1' where cod = 1 Delete from Prueba where cod = 2 select * from PRUEBA COMMIT TRAN interior insert into Prueba values (3,'Prueba 3') insert into Prueba values (4,'Prueba 4') Select * from Prueba ROLLBACK TRAN exterior select * from prueba 2º Semestre 2010 Adm. Base de Datos SQL 11 Introducción Modo de Transacciones Modo de Transacciones Implícitas En el modo de transacciones implícitas las transacciones comienzan automáticamente siempre que se emplean determinadas instrucciones de T-SQL y continúan hasta que se concluyen de manera explícita con una instrucción COMMIT o ROLLBACK . Si no se especifica una instrucción de conclusión todo lo realizado se deshace cuando el usuario se desconecta. Las siguientes instrucciones comienzan transacciones nuevas en el modo de transacciones implícitas: ALTER TABLE, CREATE , DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, UPDATE. 2º Semestre 2010 Adm. Base de Datos SQL 12 Introducción Modo de Transacciones Modo de Transacciones Implícitas Para poder habilitar el modo de transacciones implícitas en SQL SERVER, se puede emplear el siguiente comando: SET IMPLICIT_TRANSACTIONS {ON | OFF} ON: Habilita las transacciones implícitas. OFF: Deshabilitan las transacciones implícitas. Ejemplo 5: Utilizando la tabla anterior. Habilite la opción para transacciones implícitas, e intente realizar un SELECT sin utilizar los comandos de transacciones. 2º Semestre 2010 Adm. Base de Datos SQL 13 Introducción Modo de Transacciones – Puntos de Almacenamiento Se puede evitar tener que deshacer todas las transacciones empleando un punto de almacenamiento para deshacer hasta cierto punto la transacción, en lugar de llegar hasta el comienzo de la transacción. Todas las modificaciones producidas hasta el punto de almacenamiento siguen siendo válidas y no se deshacen, pero las instrucciones ejecutadas después del punto de almacenamiento y hasta la instrucción ROLLBACK seguirán ejecutándose. Para especificar un punto de almacenamiento en una transacción se utiliza la siguiente instrucción: SAVE TRAN nombre_punto_transacción 2º Semestre 2010 Adm. Base de Datos SQL 14 Introducción Modo de Transacciones – Puntos de Almacenamiento Para deshacer hasta el punto de almacenamiento hay que utilizar ROLLBACK TRAN, pero utilizando el nombre del punto de almacenamiento. ROLLBACK TRAN nombre_punto_almacenamiento Se pueden tener mas instrucciones de T-SQL después de la instrucción ROLLBACK para continuar la transacción. Hay que recordar incluir una instrucción COMMIT o ROLLBACK después del ultimo ROLLBACK para validar o deshacer la instrucción. 2º Semestre 2010 Adm. Base de Datos SQL 15 Introducción Modo de Transacciones – Puntos de Almacenamiento Ejemplo 6: Utilizando la tabla definida en ejemplos anteriores (PRUEBA). Revise el siguiente ejemplo de utilización de Punto de Almacenamiento. BEGINTRAN TRANinterno interno BEGIN insertinto intoPrueba Pruebavalues values(10,'10') (10,'10') insert insertinto intoPrueba Pruebavalues values(11,'11') (11,'11') insert SAVETRAN TRANsave_interno save_interno--Guardas --GuardasTransacciones Transaccioneshasta hastaaquí aquí SAVE insertinto intoPrueba Pruebavalues values(12,'12') (12,'12') insert ROLLBACKTRAN TRANsave_interno save_interno ROLLBACK insertinto intoPrueba Pruebavalues values(13,'13') (13,'13') insert COMMITTRAN TRANinterno interno COMMIT select* *from fromprueba prueba select 2º Semestre 2010 Adm. Base de Datos SQL 16