Práctica No. 9. Concurrencia

Cuando desarrollamos aplicaciones multiusuario sobre bases de datos - Oracle, SQL Server, MySQ -- es vital considerar escenarios de concurrencia en donde los usuarios acceden a los mismos datos en forma simultánea.

En este post te quiero presentar una técnica simple y potente que suelo utilizar para hacer pruebas de casos de concurrencia. Me voy a valer de un caso muy simple en el voy a ver qué ocurre cuando bloqueo una tabla en forma completa y exclusiva. Si bien en ambientes productivos hay que tener razones muy justificadas para bloquear una tabla de manera completa y exclusiva; aquí mi idea es hacer énfasis en la técnica de prueba y observación.

Concepto de transaccion

Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.

El ejemplo clásico de transacción es una transferencia bancaria, en la que quitamos saldo a una cuenta y lo añadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, no debemos quitarlo de la cuenta de origen.

SQL Server funciona por defecto con Transacciones de confirmación automática , es decir, cada instrucción individual es una transacción y se confirma automáticamente.

Figura 9.1 Trransacciones SQL con respecto a las capas de aplicación

La figura 9.1. presenta una vista simplificada de la arquitectura de una aplicación de base de datos en las que e las transacciones se encuentran localizadas en una capa de software diferente a la capa de interfaz de usuario. Desde el punto de vista de usuario final, la aplicación proporciona casos de uso materializados como transacciones de usuario. Una transacción de usuario puede incluir múltiples transacciones SQL. Algunas de las cuales incluyen recuperación de datos y usualmente la transacción final actualiza el contenido de la base de datos Los envoltorios de reintento programadas en caso de fallos de concurrencia en las transacciones SQL.

Para comprender el funcionamiento de las transacciones SQL, tenemos que introducir algunos conceptos básicos relativos al diálogo cliente / servidor (Figura 9.2). Para acceder a una base de datos la aplicación tiene que iniciar una conexión con la BD que establece el contexto de una sesión SQL. Por simplicidad una sesión se considera un cliente SQL, El servidor de datos se corresponde con el servidor. Desde el punto de vista del servidor la aplicación usa los servicios de la BD en modo cliente / servidor pasando comandos SQL, como parámetros a las funciones/métodos a través de una API de acceso a datos. Independientemente de la interfaz de acceso a los datos usada el diálogo de ‘bajo nivel’ con el servidor se basa en el lenguaje SQL y el acceso fiable a los datos se se materializa con el uso apropiado de las transacciones SQL

Figura 9.2 Explicación de la aplicación de comandos SQL

La ejecución del comando SQL introducido al servidor es atómica en el sentido que el comando SQL completo tiene que tener éxito o en caso contrario el comando tendrá que deshacerse. Como respuesta al comando SQL, el servidor manda una o varios mensajes de diagnóstico informando del éxito o fallo del comando. Los errores de ejecución del comando se reflejan en el cliente como un conjunto de excepciones. Sin embargo es importante comprender que las sentencias SQL como UPDATE o DELETE tienen éxito en la ejecución incluso ante la ausencia de filas afectadas. Desde el punto de vista de la aplicación tiene que verificar los mensajes de diagnóstico enviados por el servidor para determinar el número de filas afectadas por la operación en cuestión

En el caso de una sentencia SELECT el cliente accede al resultado fila a fila. Estas filas se recuperan directamente del servidor a través dela red o de la cache del cliente

Objetivo: Plantear un proyecto que maneje transacciones concurrentes evaluando el uso de indices

Metodología

Los siguientes comandos SQL se usan psra controlar las transacciones

COMMIT: Salva los cambios

ROLLBACK: Deshacer los cambios.

SAVEPOINT: Xrear ountos de restauración.

SET TRANSACTION: Asigna un nombre a la transacción.

Comandos DML commands INSERT, UPDATE y DELETE

COMMIT almacena los cambios.

Ejemplo:

Considere las operaciones sobre las tablas cuentas y movimientos


DECLARE @importe DECIMAL(18,2),

	@CuentaOrigen CHAR(12),

	@CuentaDestino CHAR(12)

/* Asignamos el importe de la transferencia

* y las cuentasde origen y destino

*/
SET @importe = 50 
SET @CuentaOrigen  = '200700000001'
SET @CuentaDestino = '200700000002'
 

/* Descontamos el importe de la cuenta origen */

UPDATE centras 
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
 

/* Registramos el movimiento */

