UNIDAD 3 Transacciones Ing

(1)

UNIDAD III

TRANSACCIONES EN LAS BASES DE

DATOS

UNIVERSID

UNIVERSIDAD

AD TECNOLOGICA DE

TECNOLOGICA DE NEZAHUALCOYO

NEZAHUALCOYOTL

TL

División de Informática y Computación

Ingeniería en Tecnologías de información

Academia de Bases de Datos

DESARROLLADO POR: Lic.

DESARROLLADO POR: Lic. PABLO JULIO GUTIÉRREZ ALARCÓN

PABLO JULIO GUTIÉRREZ ALARCÓN

Contenido



Procesamiento de Transacciones.



Conceptos de transacciones.



Propiedades de las Transacciones.



Planes y

Planes y recuperabilidad.

recuperabilidad.



Seriabilidad de los Planes.



(2)

UTNeza –Acad. BD Base de Datos para Aplicaciones - PJGA 3

Procesamiento de Transacciones.

La Administración de las Transacciones tiene como objetivo

llevar el control para la

recuperación

y la

concurrencia

que

están relacionadas con la cuestión general de la

protección de

los datos ;

es decir, la protección contra la pérdida o daño de la

información que está en la base de datos. En particular tienen

que ver con problemas como:



El sistema puede abortar cuando están ejecutando algún progra ma,

con lo que dejaría a la BD en un estado desconocido.



Dos programas que están ejecutándose al mismo tiempo (“en forma

concurrente”) pueden interferir entre sí y en c onsecuencia, producir

resultados incorrectos, ya sea dentro de la BD o fuera de ella.

Conceptos de transacciones



Una transacción en una

unidad de trabajo lógica.

Considerándose

como unidad lógica : Insert, delete, update a una tabla de la base de

datos.

C.J. Date “Inrtoducción a los sistemas de BD”



Una transacción es una unidad de la ejecución de un programa que

accede y posiblemente actualiza varios elementos de datos . Una

transacción se inicia por la ejecución de un programa de usuario,

escrito en lenguaje de manipulación de datos de al to nivel o en un

lenguaje de programación.

korth-Silberschatz-Sudarshan “Fundamentos de BD”

(3)

Propiedades de las transacciones

Database

Redo

log files

Control

files

Data

files

User

process

Server

process

PGA

Archived

log files

Parameter

file

Password

file

Instance

SGA

Redo log

buffer

Data buffer

cache

Shared pool

Data dict.

cache

Library

cache

PMON

DBW0

SMON

CKPT

LGWR

Others

Overview

(4)

Estados de una transacción

1

2

3

4

5

(5)

Recuperación de transacciones

Database Transactions

A database transaction consists of one of the

following:



DML statements which constitute one consistent change to

the data



One DDL statement

(6)

Transacciones en SQL

Database Transactions



Begin when the first DML SQL statement is executed



End with one of the following events:



A

COMMIT

or

ROLLBACK

statement is issued



A DDL or DCL statement executes (automatic commit)



The user exits

i

SQL*Plus

(7)

With

COMMIT

and

ROLLBACK

statements, you can:



Ensure data consistency



Preview data changes before making changes permanent



Group logically related operations

Advantages of

COMMIT

and

ROLLBACK

Statements

SAVEPOINT B

SAVEPOINT A

DELETE

INSERT

UPDATE

INSERT

COMMIT

Time

Transaction

ROLLBACK

to SAVEPOINT B

ROLLBACK

to SAVEPOINT A

ROLLBACK

Controlling Transactions

(8)

SGA

Redo log

buffer

Data buffer

cache

Shared pool

LGWR

Instance

Database

Redo

log files

Control

files

Data

files

1

2

3

4 User

process

Server

process

COMMIT

Processing

UPDATE...

SAVEPOINT update_done;



Create a marker in a current transaction by using the

SAVEPOINT

statement.



Roll back to that marker by using the

ROLLBACK TO

SAVEPOINT

statement.

Rolling Back Changes

to a Marker

(9)

DML statement

Old image

New

image

Rollback segment

Table

Rollback Segment



An automatic commit occurs under the following

circumstances:



DDL statement is issued



DCL statement is issued



Normal exit from

i

SQL*Plus, without explicitly issuing

COMMIT

or

ROLLBACK

statements



An automatic rollback occurs under an abnormal termination

of

i

_{SQL*Plus or a system failure.}

(10)



The previous state of the data can be recovered.



The current user can review the results of the DML operations by

using the

SELECT

statement.



Other users

cannot

