Download Administración Base de Datos SQL

Administración
Base de Datos
SQL
Profesor René Sandoval
Ingeniero (E) Informática
[email protected]
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