ALCALDÍA DE SAN ANTONIO DEL SENA Dirección de Servicios de Tecnologías de Información y Comunicaciones DSTIC
Socialización y evaluación del modelo transaccional en un motor de bases de datos Octubre de 2017 ¡Garantizamos la confidencialidad de su información y la integridad de sus medios!
Presentado por: Juan Álvaro Bravo Rivera No. De ficha 1413037 Jhonny Delgado No. De Ficha 1413038 Alejandro Gómez Rodríguez No. De ficha 1413038
Contenido 1. Introducción .......................................................................................................................... 3 2. Objetivo ................................................................................................................................... 3 2.1 Objetivos especificos ................................................................................................... 3 3. Alcance .................................................................................................................................... 4 4. Consideraciones ................................................................................................................. 4 5. Preguntas formuladas ....................................................................................................... 4 5.1 ¿Como es el manejo transaccional en una base de datos dependiendo del motor utilizado? .................................................................................................................... 4 5.2 ¿Cómo se genera un bloqueo? ............................................................................. 10 5.3 ¿ Como se libera un bloqueo? ............................................................................... 12 5.4 ¿ De que forma se hace seguimiento a las transacciones en un determinado momento?.................. ................. .................. .................. ................. ................. 13 5.5 ¿Cuándo múltiples usuarios acceden en un mismo instante de tiempo a un recurso de la Base de Datos, como gestiona el motor la concurrencia? 24
6. Glosario ................................................................................................................................ 24 7. Referencias ......................................................................................................................... 27
Página. 2
1. Introducción
La Evaluación de una Base de Datos BD, es una de las tareas más importantes de un Administrador de Base de Datos. Este profesional es el encargado de analizar constantemente el funcionamiento del Sistema manejador de Bases de Datos SMBD, para optimizar el uso de los recursos, como CPU, Memoria, Disco y Red, para ver su desempeño. El análisis constante de un Sistema Manejador de Bases de Datos SMBD, permite ver su desempeño en todo momento. Si se detecta que las transacciones no son respondidas con la rapidez que se necesita o se pierden datos, el Administrador de la Base de Datos, debe aplicar las medidas correctivas para solucionar las fallas, analizando su comportamiento a través de herramientas gráficas provistas por el fabricante de la aplicación. En este laboratorio se realizaran actividades en las que se aborda situaciones problemáticas que le ayudaran al administrador de bases de datos SQL Server a desarrollar habilidades que le permitan realizar un control adecuado sobre la concurrencia y bloqueos en la base de datos. Los DBA tienen la responsabilidad de mantener disponibles los servicios que presta la base de datos, para ello deben controlar el comportamiento de los procesos que acceden de forma concurrente sobre datos compartidos y los bloqueos que estos generan sobre los mismos. (Senaintro.blackboard.com, 2017)
2. Objetivo
Una vez conocido el detalle de los procedimientos y acciones a través de los cuales el motor de base de datos seleccionado gestiona transacciones, controla bloqueos y concurrencia (Actividad AA10-3), debe participar en el foro temático “Socialización y evaluación del modelo transaccional en un motor de Bases de Datos específico”, aportando una conclusión con respecto a la manera como se manejan transacciones, bloqueos y concurrencia en el SMBD de su elección. Así la información estará dispuesta en la plataforma para recibir la retroalimentación de sus compañeros y además debe dar su opinión sobre el trabajo de dos o más participantes del proceso. 2.1 Objetivos especificos •
•
•
Reconocer las herramientas que son útiles para la supervisión de transacciones y bloqueos en el SMBD SQL Server. Utilizar las herramientas de supervisión para monitorear el comportamiento de las transacciones y bloqueos en el SMBD SQL Server. Administrar los recursos utilizados por las transacciones y bloqueos para mantener disponible los servicios de la base de datos SQL.
Página. 3
3. Alcance
La evidencia del desarrollo de esta actividad va desde participar en el foro temático de acuerdo a las indicaciones dadas en la descripción de la actividad. Ingresando al foro a través del enlace disponible en la opción del menú del curso Actividades. 4. Consideraciones
Antes de realizar la actividad es importante revisar aspectos tales como: • • •
Soporte teórico Productos requeridos Herramientas de SW
5. Preguntas formuladas
El presente laboratorio está compuesto por una serie de preguntas que permitirán abordar los principales aspectos que deben ser tenidos en cuenta para gestionar transacciones, controlar bloqueos, y concurrencia. 5.1 ¿Como es el manejo transaccional en una base de datos dependiendo del motor utilizado?
Una transacción es un evento secuencial de cambios a una base de datos las transacciones se emplean como mecanismos de petición, los ejemplos más básicos de transacciones son inserción, borrado y actualización de una base de datos, puede generarse transacciones más complejas que contengan una serie de pasos. Es importante conocer las propiedades de una transacción; las transacciones deben poseer las siguientes características para ser exitosas e integrales: •
•
•
•
Atomicidad: Todas las transacciones dentro de la sentencia deben ejecutarse sin errores si por algún motivo no se cumple, la transacción aborta en el punto de ruptura y deshace todos los cambios Consistencia: Se debe asegurar que todos los cambios en la base de datos sean correctos y estén guardados. Aislamiento: Dos transacciones no pueden estar visualizando información al mismo tiempo. Durabilidad: La transacción debe asegurar que los cambios realizados perdurarán aunque el sistema falle. Página. 4
Para dirigir el flujo y mantener sus propiedades existen las siguientes sentencias en SQL: • • •
Commit: Guarda los cambios realizados Rollback: Deshace los cambios realizados Savepoint: Crea un punto de restauración dentro de un conjunto de trasacciones para luego deshacer los cambios si es necesario
Manejo de transacciones: EL manejo transaccional inicia tras ingresar a la base de datos en este caso seleccionaremos la tabla SecSalud (Secretaria de salud), daremos clic derecho sobre esta tabla y clic en mostrar las primeras filas para evidenciar que tengamos información.
Como podemos apreciar la información contenida en la tabla es la siguiente.
Página. 5
Ahora procederemos a elaborar dos (2) transacciones diferentes insertando, actualizando o eliminando registro de esta base de datos, todas estas transacciones deben quedar abiertas. Ahora procederemos a iniciar la transacción.
Como podemos ver los comandos se han ejecutado correctamente. A continuación el código empleado. BEGIN TRANSACTION INSERT INTO EstadoEPS (descripcion) VALUES ('Sin registro')
Página. 6
Como podemos ver los comandos se han ejecutado correctamente. A continuación el código empleado. BEGIN TRANSACTION UPDATE EstadoEPS SET descripcion='Arbitrada' WHERE descripcion='Intervenida'
El comando COMMIT TRANSACTION le confirma al Sistema Manejador de Bases de Datos SMBD que las modificaciones ejecutadas anteriormente deben ser permanentes en la base de datos, es decir es el comando que autoriza la ejecución de una sentencia SQL o proceso de forma que sea permanente lo que se desea realizar, bien sea para crear, consultar, guardar, modificar o eliminar en la base de datos o parte de ella. Al volver a consultar las sentencias anteriores tuvieron efecto es decir fueron efectuadas de forma permanente de tal forma que se realizó un nuevo registro en la tabla con descripción “Sin registro” y se actualizo el registro con descripción “Intervenida” por “Arbitrada”.
Página. 7
Que sucede al hacer una consulta de todos los datos de la tabla EPS si anteriormente se ejecuta el comando? BEGIN TRANSACTION INSERT INTO EPS (ideps, nombre, estadoeps) VALUES (15,'confisena',1)
Página. 8
Al realizar una consulta en la tabla EPS despues de ejecutar la sentencia, se notan los mismos datos que tenia anteriormente, concluyendo que no se realizo el registro propuesto en la sentencia ya que se genero un error con el mensaje de “consulta completada con errores” A continuación la evidencia.
Al ser una clave primaria y estar identificada como única en la tabla y auto incrementable, es decir que el SMBD lo aumenta en uno este campo, en las inserciones no hay necesidad de nombrar y enviar un valor en este campo ya que el SMBD se encarga de eso por ello es que internamente se apaga o se coloca en estado OFF la propiedad IDENTITY_INSERT ya que si fuere ON su estado es porque permite la inserción, ya que no es auto incrementable el campo sin importar si es o no una clave primaria. Para cancelar la transacción hay que ejecutar la sentencia: ROLLBACK TRANSACTION
Página. 9
5.2 ¿Cómo se genera un bloqueo?
Bloqueos. Abra una nueva consulta. Use la base de datos Secretaria de Salud En una nueva consulta ejecute sp_lock y revise los resultados.
Página. 10
Abra informe de transacciones de bloqueo para verificar que no hay ningún bloqueo activo. Clic derecho en su bd -> informe -> informe estándar -> Todas las transacciones de bloqueo.
Página. 11
Al realizar la anterior práctica se consultaron los bloqueos que presenta el sistema y en ambos métodos encontramos el mismo resultado. Actualmente no hay transacciones de bloqueo para la base de datos (SecSalud). En conclusión un bloqueo es generado tras requerir lectura o modificación de datos modificados por otros usuarios y esta es la razón principal de la pregunta generada en este apartado. 5.3 ¿ Como se libera un bloqueo?
Para anular bloqueos de una base de datos SQL podemos seguir los pasos a continuación descritos: 1. Abrir la consola de SQL Server. Ésta se encuentra en el menú "Inicio" de Windows, bajo el menú "SQL Server". Clic en "SQL Server Management Studio" para abrir la consola. 2. Escribir "sp_who 'active' " en el espacio de trabajo y presiona la tecla "Entrar". Esto muestra todos los procesos, nombres de usuario y sesiones de ID asociadas a ellos. Identificar cuál ID está provocando el bloqueo.
Página. 12
3. Identificar la ID de sesión para el registro bloqueado. Desde la lista que apareció en el Paso 2, encontrar el usuario que tiene el bloqueo de registro y apuntar la ID de sesión. Esta ID se requiere para los siguientes pasos. 4. Escribir "Kill " en la consola. Esto removerá el bloqueo de registro en la fila de la tabla. Una vez que el bloqueo haya sido removido, abrir la tabla para comprobar que el bloqueo fue eliminado de forma exitosa. 5. Detener y reiniciar la aplicación que experimentó el problema al consultar la tabla. Este paso es requerido si el bloqueo de registro estaba provocando problemas con una aplicación de escritorio. Esto además ayuda a restablecer cualquier aplicación que haya estado causando errores cuanto intentábamos consultar el registro bloqueado. O bien sea solucionar el bloqueo a través de una reversión con ROLLBACK TRANSACTION, para que la sentencias guarden los cambios de forma permanente se coloca al final de la sentencia un COMMIT TRANSACTION 5.4 ¿ De que forma se hace seguimiento a las transacciones en un determinado momento?
El método más sencillo es el empleo de la herramienta que trae por defecto SQL en el cual a través de una serie de informes que se presentan a continuación puede realizarse el seguimiento en un momento determinado Abra informe de transacciones de bloqueo para verificar que no hay ningún bloqueo activo. Clic derecho en su bd -> informe -> informe estándar -> Todas las transacciones de bloqueo.
Página. 13
Página. 14
Al realizar la anterior práctica se consultaron los bloqueos que presenta el sistema y en ambos métodos encontramos el mismo resultado. Actualmente no hay transacciones de bloqueo para la base de datos (SecSalud). Igualmente podemos realizar multiples informes para hacer seguimiento a las trasacciones, entre los cuales encontramos: Uso de Disco
Página. 15
Uso de disco por tablas superiores
Uso de disco por tabla
Página. 16
Uso de disco por partición
Eventos de copias de seguridad y restauración
Página. 17
Todas las transacciones
Principales transacciones por edad
Página. 18
Principales transacciones por recuento de transacciones bloqueadas
Principales transacciones por recuento de bloqueo
Página. 19
Estadisticas de bloqueo de recursos por objetos
Historial de coherencia de la base de datos
Página. 20
Uso de memoria por objetos con optimización de memoria
Estadisticas de uso de indice
Página. 21
Estadisticas fisicas del indice
Historial de cambios de esquema
Página. 22
Estadisticas de usuario
De igual forma podemos realizar informes personalizados como se muestra continuación
Página. 23
Establecemos criterios y generamos informes acorde a las necesidades de la organizació y del administrador de bases de datos, los cuales son utiles en la presentación de informes y generación de datos que den valor en la toma de decisiones. 5.5 ¿Cuándo múltiples usuarios acceden en un mismo instante de tiempo a un recurso de la Base de Datos, como gestiona el motor la concurrencia?
Si múltiples usuarios acceden en un mismo instante de tiempo a un recurso de la Base de Datos la concurrencia se maneja a través del bloqueo teniendo en cuenta los diferentes tipos de bloqueos que se dan y el Deadlock. 6. Glosario
ATOMICIDAD: Es la propiedad de las transacciones que permite observarlas como operaciones atómicas: ocurren totalmente o no ocurren. Casos a considerar: Consultas unitarias. Incluso para consultas unitarias hay que preservar la atomicidad: en un sistema operativo de tiempo compartido, la ejecución concurrente de dos consultas SQL puede ser incorrecta si no se toman las precauciones adecuadas. Operación abortada. Por ejemplo, debido a una división por cero; por privilegios de acceso; o para evitar bloqueos •
•
ADMINISTRACIÓN DE BASE DE DATOS RELACIONAL: Técnica de administración de bases de datos en la que la información se guarda en tablas de datos bidimensionales. El programa que funciona con dos tablas de datos al mismo tiempo, relaciona la información mediante vinculaciones establecidas por una columna o campos comunes. ACCESO: La manera en la cual los archivos o conjunto de datos son referenciados por la computadora. BASE DE DATOS: Es una serie de datos organizados y relacionados entre sí, los cuales son recolectados y explotados por los Sistemas de Información de una empresa o negocio en particular. BUSCADORES: O también llamados motores de búsqueda, son herramientas que permiten clasificar la información que existe en la red y hacerla localizable en poco tiempo según las preferencias del usuario. CAMPO: Unidad básica de una base de datos, un campo puede ser, por ejemplo, el nombre de una persona. Los nombres de los campos, no pueden empezar con espacios en blanco y caracteres especiales. No pueden llevar puntos, ni signos de exclamación o corchetes. Si pueden tener espacios en blanco en el medio.
Página. 24
CONFIDENCIALIDAD: (ISO/IEC 13335-1:2004) Propiedad de la información por la que está no se muestra disponible o revelada para individuos, entidades o procesos no autorizados. CONSISTENCIA: La ejecución aislada de la transacción conserva la consistencia de la base de datos. CONFIGURACIÓN: Término genérico usado para describir un grupo de Elementos de Configuración que actúan o funcionan juntos para proveer un Servicio de TI, o un subconjunto representativo de un Servicio de TI. El término Configuración también se usa para describir los parámetros y ajustes realizados en uno o más CIs. CONTINGENCIA: Es un tipo preventivo, predictivo y reactivo, en el cual se presenta una estructura estratégica y operativa que ayudará a controlar una situación de emergencia y a minimizar sus consecuencias negativas. DISPONIBILIDAD: (ISACA/CISM) Garantizar que los sistemas de información y los datos estén listos para su uso cuando se les necesita; a menudo se expresa como el porcentaje de tiempo que se puede utilizar un sistema para trabajo productivo. DATOS ESTADÍSTICOS: estos almacenan información estadística sobre los datos en la base de datos. DBMS: Es un conjunto de programas que se encarga de manejar la cremación y todos los acceso a las bases de datos. DDL: Es un lenguaje de definición de datos El DBMS: es un conjunto de programas que se encargan de manejar la creación y todos los accesos a las bases de datos. ELIMINACIÓN: Es una solicitud de eliminación que se expresa de forma muy parecida a una consulta. Sin embargo, en vez de presentar tuplas al usuario, quitamos las tuplas seleccionadas de la base de datos. Sólo puede eliminar tuplas completas; no se puede eliminar únicamente valores de determinados atributos. FACILIDAD DE CONSULTAS: Permitir al usuario hacer cuestiones sencillas a la base de datos. Este tipo de consultas tienen como misión proporcionar la información solicitada por el usuario de una forma correcta y rápida. FORMULARIO: es el elemento en forma de fecha que permite la gestión de los datos de una forma más cómoda y visiblemente más atractiva GESTOR DE BASE DE DATOS: Es un conjunto de programas que permiten crear y mantener una base de datos, asegurando su integridad, confidencialidad y seguridad
Página. 25
INFORMES: Es un trabajo cuyos resultados o cuyo producto es esperado por personas distintas a quien lo realiza o bien el mismo es encargado por terceros. INDEPENDENCIA DE LOS DATOS: Se refiere a la protección contra los programas de aplicaciones que pueden originar modificaciones cuando se altera la organización física y lógica de las bases de datos. JDBC: La Conectividad de Bases de Datos Java (Java Database Connectivity, JDBC) es una especificación de la interfaz de aplicación de programa (application program interface, API) para conectar los programas escritos en Java a los datos en bases de datos populares. LA MANIPULACIÓN DE DATOS: la manipulación de datos es la recuperación de información almacenada en la base de datos LENGUAJE DE CONSULTA: Son los lenguajes en el que los usuarios solicitan información de la base de datos. Estos lenguajes son generalmente de más alto nivel que los lenguajes de programación. Los lenguajes de consulta pueden clasificarse como procedimentales y no procedimentales MANIPULACIÓN DE BASE DE DATOS: Usando la base de Datos -- el usuario puede añadir, borrar y modificar información a la base de datos así como también hacer consultas MODELO DE BASE DE DATOS ORIENTADO A OBJETOS: es una adaptación a los sistemas de bases de datos. Se basa en el concepto de encapsulamiento de datos y código que opera sobre estos en un objeto. MODELOS DE RED: Este modelo permite la representación de muchos a muchos de una Base de Datos. El modelo de red evita redundancia en la información, a través de la incorporación de un tipo de registro denominado el conector. NIVEL LOGICO: definición de las estructuras de datos que constituyen la base de datos. PLAN: Es una propuesta detallada que describe las actividades y recursos necesarios para lograr un objetivo. RECUPERACIÓN: Proporcionar como mínimo el mismo nivel de recuperación que los sistemas de bases de datos actuales. De forma que, tanto en caso de fallo de hardware como de fallo de software, el sistema pueda retroceder hasta un estado coherente de los datos. REGLAS DE INTEGRIDAD: Son restricciones que definen los estados de consistencias de las bases de datos.
Página. 26
SISTEMA DE ADMINISTRACIÓN DE BASES DE DATOS: Es el software que controla la organización, almacenamiento, recuperación, seguridad e integridad de los d atos en una base de datos SOFTWARE: Es un sistema manejador de bases de datos que permite al usuario accesar con facilidad a los datos almacenados o que ande ser almacenados SERVIDOR: Máquina en la cual se almacena Información de las aplicaciones, y/o las mismas aplicaciones. SERVICIOS: Es un conjunto de actividades que buscan responder las necesidades de un cliente, interno y /o externo. SQL: Es un lenguaje declarativo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones en ellas. Una de sus características es el manejo del álgebra y el cálculo relacional que permiten efectuar consultas con el fin de recuperar de forma sencilla información de interés de bases de datos, así como hacer cambios en ella. TI: Tecnología de información TRANSACCIONES COMPARTIDAS: Las transacciones compartidas soportan grupos de usuarios en estaciones de trabajo, los cuales desean coordinar sus esfuerzos en tiempo real, los usuarios pueden compartir los resultados intermedios de una base de datos. La transacción compartida permite que varias personas intervengan en una sola transacción. TRANSACCIÓN: Una transacción es una unidad de la ejecución de un programa. Puede consistir en varias operaciones de acceso a la base de datos. Está delimitada por constructores como Begin Transaction y End Transaction. USUARIO FINAL: es quien acceso a las bases de datos por medio de un lenguaje de consulta o de programas de aplicación. 7. Referencias •
•
Senaintro.blackboard.com. (2017). Citar un sitio web - Cite This For Me. [online] Disponible en: https://senaintro.blackboard.com/webapps/blackboard/content/listContent.jsp? course_id=_1860232_1&content_id=_93567484_1 [Accesado 15 Oct. 2017].https://docs.microsoft.com/es-es/sql/relationaldatabases/indexes/reorganize-and-rebuild-indexes Anon, (2017). [online] Disponible en: https://senaintro.blackboard.com/bbcswebdav/pid-93567531-dt-content-rid-
Página. 27
•
•
•
•
•
127071882_4/institution/semillas/217219_1_VIRTUAL/OAAPs/OAAP5/inst_ev al/egbd-p05-aa10-ev04.pdf [Accesado 15 Oct. 2017]. Angelfire.com. (2017). Glosario de Base de Datos. [online] Disponible en: http://www.angelfire.com/ultra2/pecanpie/Bimestral/Glosario.htm [Accesado 15 Oct. 2017]. iscgarcia, M. (2017). Apuntes de Administración de Bases de Datos Monografias.com. [online] Monografias.com. Disponible en: http://www.monografias.com/trabajos19/administracion-basedatos/administracion-base-datos.shtml [Accesado 15 Oct. 2017]. Co.video.search.yahoo.com. (2017). Cuando Usar Un Deadlock - Yahoo Search Results Resultados de búsqueda de Yahoo Videos. [online] Disponible en: https://co.video.search.yahoo.com/yhs/search;_ylt=A0LEVjrru.NZ00gAyn7XdA x.;_ylu=X3oDMTByMjB0aG5zBGNvbG8DYmYxBHBvcwMxBHZ0aWQDBHNlY wNzYw--?p=Cuando+Usar+Un+Deadlock&fr=yhs-iryfullyhosted_003&hspart=iry&hsimp=yhsfullyhosted_003#id=2&vid=26449bd533ddff707e13d11a72f26360&action=view [Accesado 15 Oct. 2017]. Technet.microsoft.com. (2017). Detectar y finalizar interbloqueos. [online] Disponible en: https://technet.microsoft.com/eses/library/ms178104%28v=sql.105%29.aspx [Accesado 15 Oct. 2017]. Techlandia.com. (2017). Citar un sitio web - Cite This For Me. [online] Disponible en: https://techlandia.com/anular-bloqueo-base-datos-sql-como_137972/ [Accesado 15 Oct. 2017].
Página. 28