view the results of the DML statements by the

current user.



The affected rows are

locked

; other users cannot change the data

within the affected rows.

State of the Data

Before

COMMIT

or

ROLLBACK



Data changes are made permanent in the database.



The previous state of the data is permanently lost.



All users can view the results.



Locks on the affected rows are released; those rows are

available for other users to manipulate.



All savepoints are erased.

(11)

COMMIT;

Commit complete.



Make the changes.



Commit the changes.

DELETE FROM employees

WHERE employee_id = 99999;

1 row deleted.

INSERT INTO departments

VALUES (290, 'Corporate Tax', NULL, 1700);

1 row inserted.

Committing Data

Discard all pending changes by using the

ROLLBACK

statement:



Data changes are undone.



Previous state of the data is restored.



Locks on the affected rows are released.

DELETE FROM copy_emp;

22 rows deleted.

ROLLBACK;

Rollback complete.

(12)



If a single DML statement fails during execution, only that

statement is rolled back.



The Oracle server implements an implicit savepoint.



All other changes are retained.



The user should terminate transactions explicitly by

executing a

COMMIT

or

ROLLBACK

statement.

Statement-Level Rollback



Read consistency guarantees a consistent view of the data

at all times.



Changes made by one user do not conflict with changes

made by another user.



Read consistency ensures that on the same data:



Readers do not wait for writers.

Writers do not wait for readers.

(13)

SELECT *

FROM userA.employees;

UPDATE employees

SET

salary = 7000

WHERE last_name = 'Goyal';

Data

blocks

Rollback

segments

changed

and

unchanged

data

before

change

“old” data

User A

User B

Read

consistent

image

Implementation of Read Consistency

Output

Time

Session 1

Session 2

t1

t2

t3

t4

t5

SELECT salary FROM employees

WHERE last_name='King';

24000

UPDATE employees

SET

salary=salary+10000

WHERE last_name='King';

24000

COMMIT;

34000

SELECT salary FROM employees

WHERE last_name='King';

SELECT salary FROM employees

WHERE last_name='King';

(14)

In an Oracle database, locks:



Prevent destructive interaction between concurrent

transactions



Require no user action



Automatically use the lowest level of restrictiveness



Are held for the duration of the transaction



Are of two types: explicit locking and implicit locking

Locking



Two lock modes:



Exclusive: Locks out other users



Share: Allows other users to access



High level of data concurrency:



DML: Table share, row exclusive



Queries: No locks required

(15)

Bibliografia

 Date C. J.

 SISTEMAS DE BASES DE DATOS (VOLUMEN I).  Quinta Edición.

 Addison Wesley.  Shkuntala Atre.

 TÉCNICAS DE BASES DE DATOS. ESTRUCTURACIÓN EN DISEÑO Y ADMINISTRACIÓN.  Trillas, 1991.

 Nelson T. Dinerstein.

 SISTEMAS DE MANEJOS DE ARCHIVOS Y BASES DE DATOS PARA MICROCOMPUTADORAS.  CECSA, 1990.

 Rose Deakin.

 BASES DE DATOS. USO Y ADMINISTRACIÓN EN CENTROS DE COMPUTO.  Trillas, 1988.

 Korth., Silberschatz, Sudarshan  FUNDAMENTOS DE BASES DE DATOS.01  McGrawHill, 4ta. Edición, Mexico, 2002  Adad Ruben, Medina Miguel A. y Careaga Alfredo.

 FUNDAMENTOS DE LAS ESTRUCTURAS DE DATOS RELACIONALES.  Megabyte. Noriega Editores, 1992.

 Cesar Perez Lopez

 ORACLE 9I, ADMINISTRACIÓN Y ANÁLISIS DE BASE DE DATOS  Ed. Alfaomega – RaMa, México, 2003

 Cesar Perez Lopez

 ORACLE 9I / SERVIDOR DE APLICACIONES RED Y PROGRAMACION  Ed. Alfaomega – RaMa, México, 2003



 Michael Abbey, Mike Corey, Ian Abramson  ORACLE 9i , GUIA DE APRENDISAJE  Mc Graw Hill-Osborne, Oracle Press, Mexico, 2002  Javier Lopez H.

 ORACLE, FUNDAMENTOS PARA EL DESARROLLO DE APLICACIONES WEB  Colección USERS-libros.tectimes.com

 MP Ediciones Bs. As.-Argentina  Paul Dorsey, Joseph R. Hudicka

 ORACLE 8, DISEÑO DE BASES DE DATOS CON UML