Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 1/17 UNIDAD 8 GESTIÓN DE DATOS Y SIMULTANEIDAD 8.1. OBJETIVOS Gestionar datos mediante el uso de SQL. Identificar y administrar objetos PL/SQL. Describir los disparadores y los eventos de disparadores. Controlar y resolver conflictos de bloqueo. 8.2. MANIPULACIÓN DE DATOS MEDIANTE SQL El lenguaje SQL consta de varios comandos o sentencias que permiten la introducción, modificación y eliminación de datos en una base de datos. Estas sentencias se agrupan bajo el nombre de sentencias de manipulación de datos o DML (por su siglas en inglés: Data Manipulation Lenguaje). Las sentencias DML más utilizadas son: INSERT, UPDATE, DELETE y MERGE. En la Figura 8.1 se muestran ejemplos de estas sentencias. Figura 8.1: Ejemplos de sentencias DML 8.2.1. Sentencia INSERT La sentencia básica INSERT crea filas de una en una. Mediante lo que se denomina subselección, puede hacer que el comando INSERT copie filas de una tabla a otra. Este método también se denomina sentencia INSERT SELECT. El ejemplo de la diapositiva es el siguiente comando INSERT (Figura 8.2): insert into dept_80 (select * from employees where department_id = 80); En este caso la tabla dept_80 tiene exactamente la misma estructura que la tabla employees. Si este no es el caso, puede nombrar las columnas de cada tabla. Los valores seleccionados en la sentencia SELECT se asocian a las columnas de la tabla en la que se insertan, respectivamente. Los valores de columna coinciden en el orden especificado en las sentencias INSERT y SELECT. Todo lo que se necesita es que los tipos de dato coincidan. Por ejemplo: insert into just_names (first, last) (select first_name, last_name from employees); UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 2/17 Aquí, la tabla just_names sólo tiene dos columnas que tienen el mismo tipo de dato que las columnas first name y last name de la tabla employees. El uso del método INSERT SELECT es un modo de cargar datos en bloque de una o más tablas en otra tabla. Figura 8.2: Ejemplo de sentencia INSERT mediante Oracle Enterprice Manager 8.2.2. Sentencia UPDATE El comando UPDATE se utiliza para modificar filas existentes de una tabla (Figura 8.3). El número de filas modificadas por el comando UPDATE depende de la condición WHERE. Si se omite la cláusula WHERE, se cambian todas las filas de la tabla. Si ninguna fila cumple la condición WHERE, no se realizarán modificaciones. Figura 8.3: Ejemplo de sentencia UPDATE mediante Oracle Enterprice Manager 8.2.3. Sentencia DELETE El comando DELETE se utiliza para eliminar filas existentes de una tabla (Figura 8.4). El número de filas modificadas por el comando DELETE depende de la condición WHERE. Si se omite la cláusula WHERE, se eliminan todas las filas. Si ninguna fila cumple la condición WHERE, no se eliminará ninguna fila. Se debe tener en cuenta en el ejemplo que cuando no se suprime ninguna fila, no es un error; el mensaje devuelto sólo indica que se han eliminado cero filas de la tabla. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 3/17 Figura 8.4: Ejemplo de sentencia DELETE mediante Oracle Enterprice Manager 8.2.4. Sentencia MERGE Este comando conjuga las tareas de inserción o modificación de datos en una única instrucción. La idea detrás del mismo es que si una fila que quiere introducirse en la tabla no existe la misma se ingresa, en caso de que exista se actualizan los datos correspondientes. Considere este ejemplo: algunos datos de la tabla JOBS tienen el siguiente aspecto: JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY --------- -------------- ---------- ---------- AD_PRES President 20000 40000 FI_ACCOUNT Accountant 4200 9000 ST_CLERK Stock Clerk 2000 5000 IT_PROG Programmer 4000 10000 A continuación se muestra el contenido de la tabla JOBS_ACQUISITION: JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY --------- -------------- ---------- ---------- AD_PRES VP 20000 40000 DBA DB Admin 4200 9000 SA Sys Admin 2000 5000 El comando MERGE (Figura 8.5) inserta en la tabla JOBS cualquier fila con un JOB_ID nuevo y actualiza las filas JOBS existentes con JOB_TITLE, si ya existe JOB_ID. El resultado es que el cargo “President” se cambia por “VP” y se agregan los trabajos nuevos “SA” y “DBA”. Figura 8.5: Ejemplo de sentencia MERGE mediante Oracle Enterprice Manager UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 4/17 8.3. SENTENCIAS COMMIT Y ROLLBACK Por defecto, cada comando DML que se introduce no está confirmado. Varias herramientas (incluida iSQL*Plus) tienen opciones que se pueden definir para confirmar cada comando o un grupo de comandos (Figura 8.6). Antes de emitir COMMIT o ROLLBACK, los cambios tienen un estado pendiente. Sólo el usuario que ha realizado el cambio puede ver los datos cambiados. Otros usuarios pueden seleccionar los mismos datos, pero los verán como estaban antes de realizar cualquier cambio. Ningún otro usuario puede emitir DML en los mismos datos que otro usuario ha cambiado. Por defecto, un usuario que intente realizar un cambio en la misma fila que otro usuario tiene que esperar a que el primer usuario confirme el cambio o realice un rollback. El mecanismo de bloqueo de la base de datos Oracle controla esta operación de forma automática. Puesto que el mecanismo de bloqueo está incorporado en la propia fila, es imposible que la base de datos se quede sin bloqueos. Figura 8.6: Comandos Commit y Rollback 8.4. PL/SQL PL/SQL es un lenguaje de programación de cuarta generación propietario de Oracle, que proporciona extensiones de procedimiento a SQL. PL/SQL proporciona un entorno de programación común para las aplicaciones y las bases de datos Oracle independientemente del sistema operativo o de la plataforma de hardware. Con PL/SQL, puede manipular datos con sentencias SQL y controlar el flujo de programas con estructuras de procedimientos como IF‐THEN, CASE y LOOP. También puede declarar constantes y variables, definir procedimientos y funciones, utilizar recopilaciones y tipos de objetos, e interrumpir errores de tiempo de ejecución. El programa PL/SQL también puede llamar a programas escritos en otros lenguajes como C, C++ y Java. PL/SQL también proporciona protección de los datos. El emisor necesita conocer las estructuras de datos que se están leyendo o manipulando para realizar la llamada. Asimismo, también necesita tener permiso para acceder a esos objetos; si el emisor tiene permiso para ejecutar el programa PL/SQL, es todo lo que necesita. De manera opcional, existe otro modo de permisos para llamar a PL/SQL en el que el emisor tiene que disponer de permiso para ejecutar cada sentencia que se ejecute durante el programa llamado. Puesto que se ejecuta en la base de datos, el código PL/SQL es muy eficaz para las operaciones que utilizan muchos datos y minimiza el tráfico de red en las aplicaciones. Un resumen de sus características se muestran en la Figura 8.7. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 5/17 Figura 8.7: Características de PL/SQL 8.4.1. Administración de objetosPL/SQL El DBA normalmente no es responsable de cargar el código PL/SQL en la base de datos, ni de ayudar a los desarrolladores a solucionar problemas. En general tampoco se espera que escriba aplicaciones mediante el uso de PL/SQL, pero debería estar suficientemente familiarizado con los diferentes objetos PL/SQL para realizar recomendaciones a los desarrolladores de aplicaciones e identificar objetos con problemas Figura 8.8). En Enterprice Manager Database Control, se puede acceder a objetos PL/SQL si hace clic en el separador Administration situado bajo Schema. Al hacer clic en el tipo de objeto, puede visualizar, modificar y crear el tipo de objeto PL/SQL seleccionado. Figura 8.8: Tareas del DBA con respecto al PL/SQL 8.4.2. Objetos PL/SQL Existe una amplia variedad de objetos PL/SQL en la base de datos. A continuación se expone una breve definición de algunos de ellos (Figura 8.9): Paquete: un paquete es una recopilación de procedimientos y funciones que están relacionados lógicamente. Esta parte del paquete también se denomina especificación y describe la interfaz para las aplicaciones; declara los tipos, variables, constantes, excepciones, cursores y subprogramas disponibles para su uso. Cuerpo del paquete: el cuerpo define totalmente los cursores y subprogramas, y de este modo implementa la especificación. El cuerpo contiene los detalles de implementación y las declaraciones privadas, que están ocultas para el emisor. Cuerpo del tipo: es una recopilación de métodos (procedimientos y funciones) asociados a los tipos de dato definidos por el usuario. Procedimiento: un procedimiento es un bloque PL/SQL que realiza una acción concreta. Función: una función es un bloque PL/SQL que devuelve un único valor mediante el comando PL/SQL RETURN. Es un procedimiento que tiene un valor de retorno. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 6/17 Disparador (Trigger): un disparador es un bloque PL/SQL que se ejecuta cuando se produce un evento concreto en la base de datos. Estos eventos se pueden basar en una tabla, por ejemplo, cuando se inserta una fila en la tabla. También pueden ser eventos de base de datos, como cuando un usuario se conecta a la base de datos. Figura 8.9: Objetos PL/SQL 8.4.3. Funciones Las funciones PL/SQL se utilizan normalmente para calcular un valor. Existen muchas funciones incorporadas como SYSDATE, SUM, AVG y TO_DATE. Los desarrolladores también crean sus propias funciones al escribir aplicaciones. El código para una función PL/SQL debe contener una sentencia RETURN. Para crear funciones PL/SQL hay que introducir un nombre, un esquema y un código de origen, como se muestra en la diapositiva. La función compute_tax mostrada en la Figura 8.10 se crea con el siguiente comando SQL: CREATE OR REPLACE FUNCTION compute_tax (salary NUMBER) RETURN NUMBER AS BEGIN IF salary<5000 THEN RETURN salary*.15; ELSE RETURN salary*.33; END IF; END; / Figura 8.10: Ejemplo de una función UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 7/17 8.4.4. Procedimientos Los procedimientos se escriben con la finalidad de realizar una acción concreta. Pueden contener parámetros que permiten la transferencia de valores de entrada o salida para comunicarse. Al igual que para las funciones o los packages, se puede utilizar SQL*Plus o iSQL*Plus para crear procedimientos de base de datos. La Figura 8.11 muestra un ejemplo de procedimiento. En caso de no especificar un nombre para el procedimiento que escribe, este se denomina anónimo. Generalmente un DBA o desarrollador escribe un bloque anónimo como parte de una prueba que está realizando en la base de datos. Si se desea almacenar dicho bloque, deberá proveer un nombre para el mismo para poder ser utilizado nuevamente. Figura 8.11: Creación de procedimientos 8.4.5. Paquetes Los paquetes son recopilaciones de funciones y procedimientos. Existen ventajas de rendimiento y mantenimiento al agrupar funciones y procedimientos en un único paquete. Cada paquete debe estar formado por dos objetos de base de datos compilados por separado: Especificación del paquete: este objeto (en ocasiones denominado cabecera del paquete) tiene un tipo de objeto PACKAGE y sólo contiene la definición de los procedimientos, funciones y variables del paquete. Cuerpo del paquete: este objeto tiene un tipo de objeto PACKAGE BODY y contiene el código real de los subprogramas definidos en la especificación del paquete. Los procedimientos y funciones que se llaman desde un paquete se llaman mediante una notación de puntos: nombre_paquete.nombre_procedimiento o nombre_función En el paquete que se muestra en la Figura 8.12, los subprogramas se pueden llamar de la siguiente forma: SQL> SELECT money.compute_tax(salary) FROM hr.employees WHERE employee id=107; SQL> EXECUTE money.give_raise_to_all; Figura 8.12: Creación de paquetes UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 8/17 8.4.6. Especificación y cuerpo del paquete La especificación y el cuerpo de un paquete son objetos distintos que se almacenan por separado en la base de datos (Figura 8.13). El cuerpo de los paquetes presentan las siguientes características: Están separados de las especificaciones del paquete. Por este motivo, el código del cuerpo se puede cambiar y recompilar, y otros objetos que son dependientes de la especificación no se marcan como no válidos. Contienen el código para los subprogramas definidos en la especificación del paquete. Aquí es dónde se realiza el trabajo. La especificación muestra cómo llamar a los subprogramas del paquete; el cuerpo es la sección de código. No se pueden compilar a menos que la especificación del paquete ya se haya compilado. Puede crear una especificación sin cuerpo, pero no puede crear un cuerpo sin especificación. Se pueden ajustar para ocultar detalles del código. Wrap es un programa autónomo que oculta código de origen PL/SQL de manera que puede entregar aplicaciones PL/SQL sin exponer este código de origen. También se pueden sobrecargar las funciones y procedimientos definidos en un paquete. La sobrecarga es la capacidad de crear múltiples versiones de un procedimiento con diferentes argumentos. Figura 8.13: Especificación del cuerpo del paquete 8.4.7. Paquetes incorporados Los paquetes PL/SQL incorporados que se proporcionan con la base de datos Oracle proporcionan acceso a funciones ampliadas de la base de datos, como el servicio de gestión de colas avanzadas, el cifrado y la entrada/salida (E/S) de archivos. También incluyen muchas utilidades de administración y de mantenimiento (Figura 8.14). Los paquetes que utiliza un administrador dependen del tipo de aplicaciones que sirve la base de datos. A continuación se muestran algunos de los paquetes de administración y de mantenimiento más comunes: DBMS_STATS: recopilación, visualización y modificación de estadísticas del optimizador DBMS_OUTPUT: generación de salida de PL/SQL DBMS_SESSION: acceso PL/SQL a las sentencias ALTER SESSION y SET ROLE DBMS_RANDOM: generación de números aleatorios UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 9/17 DBMS_UTILITY: obtenciónde la hora, hora de la CPU y la información de versión; cálculo de un valor hash y realización de otras muchas funciones DBMS_SCHEDULER: planificación de funciones y procedimientos que se pueden llamar desde PL/SQL DBMS CRYPTO: cifrado y descifrado de datos de la base de datos UTL_FILE: lectura y escritura en los archivos del sistema operativo desde PL/SQL UTL_HTTP: permite la realización de llamadas mediante el protocolo http desde SQL o PL/SQL. UTL_TCP: permite la comunicación de programas PL/SQL con servidores externos basados en TCP/IP utilizando este protocolo. Figura 8.14: Paquetes incorporados 8.5. DISPARADORES O TRIGGERS Los disparadores son objetos de código PL/SQL que se almacenan en la base de datos y que se ejecutan o automáticamente cuando sucede un evento (Figura 8.15). La base de datos Oracle permite que muchas acciones sirvan como eventos disparadores, entre las que se incluyen una inserción en una tabla, la conexión de un usuario a la base de datos y el intento de borrar una tabla o el cambio de valores de auditoría. Los disparadores pueden llamar a otros procedimientos o funciones. Lo más recomendable es acortar todo lo posible el código del disparador y colocar todo aquello que necesite un código más largo en un paquete independiente. Se recomienda que el uso de disparadores o triggers se haga con suma precaución y luego de un detallado análisis, ya que el abuso de los mismos puede conducir a complejas interdependencias de objetos que pueden complicar la manutención de los mismos e impactar en el rendimiento global de la base de datos. Los DBA utilizan disparadores para ayudar en las auditorías basadas en valores (se tratará en la lección titulada “Implementación de Seguridad de la Base de Datos Oracle”), para aplicar restricciones complejas y para automatizar muchas tareas. Por ejemplo, el disparador SECURE_EMPLOYEES que se muestra en la diapositiva registra todas las sentencias DML en una tabla de mantenimiento. Es posible aplicar los disparadores para realizar restricciones de integridad. Sin embargo, Oracle recomienda que utilice en la mayoría de los casos, las restricciones de integridad provistas por la base de datos (Primary Key, Unique, Foreign key, Not Null, Check), salvo que necesite realizar validaciones de integridad complejas que no pueda resolver con las restricciones anteriores o restricciones de integridad en una base de datos distribuida. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 10/17 Figura 8.15: Definición de un Disparador o Triggers 8.5.1. Eventos disparadores Existen muchos eventos que se pueden utilizar para arrancar un disparador y se dividen en tres categorías. La Tabla 8.1 muestra los tipos de eventos con sus ejemplos. Los disparadores de eventos DML se arrancan cuando las sentencias modifican los datos. Los disparadores de eventos DDL se arrancan cuando las sentencias crean un objeto o lo modifican de alguna forma. Los disparadores de evento de base de datos se arrancan cuando suceden ciertos eventos en la base de datos. Se puede especificar que la mayoría de los disparadores arranquen antes o después de que se produzca el evento. Con los eventos DML, el disparador se puede diseñar para que se arranque una vez con la sentencia o con cada fila que se modifique. Tabla 8.1: Tipos de eventos y ejemplos UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 11/17 8.6. BLOQUEOS Los bloqueos son un mecanismo, provisto por la base de datos, para prevenir la interacción destructiva entre transacciones que necesitan acceder al mismo recurso, ya sean objetos que almacenan datos como tablas u objetos del sistema no visibles al usuario. Este mecanismo de bloqueos evita que un usuario modifique datos que están siendo modificados por otro, ofreciendo de este modo un control exclusivo de los datos y garantizando la consistencia de los mismos. El mecanismo de bloqueos de Oracle está íntimamente relacionado con el control de transacciones, por ello los diseñadores de aplicaciones sólo deben definir correctamente dichas transacciones y Oracle gestiona en forma automática los bloqueos. Esto significa que, en la gran mayoría de los casos, el usuario no necesita explicitar ningún tipo de bloqueo sino que la base de datos se encarga de su manejo (Figura 8.16). Este mecanismo realiza los bloqueos al menor nivel de restricción que garantice la integridad de datos pero a la vez permita el mayor grado de concurrencia. Figura 8.16: Bloqueos 8.6.1. Mecanismo de bloqueo El mecanismo de bloqueo está diseñado para proporcionar el máximo grado posible de simultaneidad de datos en la base de datos. Las transacciones que modifican datos adquieren bloqueos de nivel de fila en lugar de bloqueos de nivel de tabla o de bloque. Las modificaciones de objetos (como movimientos de tabla) obtienen bloqueos de nivel de objeto en lugar de bloqueos de todo el esquema o base de datos. Las consultas de datos no necesitan ningún bloqueo y una consulta se realiza correctamente aunque los datos estén bloqueados (mostrando siempre el original, el valor previo al bloqueo reconstruido a partir de información de deshacer). Cuando varias transacciones necesitan bloquear el mismo recurso, la primera transacción que solicita el bloqueo lo obtiene. Las demás transacciones esperan hasta que termina la primera transacción. El mecanismo de cola es automático y no necesita la interacción del administrador. Todos los bloqueos se liberan al final de una transacción. Las transacciones se completan cuando se emite un comando COMMIT o ROLLBACK. En caso de una transacción fallida, el mismo proceso en segundo plano que automáticamente realiza un rollback de los cambios de la transacción fallida libera todos los bloqueos retenidos por esa transacción (Figura 8.17). UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 12/17 Figura 8.17: Mecanismo de bloqueo 8.6.2. Simultaneidad de datos El mecanismo de bloqueo es por defecto un modo de bloqueo detallado de nivel de fila. Distintas transacciones pueden estar actualizando diferentes filas dentro de la misma tabla sin interferir las unas en las otras (Tabla 8.2). Aunque el modelo por defecto es el bloqueo en el nivel de fila, la base de datos Oracle soporta el bloqueo manual en niveles superiores si es necesario: SQL> LOCK TABLE employees IN EXCLUSIVE MODE; Table(s) Locked. Con la sentencia anterior, cualquier otra transacción que intente actualizar una fila de la tabla bloqueada debe esperar hasta que termine la transacción que emitió la solicitud de bloqueo. El modo de bloque EXCLUSIVE es el más absoluto. Los otros modos de bloqueo son los siguientes: ROW SHARE: permite el acceso simultáneo a la tabla bloqueada, pero prohíbe que las sesiones bloqueen toda la tabla para un acceso exclusivo. ROW EXCLUSIVE: igual que ROW SHARE, pero también prohíbe el bloqueo en el modo SHARE. Los bloqueos ROW EXCLUSIVE se obtienen automáticamente al actualizar, insertar o suprimir datos. SHARE: permite consultas simultáneas, pero prohíbe actualizaciones en la tabla bloqueada. Es necesario un bloqueo SHARE (y se solicita automáticamente) para crear un índice en una tabla. SHARE ROW EXCLUSIVE: se utiliza para consultar una tabla completa y permitir que otros consulten filas de la misma, pero prohíbe que los demás bloqueen la tablaen modo SHARE o actualicen filas. EXCLUSIVE: permite realizar consultas de la tabla bloqueada pero prohíbe las demás actividades en ella. Es necesario un bloqueo EXCLUSIVE para borrar una tabla. Como cualquier solicitud de bloqueo, las sentencias de bloqueo manual esperan hasta que todas las sesiones que ya tienen bloqueos o que los han solicitado con anterioridad liberen sus bloqueos. El comando LOCK acepta un argumento especial que controla el comportamiento de espera, NOWAIT. NOWAIT devuelve el control inmediatamente si la tabla especificada ya está bloqueada por otra sesión: SQL> LOCK TABLE hr.employees IN SHARE MODE NOWAIT; LOCK TABLE hr.employees IN SHARE MODE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 13/17 Normalmente no es necesario bloquear manualmente los objetos. El mecanismo de bloqueo automático proporciona la simultaneidad de datos necesaria para la mayoría de las aplicaciones. Tabla 8.2: Ejemplo de actualización de filas distintas 8.6.3. Bloqueos DML Las transacciones de manipulación de datos o DML obtienen dos clases de bloqueos (Figura 8.18): Bloqueo EXCLUSIVE de fila para la fila o filas que se van a actualizar. Bloqueo de nivel de tabla ROW EXCLUSIVE en la tabla que se va a actualizar. Sirve para evitar que otra sesión bloquee la tabla completa (posiblemente para borrarla o truncarla) mientras se realiza el cambio. Los bloqueos a nivel de fila evitan que dos transacciones modifiquen al mismo tiempo datos de la misma fila. No hay límites en el número de bloqueos de fila que una transacción obtenga. Este tipo de bloqueos provee el mayor grado de granularidad posible y por ello brinda el mejor grado de concurrencia y el mejor nivel de productividad. Al adquirir un bloqueo a nivel de fila, Oracle le brinda a la transacción un bloqueo a nivel de tabla que evita que las sentencias de definición de datos o DDL puedan modificar esos mismos datos de la tabla que está siendo actualizada. Figura 8.18: Bloqueos DML a nivel de fila y tabla 8.6.4. Mecanismo de puesta en cola Las solicitudes de bloqueos se ponen en cola automáticamente. En cuanto termina la transacción que retiene un bloqueo, la siguiente sesión de la cola recibe el bloqueo (Figura 8.19). El mecanismo de puesta en cola realiza el seguimiento del orden en el que se han solicitado los bloqueos y el modo de bloqueo solicitado. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 14/17 Las sesiones que ya retienen un bloqueo pueden solicitar convertir dicho bloqueo sin tener que ir al final de la cola. Por ejemplo, si una sesión retiene un bloqueo SHARE en una tabla, la sesión puede solicitar convertir el bloqueo SHARE en un bloqueo EXCLUSIVE. Siempre que nadie más tenga ya un bloqueo EXCLUSIVE o SHARE de la tabla, se otorgará un bloqueo SHARE a la sesión que retiene el bloqueo EXCLUSIVE sin tener que volver a esperar en la cola. Figura 8.19: Mecanismo de puesta en cola 8.6.5. Conflictos de bloqueo Los conflictos de bloqueo se producen con frecuencia, pero normalmente se resuelven con el tiempo y el mecanismo de cola. En algunos casos excepcionales, puede que sea necesaria la intervención del administrador en un conflicto de bloqueo. En el caso de la Figura 8.20, la transacción 2 obtiene un bloqueo de una única fila a las 9:00:00 y no se realiza la confirmación, dejando el bloqueo en su lugar. La transacción 1 intenta actualizar toda la tabla a las 9:00:05, lo que necesita un bloqueo de todas las filas. La transacción 2 bloquea la transacción 1 hasta que la transacción 2 realiza la confirmación a las 16:30:01. El usuario que intenta realizar la transacción 1 se pondría en contacto casi con toda probabilidad con el administrador para solicitar ayuda en este caso, y el DBA debería detectar y resolver el conflicto. Figura 8.20: Conflictos de bloqueo UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 15/17 8.6.6. Posibles causas de conflictos de bloqueo La causa más común de conflictos de bloqueo es un cambio sin confirmar, aunque existen algunas otras causas posibles (Figura 8.21): Transacciones con una ejecución muy larga: muchas aplicaciones utilizan el procesamiento por lotes para realizar actualizaciones en bloque. Estos trabajos por lotes normalmente se planifican para momentos de baja o ninguna actividad de los usuarios, pero puede que en algunos casos no hayan terminado o tarden demasiado en ejecutarse durante el período de baja actividad. Los conflictos de bloqueo son comunes cuando la transacción y el procesamiento por lotes se están realizando simultáneamente. Niveles altos de bloqueo innecesarios: no todas las bases de datos soportan el bloqueo de nivel de fila (Oracle introdujo el soporte para los bloqueos de nivel de fila en 1988 con la versión 6). Algunas bases de datos aún realizan bloqueos en el nivel de tabla o de página. Los desarrolladores que escriben aplicaciones destinadas a ejecutarse en muchas bases de datos diferentes suelen escribir sus aplicaciones con niveles de bloqueo artificialmente altos para que la base de datos Oracle se comporte de manera similar a aquellos sistemas de base de datos con menos posibilidades. Además, los desarrolladores nuevos en Oracle a veces codifican innecesariamente en niveles de bloqueo más altos de los que necesita la base de datos Oracle. Figura 8.21: Posibles causas de conflictos de bloqueo 8.6.7. Detección de conflictos de bloqueo Enterprise Manager Database Control le permite individualizar conflictos de bloqueo desde la página Blocking Sessions. La página muestra en un formato jerárquico las solicitudes en conflicto, situando en la parte superior la que retiene el bloqueo, seguidas de todas las sesiones que están en cola para el mismo (Figura 8.22). Como información útil puede verse el nombre de usuario, el identificador de sesión y el número de segundos que la sesión ha estado en espera. También es posible, mediante el identificador de sesión, ver las sentencias SQL reales ejecutadas o solicitadas por la sesión en ese momento. El monitor de diagnóstico de base de datos automático (ADDM) también detecta de manera automática los conflictos de bloqueo y puede avisarle de las tendencias de bloqueo ineficaces. Figura 8.22: Detección de conflictos de bloqueo UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 16/17 8.6.8. Resolución de conflictos de bloqueo Para resolver un conflicto de bloqueo, la sesión que retiene el bloqueo debe liberarlo. El mejor modo de que la sesión libere el bloqueo es ponerse en contacto con el usuario y pedir que termine la transacción. En caso de emergencia, el administrador puede terminar la sesión que retiene el bloqueo haciendo clic en el botón Kill Session (Figura 8.23). Recuerde que cuando se mata una sesión todo el trabajo de la transacción actual se pierde (se realiza un rollback). Un usuario cuya sesión se ha matado debe volver a conectarse y rehacer todo el trabajo desde la última confirmación de la sesión matada. Los usuarios cuyas sesiones se han matado recibirán el siguiente error la siguiente vez que intenten emitir una sentencia SQL: ORA-03135: connection lost contact Figura 8.23: Resolución de conflictosde bloqueo 8.6.9. Resolución de conflictos de bloqueo mediante SQL La manipulación de sesiones, al igual que la mayoría del resto de tareas realizadas en Enterprise Manager, también se puede realizar mediante la emisión de sentencias SQL. La tabla v$session contiene detalles de todas las sesiones conectadas. El identificador blocking_session identifica la sesión que se quiere bloquear. De esta forma, si se consulta el valor del SID y SERIAL#, donde SID se corresponde con un identificador de sesión de bloqueo, se dispone de la información necesaria para realizar la operación kill session. La sentencia SQL ALTER SYSTEM KILL SESSION toma como parámetros el SID (Session ID o identificador de sesión) y Serial# (Serial Number) que identifica unívocamente a una sesión, y de esa forma se puede eliminar una sesión previamente identificada (Figura 8.24). Figura 8.24: Utilización de la sentencia Alter System Kill Session UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 17/17 8.6.10. Interbloqueos Un interbloqueo es un ejemplo especial de conflicto de bloqueo, también conocido como abrazo mortal. Los interbloqueos surgen cuando dos o más sesiones esperan los datos que bloquean entre ellas. Puesto que cada una está esperando a la otra, ninguna puede terminar la transacción para resolver el conflicto. La base de datos Oracle detecta automáticamente los interbloqueos y termina la sentencia con un error. La respuesta adecuada a ese error es una acción de confirmación o de rollback, que libera cualquier otro bloqueo de esa sesión para que la otra sesión pueda continuar con su transacción. En el ejemplo de la Figura 8.24, la transacción 1 debe realizar una confirmación o un rollback como respuesta al error detectado de interbloqueo. Si realiza una confirmación, necesitará volver a enviar la segunda actualización para terminar su transacción. Si realiza un rollback, debe volver a enviar ambas sentencias para terminar su transacción. Figura 8.24: Ejemplo de interbloqueo
Compartir