AA10-EV3-MANEJO DE TRANSACCIONES, BLOQUEOS Y CONTROL DE
AA10-EV3-MANEJO DE TRANSACCIONES, BLOQUEOS Y CONTROL DE
CONCURRENCIA EN UN MOTOR DE BASE DE DATOS ESPECÍFICO
CONCURRENCIA EN UN MOTOR DE BASE DE DATOS ESPECÍFICO
PRESENTADO POR:
PRESENTADO POR:
HARRY LEYSON MORENO MOSQUERA
HARRY LEYSON MORENO MOSQUERA
SERVICIO NACIONAL DE APRENDIZAJE SENA
SERVICIO NACIONAL DE APRENDIZAJE SENA
PROGRAMA DE FORMACIÓN
PROGRAMA DE FORMACIÓN
ESPECIALIZACIÓN TECNOLÓGICA EN GESTIÓN Y SEGURIDAD DE BASES
ESPECIALIZACIÓN TECNOLÓGICA EN GESTIÓN Y SEGURIDAD DE BASES
DE DATOS
DE DATOS
MODALIDAD VIRTUAL
MODALIDAD VIRTUAL
2018
2018
Desarrollar puntos laboratorio 10
Desarrollar puntos laboratorio 10
4.2.2. Abrir una consulta nueva en SQL Server 2008 R2 y escribir el siguiente 4.2.2. Abrir una consulta nueva en SQL Server 2008 R2 y escribir el siguiente comando
comando
COMMIT TRANSACTION y verificar de nuevo la cantidad de transacciones activas COMMIT TRANSACTION y verificar de nuevo la cantidad de transacciones activas y determinar que hace este comando en la base de datos SecSalud.
y determinar que hace este comando en la base de datos SecSalud. R/ el resultado es:
R/ el resultado es: Auto-atta
Auto-attach to proch to process '[27cess '[2736] [SQL36] [SQL] localho] localhost' on macst' on machine 'localhine 'localhost' suhost' succeededcceeded.. The thread 'localhost [54]' (0x1610) has exited with code 0 (0x0).
The thread 'localhost [54]' (0x1610) has exited with code 0 (0x0).
The program '[2736] [SQL] localhost: localhost' has exited with code 0 (0x0). The program '[2736] [SQL] localhost: localhost' has exited with code 0 (0x0).
Este comando por sí solo no hace nada, debe ser acompañado de otras clausulas, Este comando por sí solo no hace nada, debe ser acompañado de otras clausulas, dado que
dado que este comando este comando se utiliza para se utiliza para Finalizar la transacción si Finalizar la transacción si no se no se hanhan encontrado errores.
encontrado errores.
4.2.3. Que sucede al hacer una consulta de todos los datos de la tabla EPS si 4.2.3. Que sucede al hacer una consulta de todos los datos de la tabla EPS si anteriormente se ejecuta el siguiente comando:
anteriormente se ejecuta el siguiente comando: BEGIN TRANSACTION INSERT
BEGIN TRANSACTION INSERT
INTO EPS (ideps, nombre, estadoeps) INTO EPS (ideps, nombre, estadoeps) VALUES (15
VALUES (15,’confisena’,1) ',’confisena’,1) '
Para la cancelación de la transacción anterior ¿qué comando se debe utilizar?. Para la cancelación de la transacción anterior ¿qué comando se debe utilizar?. R/ sale un error: Cannot insert explicit value for identity column in table 'EPS' when R/ sale un error: Cannot insert explicit value for identity column in table 'EPS' when IDENTITY_INSERT is set to OFF.
IDENTITY_INSERT is set to OFF. Esto ocurre porque
Esto ocurre porque sql server no sql server no permite insertar datos a llaves primarias de permite insertar datos a llaves primarias de formaforma normal:
normal:
hay utilizar el comando, para que deje insertar datos de forma de inserción explicita hay utilizar el comando, para que deje insertar datos de forma de inserción explicita en la tabla a una pk: el comando a utilizar es: SET IDENTITY_INSERT EPS
Ejemplo: Ejemplo:
BEGIN TRANSACTION BEGIN TRANSACTION
SET IDENTITY_INSERT EPS ON SET IDENTITY_INSERT EPS ON
INSERT INTO EPS (ideps, nombre, estadoeps) INSERT INTO EPS (ideps, nombre, estadoeps) VALUES (15,'confisena',1)
VALUES (15,'confisena',1)
4.2.4. Que le falta a la siguiente transacción para que se efectúen los cambios en la 4.2.4. Que le falta a la siguiente transacción para que se efectúen los cambios en la base de datos Secretaria de Salud.
base de datos Secretaria de Salud. BEGIN TRANSACTION
BEGIN TRANSACTION INSERT
INSERT INTO INTO persona persona (idPersona, (idPersona, tipoidentificatipoidentificacion, cion, nombre, nombre, apellido,apellido, fechaNacimiento, sexo)
fechaNacimiento, sexo)
VALUES (1112548, 1, 'Pedro', 'Garcia', 1982-01-27, 'M'); VALUES (1112548, 1, 'Pedro', 'Garcia', 1982-01-27, 'M'); INSERT INTO EPS (nombre, estadoeps)
INSERT INTO EPS (nombre, estadoeps) VALUES ('confinacional',4);
VALUES ('confinacional',4);
R/ lo que hay que hacer es agregar el comando: SET IDENTITY_INSERT [tabla] R/ lo que hay que hacer es agregar el comando: SET IDENTITY_INSERT [tabla] ON, para que esta transacción pueda correr y arreglar campos tabla persona: ON, para que esta transacción pueda correr y arreglar campos tabla persona: tipoidentifiac
tipoidentifiacion, ion, idPersona; idPersona; quedaría:quedaría: BEGIN TRANSACTION
BEGIN TRANSACTION
SET IDENTITY_INSERT EPS ON SET IDENTITY_INSERT EPS ON
INSERT INTO Persona (idPersona, tipoidentificacion, nombre, apellido, INSERT INTO Persona (idPersona, tipoidentificacion, nombre, apellido, fechaNacimiento, sexo)
fechaNacimiento, sexo)
VALUES (111245548, 1, 'Pedro', 'Garcia', 1982-01-27, 'M') VALUES (111245548, 1, 'Pedro', 'Garcia', 1982-01-27, 'M') INSERT INTO EPS (ideps, nombre, estadoeps)
INSERT INTO EPS (ideps, nombre, estadoeps) VALUES (16,'confiacional',4)
VALUES (16,'confiacional',4)
4.2.5. En el siguiente cuadro especificar para cada tipo de transacción si es implícita, 4.2.5. En el siguiente cuadro especificar para cada tipo de transacción si es implícita, explicita o automática.
Transacciones explícitas
Transacciones explícitas
Por el contrario, las Transacciones explícitas son las que se define en el código Por el contrario, las Transacciones explícitas son las que se define en el código T-SQL. Hay que indicar cuando se inici
SQL. Hay que indicar cuando se inician (BEGIN TRANSACTION) y cuando finalizanan (BEGIN TRANSACTION) y cuando finalizan (COMMIT TRANSACTION), y pueden albergar un conjunto de instrucciones dentro (COMMIT TRANSACTION), y pueden albergar un conjunto de instrucciones dentro de la misma transacción.
de la misma transacción.
Cuando se produce el COMMIT, se hacen efectivos los cambios en los ficheros de Cuando se produce el COMMIT, se hacen efectivos los cambios en los ficheros de datos (.mdf y .ndf). Mientr
datos (.mdf y .ndf). Mientras no se realiza el COMMIT las sentencias de los cambiosas no se realiza el COMMIT las sentencias de los cambios se guardan en el log de transacciones (.log), que gracias a este es posible revertir se guardan en el log de transacciones (.log), que gracias a este es posible revertir los cambios si fuese necesario..
los cambios si fuese necesario..
Automática
Automática
Es el modo de administración de transacciones predeterminado de SQL Server Es el modo de administración de transacciones predeterminado de SQL Server Database Engine (Motor de base de datos de SQL Server). Cada instrucción Database Engine (Motor de base de datos de SQL Server). Cada instrucción Transact-SQL se confirma o se revierte cuando finaliza. Si una instrucción termina Transact-SQL se confirma o se revierte cuando finaliza. Si una instrucción termina correctamente, se confirma; si encuentra un error, se revierte. Una conexión a una correctamente, se confirma; si encuentra un error, se revierte. Una conexión a una instancia de Motor de base de datos funciona en modo de confirmación automática instancia de Motor de base de datos funciona en modo de confirmación automática siempre que no se suplante el modo predeterminado mediante transacciones siempre que no se suplante el modo predeterminado mediante transacciones explícitas o implícitas.
explícitas o implícitas.
4.3.1. Abra una
4.3.1. Abra una nueva consulta. nueva consulta. Use la Use la base de datos base de datos Secretaria de Salud Secretaria de Salud En unaEn una nueva consulta ejecute sp_lock y revise los resultados.
Se verifican las consultas definidas en el laboratorio para comenzar la parte de Se verifican las consultas definidas en el laboratorio para comenzar la parte de trabajo con bloqueos de transacciones:
trabajo con bloqueos de transacciones:
4.3. Bloqueos.
4.3. Bloqueos.
Use la base de
Use la base de datos Secretaria de Salud En datos Secretaria de Salud En una nueva consulta ejecute sp_lock una nueva consulta ejecute sp_lock yy revise los resultados. Abra informe de transacciones de bloqueo para verificar que revise los resultados. Abra informe de transacciones de bloqueo para verificar que no hay ningún bloqueo activo. Clic derecho en su bd -> informe -> informe estándar no hay ningún bloqueo activo. Clic derecho en su bd -> informe -> informe estándar -> Todas las transacciones de bloqueo.
CONCURRENCIA, TRANSACCIONES, ACCESOS Y BLOQUEOS. MANEJO DE
CONCURRENCIA, TRANSACCIONES, ACCESOS Y BLOQUEOS. MANEJO DE
JMETER.
JMETER.
2.1 Tipos de Concurrencia de Transacciones 2.1 Tipos de Concurrencia de Transacciones
Optimista: Deja realizar modificacioneOptimista: Deja realizar modificaciones de los datos y se persisten (commitado).s de los datos y se persisten (commitado).
Cuando se van a persistir se verifica que no se han modificado por otras Cuando se van a persistir se verifica que no se han modificado por otras transacciones simultáneamente; en cuyo caso produce un error.
transacciones simultáneamente; en cuyo caso produce un error.
Pesimista: Para los datoPesimista: Para los datos modificados, s modificados, realizar un blorealizar un bloqueo de queo de los mismos.los mismos.
Impendiendo que otras transacciones realicen cambios de esos datos. Impendiendo que otras transacciones realicen cambios de esos datos. 2.2 Tipos de Nivel de Aislamiento de Transacción
2.2 Tipos de Nivel de Aislamiento de Transacción
READ UNCOMMITTED: READ UNCOMMITTED: Leen valores Leen valores modificados por otras modificados por otras transacciones notransacciones no
persistidos (commitados). persistidos (commitados).
READ COMMITTED: READ COMMITTED: No dejan No dejan leer valores leer valores modificadmodificados os por otras por otras transaccionestransacciones
no
no persistidos persistidos (commitados). (commitados). READ READ COMMITTED COMMITTED al al releer releer datosdatos que se han comitado por otra transacción durante la ejecución de la propia; que se han comitado por otra transacción durante la ejecución de la propia; obtiene valores diferentes.
obtiene valores diferentes.
READ_COMMITTED_SNAPSHOT: Evita READ_COMMITTED_SNAPSHOT: Evita el problema el problema del REdel READ CAD COMMITED.OMMITED.
Crea un estado en la base de datos; de es
Crea un estado en la base de datos; de esta manera la transacción lee ta manera la transacción lee los datoslos datos referidos a ese estado. No impide que otras transacciones modifiquen los datos referidos a ese estado. No impide que otras transacciones modifiquen los datos leídos por la nuestra.
leídos por la nuestra.
REPEATABLE READ: REPEATABLE READ: Evita Evita el el problema problema del del READ READ COMMITED. COMMITED. REPEATABLEREPEATABLE
READ evita que otra transacción modifique los datos modificados por nuestra READ evita que otra transacción modifique los datos modificados por nuestra transacción. Dado que los datos leídos; pueden depender de lo realizado en la transacción. Dado que los datos leídos; pueden depender de lo realizado en la otra transacción.
otra transacción.
SERIALIZABLE: REPEATABLE READ que además se extiende paraSERIALIZABLE: REPEATABLE READ que además se extiende para
inserciones. inserciones.
2.3 TRANSACCIONES EN SQL SERVER 2.3 TRANSACCIONES EN SQL SERVER
En SQL Server el tipo de concurrencia es pesimist
En SQL Server el tipo de concurrencia es pesimista. El bloqueo se activa al modificara. El bloqueo se activa al modificar los datos; no al leerlos. Si queremos activarlo en su lectura con debemos usar la los datos; no al leerlos. Si queremos activarlo en su lectura con debemos usar la cláusula WITH UPDLOCK.
cláusula WITH UPDLOCK.
SELECT * FROM TABLE WITH (UDPLOCK) WHERE ID = 1 SELECT * FROM TABLE WITH (UDPLOCK) WHERE ID = 1
El nivel del Aislamiento en SQL SERVER se indica con la instrucción SET El nivel del Aislamiento en SQL SERVER se indica con la instrucción SET TRANSACTION ISOLATION LEVEL. Por defecto es READ COMMITTED.
TRANSACTION ISOLATION LEVEL. Por defecto es READ COMMITTED.
En SQL SERVER cuando indicamos READ COMMITTED, puede ser READ En SQL SERVER cuando indicamos READ COMMITTED, puede ser READ COMMITTED o READ COMMITED SNAPSHOT. Esto se determina en función de COMMITTED o READ COMMITED SNAPSHOT. Esto se determina en función de la configuración de la base de datos:
la configuración de la base de datos:
READ COMMITED SNAREAD COMMITED SNAPSHOT: La base PSHOT: La base de datos se de datos se encuentra con laencuentra con la
configuración SET READ_COMMITTED_SNAPSHOT ON. configuración SET READ_COMMITTED_SNAPSHOT ON.
READ CREAD COMMITTED: La OMMITTED: La base de base de datos se datos se encuentra con encuentra con la configuración la configuración SETSET
READ_COMMITTED_SNAPSHOT OFF READ_COMMITTED_SNAPSHOT OFF
En SQL AZURE solo existe READ COMMITTED SNAPSHOT. No se puede En SQL AZURE solo existe READ COMMITTED SNAPSHOT. No se puede desactivar con la configuración SET READ_COMMITTED_SNAPSHOT OFF.
desactivar con la configuración SET READ_COMMITTED_SNAPSHOT OFF. 2.4 TRANSACCIONES EN OTROS ENTORNOS
2.4 TRANSACCIONES EN OTROS ENTORNOS En Entity Framework, LINQ, DataSet,… el
En Entity Framework, LINQ, DataSet,… el tipo de concurrencia es optimista.tipo de concurrencia es optimista.
En Hibernate, por defecto tiene concurrencia optimista. Pero se puede habilitar En Hibernate, por defecto tiene concurrencia optimista. Pero se puede habilitar concurrencia pesimista.
concurrencia pesimista.
En general, se recomienda el uso del Transa
En general, se recomienda el uso del TransactionScope, para determinar el nictionScope, para determinar el nivel devel de aislamiento
aislamiento con con TransactionScopeOptiTransactionScopeOption.on.
RENDIMIENTO CON JMETER
RENDIMIENTO CON JMETER
Se ha permitido desarrollar una herramienta de análisis de resultados similar a la Se ha permitido desarrollar una herramienta de análisis de resultados similar a la que proporcionan las herramientas comerciales. Las princi
que proporcionan las herramientas comerciales. Las principales ventajas que aportapales ventajas que aporta son:
son:
Permite generar Permite generar un un informe dinforme de e pruebas pruebas de de resultados de resultados de manera aumanera automáticatomática
sin necesidad de tratar los datos en hojas de cálculo. sin necesidad de tratar los datos en hojas de cálculo.
Permite estudiar Permite estudiar de de manera gmanera gráfica la ráfica la relación de relación de los los diferentes indicadoresdiferentes indicadores
de las
de las pruebas, pruebas, generando automáticamente generando automáticamente gráficas de gráficas de rendimiento.rendimiento.
Facilita la Facilita la Identificación de Identificación de los los posibles pposibles puntos de untos de saturación para saturación para detectardetectar
"cuellos de botella". "cuellos de botella".
Desenmascara los Desenmascara los posibles errores posibles errores de la de la aplicación por aplicación por la aplicación la aplicación dede
concurrencia. concurrencia.
Permite recuperar Permite recuperar de de manera íntegra manera íntegra los resultados los resultados de de pruebas pruebas anteriores, oanteriores, o
almacenarlos
almacenarlos en en un un histórico histórico para para posibles posibles comparaciones comparaciones de de resultados.resultados.
El objetivo final es dotar al ingeniero de pruebas de una herramienta que aumente El objetivo final es dotar al ingeniero de pruebas de una herramienta que aumente su productividad, y también la capacidad de análisi
su productividad, y también la capacidad de análisis para dotar de mayor calidad lass para dotar de mayor calidad las pruebas de rendimiento.
pruebas de rendimiento.
Manejo de transacciones y
Para este laboratorio, se Utilizaron par
Para este laboratorio, se Utilizaron parámetros de configuración en el base de datos,ámetros de configuración en el base de datos, los cuales por motivo de facilidad y configuración de mi equipo pueden diferir de los los cuales por motivo de facilidad y configuración de mi equipo pueden diferir de los recomendados por el Tutor, por ello los detallo.
recomendados por el Tutor, por ello los detallo. Nombre Servidor: localhost
Nombre Servidor: localhost
Nombre de variable sql server: pc-PC Nombre de variable sql server: pc-PC Usuario conexión base datos: pc-PC Usuario conexión base datos: pc-PC Password: 123
Password: 123
Puerto conexion: 1433 Puerto conexion: 1433 base de datos: Sec_salud base de datos: Sec_salud Anexo la pantalla de conexió
Anexo la pantalla de conexión de sql server, donde se detalla n de sql server, donde se detalla que no conecto conque no conecto con autenticación de Sql server si no de Windows y utilizo el localhost de la máquina. autenticación de Sql server si no de Windows y utilizo el localhost de la máquina.
En las configuraciones de seguridad como les he definido, el usuario de conexión En las configuraciones de seguridad como les he definido, el usuario de conexión es pc-PC
Primero se define el grupo de hilos a trabajar o usuarios a concurrir: Primero se define el grupo de hilos a trabajar o usuarios a concurrir:
Se define la conexion con el controlador jdb, quien permitira acceder al Sql server: Se define la conexion con el controlador jdb, quien permitira acceder al Sql server:
Luego se define la conexión jdbc: Luego se define la conexión jdbc:
URL
URL BD: jBD: jdbc:pc-PC://localhdbc:pc-PC://localhost:1433;databaseName=Seost:1433;databaseName=Sec_saludc_salud DRIVER
DRIVER JDBC: JDBC: com.microsoft.sqlcom.microsoft.sqlserver.jdbc.SQLServerDriverserver.jdbc.SQLServerDriver USUARIO: pc-PC
USUARIO: pc-PC A contin
Luego se anexa el componente gráfico: Luego se anexa el componente gráfico:
Aquí
Aquí una una vez vez dato dato el el Play Play en en ejecutar, vemos ejecutar, vemos como como el el grafico grafico comienzcomienza a a a tenertener cambios y ver los resultados de las transacciones o consultas de forma graficas cambios y ver los resultados de las transacciones o consultas de forma graficas definida en los hilos de conexión.
definida en los hilos de conexión.
Anexo
Anexo reporte reporte resumeresumen, n, un run reporte eporte más numás numérico mérico que que permite permite verificar verificar varianzvarianza dea de datos y concurrencia.
datos y concurrencia.
Otra grafica tipo spline que perm
Otra grafica tipo spline que permite ver en una secuencia más lineal, la concurrenciaite ver en una secuencia más lineal, la concurrencia de las transacciones medidas por el Jmeter.
Anexo
Anexo el el monitor monitor de de rendimienrendimiento to de de Sql Sql server server en en función, función, dentro dentro del del menúmenú performance tool, programas del Pack de instalación de Sql server, server profile performance tool, programas del Pack de instalación de Sql server, server profile
Para más detall