INSERT INTO movimientos(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM centascWHERE NUMCUENTA = @CuentaOrigen
 

/* Incrementamos el importe de la cuenta destino */

UPDATE cuenntas
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
 

/* Registramos el movimiento */
INSERT INTO movimientos(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM centas
WHERE NUMCUENTA = @CuentaDestino

Esta forma de actuar seria erronea, ya que cada instrucción se ejecutaria y confirmaría de forma independiente, por lo que un error dejaría los datos erroneos en la base de datos ( ¡y ese es el peor error que nos podemos encontrar! )

Transacciones implicitas y explicitas

Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de los siguientes métodos:

Transacciones explícitas

Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK.

Transacciones implícitas

Se inicia automátivamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.

Para activar-desactivar el modo de transacciones implicitas debemos ejecutar la siguiente instrucción.


Activamos el modo de transacciones implicitas

SET IMPLICIT_TRANSACTIONS ON

Desactivamos el modo de transacciones implicitas

SET IMPLICIT_TRANSACTIONS OFF 

Cuando la opción bANSI_DEFAULTS está establecida en ON, IMPLICIT_TRANSACTIONS también se establece en ON.

El siguiente ejemplo muestra el script anterior haciendo uso de transacciones explicitas.

DECLARE @importe DECIMAL(18,2),
	@CuentaOrigen VARCHAR(12),

	@CuentaDestino VARCHAR(12)

/* Asignamos el importe de la transferencia

* y las cuentasde origen y destino */

SET @importe = 50 

SET @CuentaOrigen = '200700000002'

SET @CuentaDestino = '200700000001'

BEGIN TRANSACTION -- O solo BEGIN TRAN

BEGIN TRY

/* Descontamos el importe de la cuenta origen */

UPDATE cuentasSET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen

/* Registramos el movimiento */

INSERT INTO movimientos(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, 
 IMPORTE, FXMOVIMIENTO)

SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM cuentasWHERE NUMCUENTA = @CuentaOrigen

 /* Incrementamos el importe de la cuenta destino */

UPDATE cuentas
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */

INSERT INTO moviientos (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,
 IMPORTE, FXMOVIMIENTO)

SELECT  IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM cuentas WHERE NUMCUENTA = @CuentaDestino

/* Confirmamos la transaccion*/ 
COMMIT TRANSACTION -- O solo COMMIT
 END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/ 
  ROLLBACK TRANSACTION -- O solo ROLLBACK
  PRINT 'Se ha producido un error!'
END CATCH

El siguiente ejemplo muestra el mismo script con transacciones implicitas.

SET IMPLICIT_TRANSACTIONS ON

DECLARE @importe DECIMAL(18,2),
	@CuentaOrigen CHAR(12),
	@CuentaDestino CHAR(12)

/* Asignamos el importe de la transferencia
* y las cuentasde origen y destino */

SET @importe = 50 
SET @CuentaOrigen = '200700000002'
SET @CuentaDestino = '200700000001'

BEGIN TRY
/* Descontamos el importe de la cuenta origen */
UPDATE cuentas
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen

/* Registrams el movimiento */
INSERT INTO moviemtos (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,
 IMPORTE, FXMOVIMIENTO)

SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM cuenta sWHERE NUMCUENTA = @CuentaOrigen

/* Incrementamos el importe de la cuenta destino */
UPDATE cuentas
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */
INSERT INTO movimientos(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,
 IMPORTE, FXMOVIMIENTO)

SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM cuentas
WHERE NUMCUENTA = @CuentaDestino

/* Confirmamos la transaccion*/ 
COMMIT TRANSACTION -- O solo COMMIT
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/ 
  ROLLBACK TRANSACTION -- O solo ROLLBACK
  RINT 'Se ha producido un error!'
END CATCH

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la siguiente vez que la conexión ejecuta una instruccion para modificar datos.

La conexión continúa generando transacciones implícitas hasta que se desactiva el modo de transacciones implícitas.

Podemos verificar el número de transacciones activas a través de @@TRANCOUNT


SET IMPLICIT_TRANSACTIONS ON
BEGIN TRY
UPDATE cuentas SET FXALTA = FXALTA - 1
  PRINT @@TRANCOUNT
  COMMIT 
END TRY
BEGIN CATCH 
  ROLLBACK
  PRINT 'Error'
END CATCH

Otro punto a tener en cuenta cuando trabajamos con transacciones son los bloqueos y el nivel de aislamiento

Transacciones anidadas.

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas.

Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharan también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT de ellas.


BEGIN TRAN
UPDATE empleados 
SET NOMBRE = 'Pedro'
WHERE ID=101 

BEGIN TRAN
  UPDATE empleados 
  SET APELLIDO1 = 'Picapiedra'
  WHERE ID=101

  -- Este COMMIT solo afecta a la segunda transaccion.
  COMMIT 
  -- Este ROLLBACK afecta a las dos transacciones.
  ROLLBACK 

Una consideración a tener en cuanta cuando trabajamos con transacciones anidadas es la posibilidad de utilizar puntos de guardado o SAVEPOINTs.

Puntos de recuperacion (SavePoint).

Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción.

El siguiente ejemplo muestra como trabajar con puntos de recuperación.

BEGIN TRAN 
  UPDATE empleados
  SET NOMBRE = 'Pablo'
  WHERE ID=101 

  UPDATE empleados 
  SET APELLIDO1 = 'Otro'
  WHERE ID=101

  SAVE TRANSACTION P1 -- Guardamos la transaccion (Savepoint)

  UPDATE empleados
  SET APELLIDO1 = 'Marmol!'
  WHERE ID=101

  -- Este ROLLBACK afecta solo a las instrucciones
  -- posteriores al savepoint P1.
  ROLLBACK TRANSACTION P1
  -- Confirmamos la transaccion

  COMMIT

Sugerencias didácticas

Los estudiantes deberían experimentar y verificar por si mismos los temas presentados en este tutorial usando SGBD reales. Para este propósito, en este tutorial se presenta un laboratorio virtual gratuito de bases de datos, junto con scripts de prueba proporcionados en la sección de referencias.

Reporte del alumno

Pruebe los ejemplos de esta práctica

Presente 9 scripts (3 por gestor) de transacciones implicitas y explicitas. Muestre sus resultados.

Descarrgar klas base de datos dolar actualizarla. Implementar el concepto de transacciones.

Preseeente su reporte en el formato acostumbrado.

Bibliografía preliminar

http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling

http://www.fdi.ucm.es/profesor/fernan/dbd/apuntestema07.pdf

http://blog.maxiaccotto.com/post/comprendiendo-el-funcionamiento-de-transacciones-en-sql-server

http://www.campusmvp.es/recursos/post/Fundamentos-de-SQL-Transacciones.aspx

http://www.hermosaprogramacion.com/2014/05/sql-transaccion-que-es/

http://www.devjoker.com/contenidos/articulos/63/Transacciones-con-PLSQL.aspx



Anterior
Valid XHTML
home

Siguiente