El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for 18 septiembre 2010

Depurar consultas Transact SQL

Posted by kiquenet en 18 septiembre 2010

Son muchas las veces que un programador necesita depurar consultas SQL (Transact SQL, T-SQL) desde Microsoft Visual Studio: mediante la inspección rápida de variables, obtenemos el string infinitamente largo que representa nuestra consulta, que habíamos tabulado con mimo en nuestro código, y que ahora aparece en una única línea. Si queremos resolver un fallo en esta consulta, o intentar optimizarla, tenemos que pegarla en el Management Studio de Microsoft SQL Server y volver a perder nuestro tiempo en tabularla para que sea más fácil entenderla.

Con este truco evitaremos malgastar nuestro precioso tiempo: Management Studio de Microsoft SQL Server formateará esta consulta de nuevo por nosotros.

Al capturar la consulta desde la inspección rápida de variables de Microsoft Visual Studio para depurarla, podríamos obtener un string como éste (no es muy largo en este caso, pero nos sirve como ejemplo):

Consulta T-SQL Original

Consulta T-SQL original en una única línea

Ahora, ejecutamos el editor de consultas de Microsoft SQL Server Mangement Studio pinchando en el icono que aparece en la siguiente imagen:

Microsoft SQL Server Mangement Studio - Diseñar consulta

Microsoft SQL Server Mangement Studio – Diseñar consulta

Aparecerá esta ventana que ignoraremos en este caso.

Microsoft SQL Server Mangement Studio - Agregar tabla

Microsoft SQL Server Mangement Studio – Agregar tabla

Pegamos la consulta que habiamos obtenido en la inspección rápida en el recuadro inferior de la pantalla (marcado en rojo en la siguiente imagen) y después quitamos el foco de ese recuadro haciendo clic en el superior, por ejemplo.

Pegamos la consulta T-SQL

Pegamos la consulta T-SQL

En este momento aparecerá en el recuadro superior una vista de diseño de todas las tablas que intervienen en nuestra consulta SQL, y la relación que existe entre ellas, y en la parte inferior podremos ver nuestra consulta SQL formateada automáticamente por Microsoft SQL Server Mangement Studio.

Consulta formateada

Consulta formateada

Ya sólo quedaría pulsar en “Aceptar” de la pantalla anterior para que la consulta nos sea devuelta y podamos ejecutarla.

Consulta formateada

Consulta formateada y lista para ejecutar

¡Y ya está! Esperamos que este pequeño truco para formatear o tabular consultas T-SQL os ayude a mejorar vuestro trabajo diario.

De Mr. Ramos

Anuncios

Posted in SQL | Etiquetado: | Leave a Comment »

Unity Application Block 1.2. Caso Práctico.

Posted by kiquenet en 18 septiembre 2010

Microsoft Unity Application Block 1.2 es un contenedor de inyección de código que soporta inyección por constructor, por propiedad (setter) y por método, ofreciendo a los desarrolladores las siguientes ventajas:

  • Provee una manera simplificada de creación de objetos, especialmente para crear objetos de una jerarquía de clases, simplificando el código de la aplicación.
  • Soporte abstracción de requerimientos, lo que permite a los desarrolladores especificar dependencias en tiempo de ejecución o mediante un fichero de configuración, simplificando el manejo del código transversal en las aplicaciones.
  • Incrementa la flexibilidad, dejando la configuración de los componentes al contenedor.
  • Dispone de capacidades para la localización de servicios. Esto permite a los clientes almacenar o cachear el contenedor. Esto es especialmente interesante en aplicaciones web ASP.NET donde los desarrolladores pueden persistir el contenedor en la memoria de sesión o de aplicación.

Unity Application Block permite a los desarrolladores realizar ingeniería del software basada en componentes. Las aplicaciones modernas están formadas por objetos de negocio particulares y por componentes que realizan tareas genéricas o específicas dentro de la aplicación. Además, están formadas por componentes individuales ejecutados de forma transversal como traceo, autenticación, autorización, cacheo y manejo de excepciones.

Una de las claves para conseguir desarrollar con éxito este tipo de aplicaciones es conseguir un diseño de código desacoplado o lo menos acoplado posible. Con un diseño y desarrollo software desacoplado conseguiremos aplicaciones más flexibles y fáciles de mantener.

Existen diferentes técnicas para permitir el diseño y desarrollo de software desacoplado. En este artículo nos centraremos en los patrones de diseño Inversión del Control (IoC – Inversion of Control) e Inyección por Dependencia (DI – Dependency Injection) pero existen otras como el patrón de diseño Intercepción (Interception pattern).

Inversion of Control (IoC) – Dependency Injection (DI)

El patrón Inversion of Control (IoC) es un patrón de diseño genérico que describe técnicas para soportar arquitecturas con la capacidad de intercambio de software (plug-in) donde los objetos pueden buscar instancias de otros objetos que éstos necesitan.

El patrón de diseño Dependency Injection (DI) es un caso especial del patrón IoC y es una técnica de programación basada en interfacesconsistente en alterar el comportamiento de la clase sin cambiar internamente ésta. El desarrollador genera código contra un interface para la clase y usa un contenedor que inyecta la instancia de la clase, basándose en el interface o el tipo del objeto. Las técnicas para inyectar instancias son: inyección por interface, inyección por constructor, inyección por propiedad (setter) e inyección por método.

Caso Práctico

Realizar con éxito un proyecto de desarrollo software es una empresa complicada. presupuestos limitados, diferentes tecnologías, resistencia al cambio, necesidades mal definidas y cambiantes, carencia de profesionales cualificados, son algunos de los problemas con los que se encuentra un equipo de ingenieros del software cuando pretenden ofrecer una solución que resuelva o al menos minimice el impacto de estos y otros problemas posibles.

Con asiduidad nos hemos enfrentado ante casos en los que se plantea una funcionalidad transversal utilizada desde diferentes puntos del sistema y que además puede cambiar y ser ampliada con frecuencia.

Ante estos casos no sólo es deseable disponer de un buen diseño orientado a objetos que nos permita poder actuar sobre la funcionalidad cambiante y poder ampliar a nuevos casos sino que también es interesante la actualización “en caliente” de estos cambios. Sería deseable que cuando nuestro sistema esté en producción, ante un cambio de funcionalidad o extensión de ésta, no tuviéramos que abrir nuestro compilador con el proyecto entero, codificar los cambios, parar el aplicativo en producción (y las personas que trabajan sobre él), hacer el cambio y volver a levantar el entorno.

Una solución deseable consistiría en implementar el cambio o la nueva funcionalidad en una DLL, implantar esta DLL en producción y configurar la creación de los objetos que disparan esa funcionalidad desde un fichero XML.

El caso que nos ocupa trata de resolver un problema (simplificado) de gestión de alertas en un proyecto software. Podríamos decir que vamos a diseñar una solución orientada a objetos que nos permita definir una alerta con la capacidad de ejecutarse en un día, hora y minuto concreto. En la solución final cada alerta tendrá una implementación concreta. Además, existirá un planificador que será el encargado de ejecutar las alertas en el momento adecuado, por ejemplo, ejecutándose desde un servicio windows.

Para la implementación de este caso práctico simplificado utilizaremos implementación de DLLs, que trata de concentrar en un fichero funcionalidades concretas, Microsoft Unity Application Block 1.2, que nos ofrece un contenedor para la inyección de código mediante constructor (parametrós con arrays), y configuración de inyección mediante fichero XML.

Ejemplo

Toda la funcionalidad la vamos a implementar en una DLL llamada Solusoft.DC. En esta DLL vamos a tener tanto la funcionalidad de las alertas como la del planificador. Realmente no debería ser así y deberían estar en dos DLLs separadas para dividir aún más el código.

A continuación se muestra una implementación de las Alertas utilizando un interfaz y una jerarquía de clases. Nos aprovecharemos del método ejecutar() para lanzar la tarea cuando se cumplan las condiciones de estar en el día, hora, minuto correctos. Nótese que los tipos devueltos por el interfaz son de tipo string por comodidad a la hora de hacer la inyección de código.

// namespace Solusoft.DC.Alertas.Interfaces
public interface IAlertable
{
       void Ejecutar();
       System.String DiaEjecucion();
       System.String HoraEjecucion();
       System.String MinutoEjecucion();
}   // namespace Solusoft.DC.Alertas
public abstract class Alerta
{
    protected System.String dia;
    protected System.String hora;
    protected System.String minuto;     public Alerta(System.String dia, System.String hora, System.String minuto)
    {
        this.dia = dia;
        this.hora = hora;
        this.minuto = minuto;
    }
}   public class AlertaX : Alerta , Interfaces.IAlertable
{
    public AlertaX(System.String dia, System.String hora, System.String minuto)
    : base(dia, hora, minuto)
    {
        ;
    }   public void Ejecutar()
    {
        Console.WriteLine("Se ha ejecutado la alerta X");
    }   public System.String DiaEjecucion()
    {
        return this.dia;
    }   public System.String HoraEjecucion()
    {
        return this.hora;
    }   public System.String MinutoEjecucion()
    {
        return this.minuto;
    }    
}   public class AlertaY : Alerta, Interfaces.IAlertable
{   public AlertaY(System.String dia, System.String hora, System.String minuto)
                           : base(dia, hora, minuto)
    {
        ;
    }   public void Ejecutar()
    {
        Console.WriteLine("Se ha ejecutado la alerta Y");
    }   public System.String DiaEjecucion()
    {
        return this.dia;
    }   public System.String HoraEjecucion()
    {
        return this.hora;
    }   public System.String MinutoEjecucion()
    {
        return this.minuto;
    }
}

El planificador es algo más sencillo pero tiene una característica importante y es la de incorporar en el constructor un array de referencias a objetos que implementen el interfaz IAlertable. Esto nos servirá para poder indicarle al contenedor cuáles son las instancias concretas de alertas que se deben planificar.

// namespace Solusoft.DC.Alertas.Interfaces
public interface IPlanificable
{
        void Ejecutar();      
}   // namespace Solusoft.DC.Alertas
public class Planificador : Interfaces.IPlanificable
{
    //Array de alertas
    private Interfaces.IAlertable [] Alertas;   public Planificador(Interfaces.IAlertable [] alertas)
    {
        this.Alertas = alertas;           
    }   public void Ejecutar()
    {
        Console.WriteLine("Se ha ejecutado el planificador");
        for (int i = 0; i < Alertas.Length; i++)
        {   // Comprobar si ejecutar
            Alertas[i].Ejecutar();
        }
     }   public void quienSoy()
     {
          Console.WriteLine("Soy el planificador");
     }
}

Con esta implementación basada en interfaces y herencia estamos en predisposición de tener un software lo más desacoplado posible. Esto unido a la utilización de Microsoft Unity Application Block 1.2 mediante configuración por diseño nos va a permitir indicar incluso en tiempo de ejecución cuáles son las intancias concretas de las alertas que el planificador debe controlar para su ejecución.

A continuación, se muestra el fichero de configuración completo para este ejemplo:

<?xml version="1.0" encoding="utf-8" />
<configuration>
  <configSections>
    <section name="unity"
      type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection,
      Microsoft.Practices.Unity.Configuration"/>
  </configSections>   <unity>
    <typeAliases>
      <!-- Ciclo de vida de los objetos -->
      <typeAlias alias="singleton"
                 type="Microsoft.Practices.Unity.ContainerControlledLifetimeManager,
                       Microsoft.Practices.Unity" />   <typeAlias alias="external"
                 type="Microsoft.Practices.Unity.ExternallyControlledLifetimeManager,
                 Microsoft.Practices.Unity" />   <!-- Alias para los tipos definidos del usuario -->
      <!-- IPlanificable - Planificador -->
      <typeAlias alias="IPlanificable" type="Solusoft.DC.Alertas.Interfaces.IPlanificable, Solusoft.DC"/>
      <typeAlias alias="IAlertable" type="Solusoft.DC.Alertas.Interfaces.IAlertable, Solusoft.DC"/>
      <!-- Array de IAlertable -->
      <typeAlias alias="IArrayAlertable" type="Solusoft.DC.Alertas.Interfaces.IAlertable[], Solusoft.DC"/> 
      <!-- Clase abstracta para alertas -->
      <typeAlias alias="Alerta" type="Solusoft.DC.Alertas.Alerta, Solusoft.DC" />
    </typeAliases>   <containers>
      <container>
        <types>
<!-- Mapeamos para la interfaz IAlertable - AlertaX-->
<type type="IAlertable"
  mapTo="Solusoft.DC.Alertas.AlertaX, Solusoft.DC"
  name="AlertaX">
<!-- Inyección por constructor -->
<typeConfig
extensionType="Microsoft.Practices.Unity.Configuration.TypeInjectionElement,
                               Microsoft.Practices.Unity.Configuration">
<constructor>
<param name="dia" parameterType="System.String">
<value value="1"/>
</param>
<param name="hora" parameterType="System.String">
<value value="1"/>
</param>
<param name="minuto" parameterType="System.String">
<value value="1"/>
</param>
</constructor>
</typeConfig>
</type>   <!-- Mapeamos para la interfaz IAlertable - AlertaY-->
<type type="IAlertable"
  mapTo="Solusoft.DC.Alertas.AlertaY, Solusoft.DC"
  name="AlertaY">
<!-- Inyección por constructor -->
<typeConfig
extensionType="Microsoft.Practices.Unity.Configuration.TypeInjectionElement,
                               Microsoft.Practices.Unity.Configuration">
<constructor>
<param name="dia" parameterType="System.String">
<value value="2"/>
</param>
<param name="hora" parameterType="System.String">
<value value="2"/>
</param>
<param name="minuto" parameterType="System.String">
<value value="2"/>
</param>
</constructor>
</typeConfig>
</type>   <!-- Mapeamos a objeto de clase Planificador -->
<type type="IPlanificable"
  mapTo="Solusoft.DC.Alertas.Planificador, Solusoft.DC"
  name="Planificador">
<!-- Constructor array injection -->
<typeConfig>
<constructor>
<param name="alertas" parameterType="IArrayAlertable">
<array>
<dependency name="AlertaX"/>
<dependency name="AlertaY"/>
</array>
</param>
</constructor>
</typeConfig>
<lifetime type="singleton" />
</type>   <!-- Mapeamos para la clase concreta de Alerta-->
          <type type="Alerta"
                mapTo="Solusoft.DC.Alertas.AlertaX, Solusoft.DC">
            <!-- Inyección por constructor --> 
            <typeConfig
                extensionType="Microsoft.Practices.Unity.Configuration.TypeInjectionElement,
                               Microsoft.Practices.Unity.Configuration">
              <constructor>
  <param name="dia" parameterType="System.String">
  <value value="10"/>
  </param>
  <param name="hora" parameterType="System.String">
  <value value="8"/>
  </param>
  <param name="minuto" parameterType="System.String">
  <value value="2008"/>
  </param>    
              </constructor>
            </typeConfig>
          </type>
        </types>
      </container>
    </containers>
  </unity>   <appSettings>
<add key="ObjetoPlanificador" value="Planificador"/>
  </appSettings>
</configuration>

La configuración de la sección Unity para este ejemplo incorpora los siguientes elementos:

  • Alias para tipos: Indicando alguno de los posibles ciclos de vida de los objetos propuestos por Microsoft (singleton y external) además de los tipos propios del aplicativo, IPlanificable, IAlertable, IArrayAlertable, Alerta. Estos alias son importantes porque hacen más fáciles de utilizar los tipos completos, nótese que hay que utilizar namespace más nombre de DLL. También se indica el tipo de array que recibirá el constructor del objeto planificador.
  • Dentro del contenedor definimos el mapeo contra la clase concreta que será utilizada para instanciar un objeto, dándole un nombre, por ejemplo, para el tipo IAlertable se podrán servir una instancia de la clase Solusoft.DC.Alertas.AlertaX si se le pide el objeto al contenedor por el nombre “AlertaX” o un objeto de la clase Solusoft.DC.Alertas.AlertaY si se le pide el objeto al contenedor por el nombre “AlertaY”. En estos casos se ve como además lleva inyección de código por parámetros de construcción, indicando para cada alerta (X o Y) los valores exactos para dia, hora, minuto.
  • El caso más interesante es el del planificador que resuelve las peticiones al tipo IPlanificable como una instancia de la clase Solusoft.DC.Alertas.Planificador (fíjese cómo es aquí donde podría cambiar el planificador de nuestro sistema si quisiéramos), permitiendo inyección por constructor con un parámetro que es un array, indicando las instancias concretas de alertas que se le pasaran por el constructor, en este caso mediante los nombres AlertaX, AlertaY.

Una vez que tenemos la estructura de clases/interfaces y el contenedor configurado tan solo nos queda saber utilizarlo desde el código de nuestra aplicación. El ejemplo es el siguiente:

IUnityContainer container = new UnityContainer();
var section =
           (UnityConfigurationSection)ConfigurationManager.GetSection("unity");
           section.Containers.Default.Configure(container);   // Alerta alerta = container.Resolve&lt;Alerta&gt;();   IAlertable alertaX = container.Resolve&lt;IAlertable&gt;("AlertaX");
Console.WriteLine(alertaX.MinutoEjecucion());   IAlertable alertaY = container.Resolve&lt;IAlertable&gt;("AlertaY");
Console.WriteLine(alertaY.MinutoEjecucion());   string objetoPlanificador = ConfigurationSettings.AppSettings[CFG_PLANIFICADOR] as string;   IPlanificable planificador = container.Resolve&lt;IPlanificable&gt;(objetoPlanificador);   planificador.Ejecutar();   Console.ReadLine();

La utilización del contedor una vez configurado es muy sencilla. En primer lugar hay que decirle a nuestro aplicativo que se prepare para utilizar un contenedor y que lo haga cargando la configuración desde la sección Unity. En este ejemplo, el fichero de configuración es el App.Config de una aplicación en modo consola pero podría ser cualquier otro (consultar documentación para cargar la configuración de otro archivo).

Mediante llamadas container.Resolve conseguimos pedir al contenedor los objetos que hayan sido configurados previamente en diseño. Con esto hacemos la llamada a: IPlanificable planificador = container.Resolve_IPlanificable_(objetoPlanificador); para pedirle el objeto concreto que esté capacitado para planificar. Nótese como en este caso ya estará configurado con las alertas precisas a ejecutar y una llamada simple a planificador.Ejecutar(); permitirá su ejecución.

Conclusiones

En este artículo hemos visto alguna de las características de Microsoft Unity Application Block, concretamente aquellas que nos permiten resolver instancias de objetos del contenedor por medio de configuración en ficheros XML. Sólo con esto nuestras arquitecturas de software pueden ser desacopladas y muy versátiles.

Para más información puede consultar Microsoft Developer Network


Del Mister Profesor Ruiz

Posted in .NET, Arquitectura | Etiquetado: , | Leave a Comment »

Transacciones: Interbloqueos en SQL Server

Posted by kiquenet en 18 septiembre 2010

Por si fuera poco lo que hemos visto hasta ahora, aún tenemos pendiente revisar la gestión de interbloqueos que realiza SQL Server. Este punto es de vital importancia en nuestras aplicaciones que acceden concurrentemente a los datos en nuestra base de datos.

Un interbloqueo es una situación en la que dos transacciones están esperando para utilizar un recurso bloqueado.

SQL Server detecta automáticamente una situación de interbloqueo mediante un método denominado detección de cadenas circulares de bloqueos. Cuando un interbloqueo ocurre, SQL Server termina el proceso que haya generado la cadena circular de bloqueos.

Los interbloqueos no se producen únicamente a nivel de página de datos, de hecho, muchos interbloqueos se producen a nivel de página de índice.

Minimización de la contienda de bloqueo

Para minimizar el número de interbloqueos se deben considerar las siguientes recomendaciones:

  • Las transacciones deben ser cortas y afectar al menor número de datos posibles.
  • Se debe dar preferencia a la utilización de alguna de las técnicas del enfoque optimista.
  • Puede resultar conveniente establecer una prioridad baja (“set deadlockpriority low”) para las consultas DSS y una alta (“set deadlockpriority normal”) para las consultas OLTP.
  • El orden de acceso a las tablas debe ser siempre el mismo en las distintas partes del código.
  • Se debe minimizar el uso de “holdlock”. Por ejemplo, el código:
declare @SEQNO int   begin transaction
   SELECT @SEQNO = isnull(SEQ#,0)+1
   FROM SEQUENCE WITH holdlock
   /*
      En ausencia de holdlock el bloqueo compartido sería liberado
      por lo que si otra transacción concurrente ejecutara el mismo
      comando obtendría el mismo número de secuencia
   */
   UPDATE SEQUENCE
   SET SEQ# = @SEQNO
   /*
      Ahora puede hacerse lo que se desee con este número de
      secuencia
   */
   …
commit transaction
podría reescribirse como:
declare @SEQNO int   begin transaction
   UPDATE SEQUENCE
   SET @SEQNO = isnull(SEQ#,0)+1
   FROM SEQUENCE
   SELECT @SEQNO
   …
commit transaction

De esta forma se obtiene directamente el bloqueo de actualización eliminándose la posibilidad de que se produzca un interbloqueo.

  • SQL Server proporciona un soporte completo para establecer la granularidad del bloqueo a nivel de fila. Una granuralidad del bloqueo más fina proporciona una mejor concurrencia, minimizando el número de interbloqueos, aunque aumenta el trabajo del servidor para gestionar los bloqueos.
  • Se debe evitar las interactuaciones con el usuario dentro de una transacción con el fin de minimizar el tiempo de duración de la misma.
  • Se debe seleccionar cuidadosamente el nivel de aislamiento de una transacción, ya que el nivel de aislamiento determina el tipo de bloqueo. Es posible reducir el número de interbloqueos bajando el nivel de aislamiento.

Gestión de interbloqueos

SQL Server devuelve el error 1205 al cliente cuando termina un proceso como resultado de un interbloqueo.


Del Sr. Profesor Alcalde

Posted in .NET, DataAccess | Etiquetado: | Leave a Comment »

Transacciones: Enfoques de control de transacciones en SQL Server

Posted by kiquenet en 18 septiembre 2010

Siempre con el objetivo de maximizar el rendimiento de nuestras aplicaciones multiusuario que trabajan accediendo a bases de datos, hay una cosa más que debemos tener en cuenta: los enfoques de control de transacciones.

Al implementar aplicaciones multiusuario que trabajan contra bases de datos se pueden adoptar dos enfoques para el control de transacciones: un enfoque de bloqueo optimista y un enfoque de bloqueo pesimista.

El bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. En lugar de hacer esto, se confía en que el gestor de bases de datos (Microsoft SQL Server, por ejemplo) se encargue de hacerlo mientras el programador únicamente se debe centrar en la lógica de la aplicación.

El bloqueo pesimista supone que el código de la aplicación intentará imponer algún tipo de mecanismo de bloqueo al gestor de bases de datos.

Cada uno de estos enfoques presentan una serie de ventajas e inconvenientes que iremos analizando y además presentaremos algunas técnicas para implementarlos.

Enfoque optimista de gestión de transacciones en SQL Server

La eficiencia de una aplicación multiusuario (usuarios concurrentes de acceso a bases de datos, en nuestro caso) que trabaje contra SQL Server se ve condicionada por ciertos factores entre los que se encuentran el enfoque de control de transacciones que adoptemos.

Como se introducía en el post anterior de esta saga “Enfoques de control de transacciones en SQL Server“,existen dos enfoques básicos: enfoque optimista y enfoque pesimista. Del enfoque que seleccionemos y de lo hábiles que seamos observando ciertas sencillas reglas en cada uno de ellos dependerá la eficiencia de nuestra aplicación.

Un bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. En lugar de hacer esto, se confía en que el gestor de bases de datos (Microsoft SQL Server, por ejemplo) se encargue de hacerlo mientras el programador únicamente se debe centrar en la lógica de la aplicación.

Una vez hemos decidido que vamos a dejar que SQL Server gestione las transacciones por nosotros, y pese a que hemos descargado sobre el gestor de bases de datos en gran medida la responsabilidad de hacer que nuestra aplicación sea eficiente, aún hay ciertas cosas que como buenos programadores debemos tener en cuenta.

Para implementar un bloqueo optimista en una aplicación sin que ésta se detenga bruscamente cuando exista un número excesivo de bloqueos en el servidor es preciso observar ciertas reglas sencillas:

  • Se debe minimizar el tiempo de duración de una transacción.
  • El código de la aplicación debe asegurar que las actualizaciones se realicen sobre un registro concreto, en lugar de mantener el bloqueo mientras el usuario está examinado los datos.
  • Asegurar que todos los códigos de la aplicación actualizan y seleccionan tablas en el mismo orden. Esto evitará que se produzcan los bloqueos permanentes.

Bloqueo optimista utilizando “timestamp”

SQL Server proporciona un tipo especial de datos denominado “timestamp”. El valor de una columna de este tipo es generado automáticamente cada vez que se almacena una fila con “insert” o “update”. Es simplemente un contador que cambia de manera monótona con cada actualización o inserción.
El propósito, para nuestro caso en particular, del tipo de datos “timestamp” consiste en servir como número de versión para los esquemas de bloqueo optimista.
Para poder emplear un bloqueo optimista basado en este tipo de datos, deben cumplirse las siguientes dos condiciones:

  • La tabla debe tener una clave primaria.
  • La tabla debe tener una columna de tipo “timestamp”.

El cliente lee la fila con el valor actual de la columna de marca temporal, pero no mantiene ningún bloqueo. En algún momento posterior, cuando el cliente quiere actualizar la fila, debe asegurarse de que ningún otro cliente haya cambiado la misma fila mientras tanto (puesto que no hay bloqueos, es responsabilidad del cliente el asegurarse de que los cambios hechos por otros clientes sean preservados). El cliente prepara de una forma especial el “update”, utilizando una columna “timestamp” como marcador de versión:

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      timestamp = NUM_VERSION_OLD

Puesto que la cláusula “where” incluye la clave primaria, sólo se verá afectada una fila como máximo. Si algún otro cliente ha cambiado la fila, la segunda parte del “where” fallará, dándose como resultado que el “update” ha actualizado cero filas, como indicación de fallo de bloqueo. El cliente puede entonces elegir entre volver a leer los datos o efectuar cualquier otro procedimiento de recuperación que se considere oportuno.

Existen varias ineficiencias asociadas a esta forma de trabajo que se expondrán en el siguiente apartado. Por ello, si se va a utilizar un enfoque optimista basado en una marca de tiempo, se recomienda que dicha marca esté basada en un tipo de datos “int”.

Bloqueo optimista utilizando “int”

La única diferencia con respecto al método anterior es que, puesto que el servidor no va a actualizar de forma automática el valor de esta columna, será el cliente el encargado de hacerlo.

La sentencia “update” tendrá la forma:

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR,
    NUM_VERSION = NUM_VERSION_OLD+1
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      NUM_VERSION = NUM_VERSION_OLD

Las diferencias con respecto al método anterior son las siguientes:

  • El tipo de datos “int” ocupa sólo cuatro bytes mientras que el tipo de datos “timestamp” ocupa ocho.
  • Al utilizar el tipo de datos “int” el cliente debe añadir la actualización de la marca en las sentencias “insert” y “update”, además de incluir el tratamiento en la cláusula “where”.
  • Si se usa el tipo de datos “timestamp”, después de hacer un “update” será necesario que el cliente realice un nuevo “select” para obtener la nueva marca de tiempo, si es que quiere continuar trabajando con los mismos datos.
UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
     timestamp = NUM_VERSION_OLD   /*
Si se quiere seguir trabajando con la misma fila, será necesario
conocer el nuevo valor de la columna timestamp ya que ha
sido asignado automáticamente por el servidor
*/   SELECT timestamp
FROM TABLA
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA

En el caso de usar una marca basada en un tipo de datos “int”, el cliente sabe después del “update” el nuevo valor de la marca, por lo que no tendrá que realizar una consulta para seguir trabajando con los mismos datos.

UPDATE TABLA
SET COLUMNA_CAMBIADA = NUEVO_VALOR,
    NUM_VERSION = NUM_VERSION_OLD+1
WHERE COL_CLAVE_PRIMARIA = CLAVE_PRIMARIA AND
      NUM_VERSION = NUM_VERSION_OLD   /*
Aquí el cliente sabe cuál es el nuevo valor de la columna
NUM_VERSION, que es el que tenía almacenado + 1
*/

En la mayoría de los casos, la elección de este enfoque de control de transacciones es acertada. Sin embargo, existen ciertas situaciones donde el número de usuarios que acceden concurrentemente a la base de datos es elevado, las transacciones son largas y un largo etcétera en los que será conveniente evaluar la idoneidad de apostar por un enfoque pesimista de gestión de transacciones.

Enfoque pesimista de gestión de transacciones en SQL Server

Diseñar nuestra aplicación, que va a soportar el acceso concurrente a datos, basándonos en un enfoque optimista de gestión de transacciones implica suponer que SQL Server va a administrar a la perfección las contiendas de bloqueos que se produzcan cuando nuestra aplicación esté en marcha.

Por un lado, resulta poco realista suponer que un programador o un usuario no pueda hacer nada que provoque explícitamente un bloqueo y, por el otro, tenemos aplicaciones cuyas exigencias nos obligan a determinar de forma explícita con qué lógica deben ser manejadas las acciones que realizamos sobre los datos… realmente existen muchas situaciones que dan lugar a que se produzcan un gran número de bloqueos en el servidor, lo que puede llegar a inutilizar nuestra aplicación si no ponemos cuidado a la hora de diseñar el acceso a la base de datos.

Un enfoque pesimista de gestión de transacciones consiste en diseñar de qué forma vamos a condicionar a nuestro sistema gestor de bases de datos SQL Server para que gestione la competición por el acceso a datos o contienda de bloqueo.

En SQL Server tenemos dos posibilidades, no excluyentes, de tomar el control, o al menos de condicionar el comportamiento, sobre cómo se va a manejar la contienda de bloqueo:

  • utilizando los niveles de aislamiento de transacciones que implementa SQL Server
  • utilizando sugerencias para nivel de aislamiento y granularidad de bloqueo

Utilización de los niveles de aislamiento de las transacciones

Los niveles de aislamiento determinan el grado en que los datos incoherentes de una transacción están protegidos frente a otras transacciones. En teoría, toda transacción debe estar completamente aislada de otras transacciones, pero en la realidad, por razones prácticas, esto puede no ser cierto siempre. El uso de un nivel u otro estará condicionado por la naturaleza de los procesos de tratamiento de datos de cada aplicación en particular.

Para establecer el grado de aislamiento de las transacciones durante toda una conexión, en SQL Server se utiliza la sentencia “set transaction isolation level”.

En un entorno multiusuario, pueden darse cuatro situaciones: actualización perdida, lecturas sucias, lecturas no repetibles, lecturas fantasma.

  • Actualización perdida: No proporciona ningún aislamiento. La última transacción en modificar el conjunto de datos prevalecerá y los cambios realizados por otras transacciones se perderán.
  • Lecturas sucias: Una transacción puede leer datos que están siendo modificados por otras transacciones. Estos datos son incoherentes porque las otras transacciones pueden decidir cancelar los cambios.
  • Lecturas no repetibles: En esta situación una transacción lee los mismos datos dos veces, pero entre una lectura y otra, otra transacción ha modificado los datos. Así, los datos obtenidos en la primera lectura serán distintos a los obtenidos en la segunda.
  • Lecturas fantasma: En esta situación, la transacción 1 lee los datos en un rango de claves del 1 al 8, existiendo las filas 1, 3 y 5. Una segunda transacción inserta en la misma tabla una fila con la clave 2. Cuando la transacción 1 realice una nueva consulta sobre dicho rango de claves se encontrará con una fila que antes no existía.

Idealmente, un SGBD debe proporcionar niveles de aislamiento para evitar todas estas situaciones. A veces, por razones de rendimiento y siempre condicionado por la naturaleza de los procesos que manejan la información, pueden permitirse algunas de estas situaciones.

ANSI define los siguientes cuatro estándares para el aislamiento de transacciones: lectura de datos no confirmados (N0), lectura de datos confirmados (N1), lectura repetible (N2) y serializable (N4). SQL Server proporciona soporte para todos estos niveles.

Lectura de datos no confirmados

Si se selecciona el modo de lecturas no confirmadas no se proporciona ningún aislamiento a las transacciones.

Una transacción que se ejecute con este nivel de aislamiento no será inmune a las lecturas sucias, lecturas no repetibles ni lecturas fantasma.

La instrucción T-SQL que selecciona este nivel de aislamiento es:

SET transaction isolation level READ uncommitted
Lectura de datos confirmados

Es el comportamiento predeterminado de SQL Server.

En este modo no se ejecutan inicios de transacciones implícitas. Durante la ejecución de una sentencia se establece un bloqueo de actualización sobre el recurso a modificar. En cuanto termine esta sentencia, se liberará el bloqueo.

En este nivel de aislamiento se protege a la transacción contra lecturas sucias, pero las transacciones no son inmunes a la pérdida de actualizaciones, las lecturas no repetibles ni a las lecturas fantasma.

SET transaction isolation level READ committed
Lectura repetible

SQL Server da inicio a una transacción de manera implícita al ejecutar una sentencia de modificación de datos, por lo que será necesaria una sentencia “commit” o “rollback” para terminarla. Este modo permite a las transacciones establecer bloqueos compartidos y exclusivos sobre los recursos a modificar.

Bajo este nivel de aislamiento, las transacciones están protegidas contra pérdida de atualizaciones, las lectruras sucias y las lecturas no repetibles. Sin embargo, dichas transacciones sí pueden sufrir problemas de lecturas fantasma.

SET transaction isolation level repeatable READ
Serializable. Serialización y bloqueo de rango de clave

Este modo es el más estricto, ofreciéndose aislamiento completo a las transacciones.

SQL Server da comienzo de manera implícita a una transacción, utilizando un bloqueo de rango de clave sobre las páginas índice, para asegurar aislamiento frente lecturas fantasma.

Bajo este nivel de aislamiento las transacciones utilizan bloqueos compartidos, exclusivos y de rango de clave para asegurar el aislamiento total frente a otras transacciones.

Si la operación es una búsqueda de rango, se establece un bloqueo de intención compartido sobre el rango de claves especificado en la cláusula “where”. Otras transacciones podrán leer ese rango, pero no podrán insertar ni borrar registros dentro del mismo.

Si la operación es de búsqueda o borrado de una fila no existente, SQL Server establece un bloqueo de intención compartido sobre la página de índice para el rango de claves comprendido en la sentencia de la operación.

Si la operación es de inserción, SQL Server establece un bloqueo de intención de inserción sobre la página de índice, sólo para la clave que está siendo insertada. Lo mismo ocurre si la operación es de borrado.

Dado que pueden llegar a bloquearse un elevado número de recursos, pueden existir problemas de baja concurrencia en las aplicaciones que establezcan este nivel de aislamiento.

SET transaction isolation level serializable

Utilización de sugerencias para niveles de aislamiento y granularidad de bloqueos

Como se ha comentado en el apartado anterior, se puede establecer el nivel de aislamiento de las transacciones que se ejecuten en una conexión con la sentencia “set transaction isolation level”.

Esta sentencia T-SQL crea un nivel de global de aislamiento para la conexión, lo que proporciona un nivel de aislamiento homogéneo para la aplicación. Sin embargo, a veces es recomendable utilizar distintos niveles de aislamiento dependiendo de la naturaleza del proceso de tratamiento de los datos en cuestión, etc.

SQL Server permite especificar sugerencias de tablas en las cláusulas “select”, “delete”, “update” e “insert”, lo que permite sustituir el nivel de aislamiento definido para la conexión. Esta sustitución del nivel de aislamiento estará vigente mientras dure la transacción.

Aunque suele ser buena idea que sea el optimizador el que tome las decisiones de qué granularidad de bloqueo y nivel de aislamiento se debe utilizar, a veces resulta útil sugerir al gestor a utilizar una granularidad y niveles de aislamiento determinados.

Sugerencias de nivel de aislamiento de una transacción
  • “holdlock”: Dentro de una transacción, los bloqueos compartidos (los que se establecen al seleccionar una fila, por ejemplo) se liberan en cuanto la sentencia que mantiene el bloqueo se termina. Para mantener el bloqueo compartido durante toda la transacción se utiliza la cláusula “holdlock”. Esta cláusula es útil, por ejemplo, para actualizar el valor de un contador no “identity”.
    declare @SEQNO int
    begin transaction
    SELECT @SEQNO = isnull(SEQ#,0)+1
    FROM SEQUENCE WITH holdlock
    /*
    En ausencia de holdlock el bloqueo compartido sería liberado
    por lo que si otra transacción concurrente ejecutara la
    misma sentencia obtendría el mismo número de secuencia
    */
    UPDATE SEQUENCE
    SET SEQ# = @SEQNO
    /*
    Ahora puede hacerse lo que se desee con este número
    de secuencia
    */
    …
    commit transaction

    Hay que resaltar una cuestión que puede llevar a engaño. La ejecución del conjunto de sentencias anterior, impide que dos procesos ejecutándose concurrentemente acaben obteniendo el mismo número de secuencia. Sin embargo, no evita que se produzca una situación de interbloqueo con la consecuente terminación del proceso que lo ha producido. Esta situación se daría en el caso de que los dos procesos ejecutaran el “select” y luego ambos quisieran ejecutar el “update”. Sólo uno de ellos lo conseguiría y SQL Server terminaría el proceso que ha provocado el interbloqueo. Reescribiendo el código de la siguiente manera se evitaría esta situación:

    declare @SEQNO int
    begin transaction
    UPDATE SEQUENCE
    SET @SEQNO = isnull(SEQ#,0)+1
    FROM SEQUENCE
    SELECT @SEQNO
    …
    commit transaction

    De esta forma se obtiene el bloqueo de actualización directamente y se evita la posibilidad de interbloqueo.

  • “nolock”: Puede usarse esta sugerencia para especificar que no se establezca ningún bloqueo compartido sobre el recurso y que se denieguen las solicitudes de bloqueos de actualización o exclusivos. Esta opción permitirá las lecturas sucias a otras transacciones. Es útil en los entornos de generación de informes donde los datos aproximados resulten aceptables.
  • “readcommitted”: Es equivalente a utilizar “set transaction isolation level” salvo, por supuesto, que sólo tiene efecto hasta que termine la transacción.
  • “readpast”: Esta sugerencia se aplica sólo a las sentencias “select” y permite que la consulta se salte filas sobre las que existan bloqueos. Dadas las restricciones a las que está sometida el uso de esta sugerencia, es prácticamente imposible implementarla en entornos OLTP.
  • “readuncommitted”: Equivalente a especificarlo con “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción, y es igual a la sugerencia “nolock”.
  • “repeatableread”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta el fin de la transacción.
  • “serializable”: Equivalente a usar “set transaction isolation level”, salvo que sólo tiene efecto hasta que termine la transacción.
Sugerencias de granularidad de bloqueo

Quiero remarcar que no uso la palabra “sugerencia” por gusto: en SQL Server se definen así, como sugerencias, y es que el optimizador de consultas del gestor de base de datos a tenderá a nuestras sugerencias siempre y cuando no considere que perjudican al rendimiento del servidor.

  • “rowlock”: Fuerza al gestor de bloqueos a establecer un bloqueo a nivel de fila.
  • “paglock”: Fuerza al gestor a establecer un bloqueo a nivel de página.
  • “tablock”: Se usa para establecer un bloqueo a nivel de tabla.
  • “tablockx”: Especifica un bloqueo exclusivo sobre una tabla. Sólo es necesario si se pretenden realizar operaciones de mantenimiento de la tabla.
  • “updlock”: Esta opción es similar a “holdlock”. La diferencia es que “holdlock” establece un bloqueo compartido y “updlock” establece un bloqueo de actualización sobre los recursos mientras dure la transacción.

Enfoque mixto de gestión de transacciones en SQL Server

Cuando los requisitos de eficiencia de una aplicación en la que se produce acceso concurrente a datos sobre SQL Server son muy estrictos, no podemos basar el diseño de nuestra aplicación ni en un enfoque optimista de gestión de transacciones ni en un enfoque pesimista de gestión de transacciones ya que corremos el riesgo de no satisfacer dichos requisitos.

Además, aunque en apariencia estos dos enfoques de control de transacciones expuestos con anterioridad cubren las necesidades de cualquier aplicación, esto no es así dado que las técnicas empleadas para implementar uno y otro enfoque presentan ciertas carencias que obligan a diseñar otras formas de trabajo.

En cuanto al enfoque optimista, esas carencias se resumen en el siguiente punto:

  • La carencia principal reside en el hecho de que puede darse que dos usuarios seleccionen los mismos datos para su posterior modificación. Ambos usuarios podrán realizar todas las modificaciones que quieran, pero sólo uno de ellos conseguirá completar la operación con éxito, dado que el mecanismo implementado por estos enfoques optimistas impedirá que se pierdan modificaciones. Si el mantenimiento afectaba a un elevado volumen da datos, el usuario que no consiga completar la actualización de los datos habrá perdido mucho tiempo de trabajo.
  • Existe la necesidad de implementar la gestión de tratamiento de carreras de actualización de datos.

Las técnicas de enfoque optimista funcionan bien si el número de tablas a manejar es pequeño. Sin embargo, esta técnica complica el desarrollo de aquellos mantenimientos que manejen un elevado número de tablas. En estos casos debe estudiarse la posibilidad de utilizar un enfoque pesimista del control de transacciones, siempre y cuando esta decisión no genere nuevos problemas.

En cuanto al enfoque pesimista, las carencias se resumen en los siguientes puntos:

  • La elección en cada caso del nivel de aislamiento de una transacción o una conexión y de la granularidad del bloqueo es una tarea extremadamente complicada dado el elevado número de factores y combinaciones que pueden darse. Una elección no adecuada puede hacer que la eficiencia del servidor, y por tanto de la aplicación, se vea seriamente afectada.
  • Existe la necesidad de implementar el tratamiento de interbloqueos.
  • Por otro lado, existe el mismo problema que había con el enfoque optimista referente a la actualización simultánea de los mismos datos por procesos distintos, con la particularidad de que ahora el tratamiento de interbloqueos será diferente.
  • Finalmente, existe un problema relacionado con el carácter indefinido que tienen los bloqueos sobre los datos. Con un enfoque pesimista podría darse la situación de que un usuario bloqueara los datos para su posterior modificación y que, en el caso extremo, nunca liberara los recursos bloqueados. Esta situación que parece imposible es realmente usual.

Los siguientes esquemas de trabajo propuestos, basados en que cada una de las copias de nuestra aplicación respete una serie de reglas a la hora de acceder a los datos, tratan de cubrir estas carencias. Cabe resaltar que no son excluyentes con respecto a los métodos expuestos anteriormente, necesitándose en algunos casos dichos métodos para asegurar el correcto funcionamiento y mejorar la eficiencia de estos últimos.

Los dos métodos están basados en añadir una columna a las tablas que indique si la fila en cuestión está bloqueada o no. La elección de qué tablas son las que deben incluir esta columna depende de cada modelo de datos y de las tablas que se vean involucradas en cada mantenimiento.

Bloqueo pesimista utilizando “flag + plan de contingencia”

En las tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “IT_EN_USO”. En una fila que haya sido seleccionada por un proceso de modificación, el valor de dicha columna será 1, y valdrá 0 en cualquier otro caso.

Un proceso que quiera modificar los datos de una fila, sólo podrá hacerlo si “IT_EN_USO” vale 0. Para ocupar el recurso, pondrá el valor 1 en dicha columna.

Si un mismo proceso necesita modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde.

Tras finalizar la modificación de los datos, el proceso que los ha modificado será el encargado de liberar los recursos poniendo el valor 0 en la columna “IT_EN_USO” en aquellas filas que corresponda.

Dentro de la utilización de este enfoque, pueden darse mantenimientos que necesiten la interacción con el usuario (para la recogida de los nuevos datos) y mantenimientos que no la necesiten (actualizaciones automáticas, por ejemplo).

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursos (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque optimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursos (Cod) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, IT_EN_USO '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      if FieldByName('IT_EN_USO').AsString = '1' then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set IT_EN_USO = 1 '+
                 'where CD = '+Cod+' and '+
                       'IT_EN_USO = 0';      
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosLibres:=false;
      end;
      Next;
   end;
   OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure LiberarTodosRecursos (Cod);
 * Cometido..: Libera todos los recursos que se ocuparon para la
 *             modificación.
 * ----------------------------------------------------------------
 *)
procedure LiberarTodosRecursos (Cod);
begin
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof do begin
      strSQL:='update TABLA '+
              'set IT_EN_USO = 0 '+
              'where CD = '+Cod;
      EjecutarSQL(strSQL);
      Next;
   end;
end; // LiberarTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure VolcarDatosABD (Cod, DatosModif);
 * Cometido..: Vuelca los datos modificados por el usuario a la BD.
 * ----------------------------------------------------------------
 *)
procedure VolcarDatosABD (Cod, DatosModif);
begin
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof do begin
      strSQL:='update TABLA '+
              'set CAMPOS_MODIF = '+DatosModif+' '+
              'where CD = '+Cod;
      EjecutarSQL(strSQL);
      Next;
   end;
end; // VolcarDatosABD   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      CommitTransaction;
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         StartTransaction;
         VolcarDatosABD(Cod,DatosModif);
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
      if DatosModificados = CANCEL then begin
         StartTransaction;
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
   end
   else begin
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   end;
end; // Principal

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán constantemente en este estado, ya que se cerró la transacción en la que se ocuparon. En estos casos, poco habituales por otro lado, será necesario aplicar el plan de contingencia.

El esquema del plan de contingencia sería:

(*
 * ----------------------------------------------------------------
 * procedure PlanContingencia;
 * Cometido..: Liberar todos los recursos de la BD.
 * ----------------------------------------------------------------
 *)
procedure PlanContingencia;
begin
   StartTransaction;
   for TodosLosRecursosControlados do
      LiberarTodosRecursos(Cod);
   CommitTransaction;
end; // PlanContingencia

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod,DatosModif) : boolean;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      VolcarDatosABD(Cod,DatosModif);
      LiberarTodosRecursos(Cod);
      CommitTransaction;
      ModificarDatos:=true;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=false;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   if ModificarDatos(Cod,DatosModif) then
      Mensaje('Datos modificados')
   else
      MostrarError('Imposible modificar datos');
end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma rutina y en la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. Este tipo de mantenimientos no provoca problemas que hagan necesaria la ejecución del plan de contingencia, ya que todas las modificaciones sobre la base de datos se realizan dentro de la misma transacción.

Los procesos que sólo visualicen datos sin llegar a modificarlos, no tendrán en cuenta el valor de la columna IT_EN_USO, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo evita que dos procesos seleccionen a la vez los mismos datos para su posterior modificación, por lo que ningún usuario perderá tiempo modificando datos que finalmente no pueda salvar. Sin embargo, no impide que un usuario bloquee indefinidamente datos.

La pega de este esquema reside en el plan de contingencia. Para poder aplicar el proceso que liberará todas las filas bloqueadas permanentemente por un fallo en el suministro eléctrico, etc., será necesario que no haya ninguna aplicación activa excepto la que está ejecutando el administrador. Es, por tanto, aplicable en aplicaciones con pocos usuarios y en oficinas en las que se pueda controlar la actividad de todos los operarios a la vez para evitar situaciones de pérdida de datos e inconsistencias que podrían llegar a derivarse de la aplicación del plan de contingencia estando alguna aplicación activa.

Una forma de evitar el plan de contingencia manteniendo este esquema de trabajo es bajar el nivel de aislamiento de las transacciones para poder leer datos de los que todavía no se ha hecho el commit (permitiendo lecturas sucias). En este caso, no se cerraría la transacción tras ocupar los recursos, sólo se cerraría tras liberarlos, por lo que si algo falla entre el proceso de ocupar los recursos y después liberarlos, sería el propio SQL Server el encargado de deshacer los cambios, dejando de nuevo los recursos libres.

El flag “IT_EN_USO” asegura que dos usuarios no modifiquen a la vez los mismos datos. Bajar el nivel de aislamiento permite una mayor concurrencia en el acceso a los datos.

El esquema de trabajo para mantenimientos que necesiten la intervención del usuario será en este caso:

(*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   EjecutarSQL('set transaction isolation level readuncommitted');
   StartTransaction;
   if OcuparTodosRecursos(Cod) then begin
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         VolcarDatosABD(Cod,DatosModif);
         LiberarTodosRecursos(Cod);
         CommitTransaction;
      end;
      if DatosModificados = CANCEL then begin
         RollBackTransaction;
      end;
   end
   else
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   EjecutarSQL('set transaction isolation level readcommitted');
end; // Principal

El esquema de trabajo para mantenimientos que no necesiten la intervención del usuario no variará con respecto al esquema que no baja el nivel de aislamiento excepto en el hecho de que no necesita el plan de contingencia.

En las consultas que trabajen sobre las tablas manejadas con estos esquemas de bloqueo también se debe bajar el nivel de aislamiento ya que, al no cerrar la transacción hasta el final, SQL Server mantendrá bloqueos sobre dichas tablas.

Esta opción puede emplearse en contados casos ya que bajar el nivel de aislamiento de las transacciones puede ocasionar graves problemas. Un caso en el que sería medianamente fácil usar esta optimización sería en aquellos mantenimientos que sólo afectan a datos de una tabla. Si el mantenimiento afecta a tablas de tipo maestro – detalle, la programación se complica ya que las consultas que se realicen podrán leer datos sucios, en los que todavía no existan todos los detalles de un maestro, etc., pudiendo ocasionar resultados no deseados.

Supongamos ahora que mezclamos este esquema de trabajo con un enfoque pesimista. La función “OcuparTodosRecursos” podría haber sido escrita:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosPesimista (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque pesimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosPesimista (Cod) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, IT_EN_USO '+
           'from TABLA with (repeatableread) '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      if FieldByName('IT_EN_USO').AsString = '1' then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set IT_EN_USO = 1 '+
                 'where CD = '+Cod;
         EjecutarSQL(strSQL);
      end;
      next;
   end;
   OcuparTodosRecursosPesimista:=TodosLibres;
end; // OcuparTodosRecursosPesimista

Podría ocurrir que dos usuarios intentaran modificar a la vez los mismos datos. En este caso, podría producirse un interbloqueo. SQL Server mataría a la transacción que ha provocado la cadena circular de bloqueo y sería necesario un tratamiento de errores especial. Con la primera de las versiones para ocupar recursos, no se producirá nunca un interbloqueo, descargando así al servidor de la tarea de tener que resolver este tipo de conflictos y evitando tener que programar un tratamiento de errores adicional en la aplicación.

Existe aún otra manera de codificar el procedimiento “OcuparTodosRecursos”.

Esta forma evita el interbloqueo, ya que lo primero que se hace es el update, con lo que se obtienen directamente los bloqueos de escritura. Además, como consecuencia de hacer un único update no se sobrecarga la red, aunque sí el servidor, por lo que es adecuada para arquitecturas en la que la red es lenta y existe un servidor potente, frente a la solución presentada en la primera solución que sería más adecuada si el servidor se encuentra ya sobrecargado.

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosEficiente (Cod) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 *             Enfoque optimista.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosEficiente (Cod) : boolean;
begin
   strSQL:='update TABLA '+
           'set IT_EN_USO = 1 '+
           'where IT_EN_USO = 0 and '+
                 'CD = '+Cod;
   NumRegsAfectadosUpdate:=EjecutarSQL(strSQL);
   strSQL:='select count(*) as NumRegsSe '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
   if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
      OcuparTodosRecursosEficiente:=true
   else
      OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente

Esta forma de ocupar los recursos, con un servidor poco cargado, es mucho más eficiente siendo este aumento de eficiencia más patente cuanto más recursos sea necesario ocupar.

Esta forma de codificación es válida también para la operación de liberar recursos.

Aunque esta codificación es más eficiente, en los ejemplos de código se seguirá manteniendo la codificación más natural del mismo para que resulte más clara su comprensión. Sin embargo, hay que tener en cuenta siempre en cuenta que esta forma de trabajo es más eficiente y ocasiona una menor carga en el servidor por resolución de contiendas de bloqueo.

El hecho de que la ocupación y liberación de recursos no sea transaccional y se necesite un plan de contingencia para los casos de bloqueo permanente hace de esta solución una primera aproximación de la solución que se expone a continuación.

Bloqueo pesimista utilizando “caducidad” de bloqueo

Partiendo del bloqueo optimista utilizando un flag para bloquear los recursos y basándose en la filosofía de trabajo de los enfoques optimistas simples, se puede plantear otra solución que tiene ciertas características que la hacen interesante.

En este caso, en aquellas tablas en las que se quiera controlar el acceso concurrente se introducirá una columna “FC_OCUPACION” de tipo datetime que, en el caso de SQL Server, ofrece una precisión de 1 milisegundo.

Se establece un tiempo de caducidad, que indicará el tiempo máximo que un proceso de modificación puede tener ocupado un recurso.

Un proceso que quiera ocupar recursos, sólo podrá hacerlo si el campo “FC_OCUPACION” del recurso a ocupar contiene una fecha caducada, es decir, si la fecha actual del servidor menos el valor de “FC_OCUPACION” supera el tiempo de caducidad. Para ocupar un recurso, el proceso introducirá la fecha actual del servidor en el campo “FC_OCUPACION” del recurso a ocupar. Esta será su marca de ocupación.

Si un mismo proceso quiere modificar varias filas, de la misma tabla o de varias, deberá obtener todos los recursos a modificar o no ocupar ninguno. En caso de no poder ocupar todos los recursos deberá liberar aquellos que ya haya ocupado y se mostrará un mensaje al usuario que le invite a intentarlo más tarde. Todos los recursos que tenga que ocupar el proceso se han de ocupar con la misma marca de ocupación.

Un proceso que haya conseguido completar con éxito todo el proceso de actualización de los datos será el encargado de liberar los mismos para que otros procesos puedan ocuparlos. Para liberar un recurso, ha de introducirse una fecha caducada en el campo “FC_OCUPACION” de cada recurso que se ha de liberar.

Llegado este punto, hay que hacer una puntualización: Una fecha no caducada indica que el recurso en cuestión está en uso. Una fecha caducada indica que el recurso no está en uso, o que el proceso que lo estaba usando ha excedido el tiempo que se le ha dado para modificar el recurso que ocupó y que, por tanto, se ha liberado el recurso (pero no lo ha liberado el proceso que lo ocupó) o, mejor dicho, que el recurso se puede ocupar.

De esta forma, un proceso que haya ocupado todos los recursos que necesite, a la hora de volcar la actualización a la base de datos, debe comprobar que la marca de ocupación sigue siendo la misma con la que ocupó los recursos. Si la marca sigue siendo la suya, podrá volcar la información, incluso aunque la marca haya caducado ya que si sigue siendo la suya significa que no hay otro proceso que haya ocupado los recursos. Por el contrario, si la marca es distinta significa que el tiempo de modificación fue excedido y que otro proceso a ocupado el recurso. En este caso, el proceso al que le ha caducado la marca no puede realizar la modificación. Este proceso debe liberar los recursos que sigan teniendo su marca de ocupación y mostrar un mensaje al usuario que le indique que no se puede realizar la actualización de los datos porque se excedió el tiempo de modificación.

Para mantenimientos en los que es necesaria la intervención del usuario, el esquema de trabajo es el siguiente:

const
   MARCA_NO_OCUPADO = '01/01/1900 00:00:00.000';
   T_CADUCIDAD = XX; // Tiempo de caducidad
(*
 * ----------------------------------------------------------------
 * function ObtenerMarcaTemp : DateTime;
 * Cometido..: Obtiene la fecha del servidor para establecer la
 *             marca de ocupación.
 * ----------------------------------------------------------------
 *)
function ObtenerMarcaTemp : DateTime;
begin
   strSQL:='select getdate() as MarcaTemp';
   EjecutarSQL(strSQL);
   ObtenerMarcaTemp:=FieldByName('MarcaTemp').AsDateTime;
end; // ObtenerMarcaTemp   (*
 * ----------------------------------------------------------------
 * function MarcaCaducada (MarcaOld, MarcaTemp): boolean;
 * Cometido..: Comprueba si la marca de modificación ha caducado
 *             o no.
 * ----------------------------------------------------------------
 *)
function MarcaCaducada : boolean;
begin
   if (MarcaTemp – MarcaOld) &gt; T_CADUCIDAD then
      MarcaCaducada:=true
   else
      MarcaCaducada:=false;
end; // MarcaCaducada   (*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones.
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursos (Cod, MarcaTemp) : boolean;
begin
   TodosLibres:=true;
   strSQL:='select CD, FC_OCUPACION '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosLibres do begin
      MarcaOld:= FieldByName('FC_OCUPACION').AsString;
      if not MarcaCaducada(MarcaOld,MarcaTemp) then
         TodosLibres:=false
      else begin
         strSQL:='update TABLA '+
                 'set FC_OCUPACION = '+MarcaTemp+' '+
                 'where CD = '+Cod+' and '+
                       'FC_OCUPACION = 'MarcaOld+;      
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosLibres:=false;
      end;
      Next;
   end;
   OcuparTodosRecursos:=TodosLibres;
end; // OcuparTodosRecursos   (*
 * ----------------------------------------------------------------
 * procedure LiberarTodosRecursos (Cod, MarcaTemp);
 * Cometido..: Libera todos los recursos que se ocuparon para la
 *             modificación.
 * ----------------------------------------------------------------
 *)
procedure LiberarTodosRecursos (Cod, MarcaTemp);
begin
   TodosOcupados:=true;
   strSQL:='select CD '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosOcupados do begin
      strSQL:='update TABLA '+
              'set FC_OCUPACION = '+MARCA_NO_OCUPADO+' '+
              'where CD = '+Cod+' and '
                    'FC_OCUPACION = '+MarcaTemp;
      NumRegsAfectados:=EjecutarSQL(strSQL);
      if NumRegsAfectados &lt; 1 then
         TodosOcupados:=false;
      Next;
   end;
end; // LiberarTodosRecursos   (*
 * ----------------------------------------------------------------
 * function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
 * Cometido..: Vuelca los datos modificados por el usuario a la BD.
 * ----------------------------------------------------------------
 *)
function VolcarDatosABD (Cod, MarcaTemp, DatosModif) : boolean;
begin
   TodosOcupados:=true;
   strSQL:='select CD, FC_OCUPACION '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   while not eof and TodosOcupados do begin
      if MarcaCaducada(
            FieldByName('FC_OCUPACION').AsDateTime,MarcaTemp) then
         TodosOcupados:=false;
      if TodosOcupados then begin
         strSQL:='update TABLA '+
                 'set CAMPOS_MODIF = '+DatosModif+' '+
                 'where CD = '+Cod+' and '
                       'FC_OCUPACION = 'MarcaTemp;
         NumRegsAfectados:=EjecutarSQL(strSQL);
         if NumRegsAfectados &lt; 1 then
            TodosOcupados:=false;
      end;
      Next;
   end;
   VolcarDatosABD:=TodosOcupados;
end; // VolcarDatosABD   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   StartTransaction;
   MarcaTempOc:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
      CommitTransaction;
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         StartTransaction;
         if VolcarDatosABD(Cod,MarcaTempOc,DatosModif) then begin
            LiberarTodosRecursos(Cod,MarcaTempOc);
            CommitTransaction;
         else begin
            RollBackTransaction;
            MostrarError('Los datos en memoria han caducado.'+
                         'Para modificar estos datos vuelva'+
                         ' a filtrar');
         end;
      end;
      if DatosModificados = CANCEL then begin
         MarcaTempLib:=ObtenerMarcaTemp;
         if not MarcaCaducada(MarcaTempOc,MarcaTempLib) then begin
            StartTransaction;
            LiberarTodosRecursos(Cod,MarcaTempOc);
            CommitTransaction;
         end;
      end;
   end
   else begin
      RollBackTransaction;
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
   end;
end; // Principal

En este caso, por poner un ejemplo, la ocupación eficiente de los recursos tendría la forma:

(*
 * ----------------------------------------------------------------
 * function OcuparTodosRecursosEficiente (Cod,MarcaTemp) : boolean;
 * Cometido..: Ocupa todos los recursos que se requieren para
 *             realizar las posteriores modificaciones con el
 *             esquema de trabajo más eficiente en servidores poco
 *             cargados.     
 * ----------------------------------------------------------------
 *)
function OcuparTodosRecursosEficiente (Cod, MarcaTemp) : boolean;
begin
   strSQL:='update TABLA '+
           'set FC_OCUPACION = '+MarcaTemp+' '+
           'where '+MarcaTemp+'-FC_OCUPACION &gt; '+T_CADUCIDAD+
                  ' and '+
                  'CD = '+Cod;
   NumRegsAfectadosUpdate:=Ejecutar(strSQL);
   strSQL:='select count(*) as NumRegsSe '+
           'from TABLA '+
           'where CD = '+Cod;
   EjecutarSQL(strSQL);
   NumRegsAfectadosSelect:=FieldByName('NumRegsSe').AsInteger;
   if NumRegsAfectadosUpdate = NumRegsAfectadosSelect then
      OcuparTodosRecursosEficiente:=true
   else
      OcuparTodosRecursosEficiente:=false;
end; // OcuparTodosRecursosEficiente

Como puede apreciarse, en el proceso existen dos transacciones. En la primera de ellas se ocupan todos los recursos que sean objeto de la modificación. Una vez ocupados todos los recursos objeto de modificación, se cierra la primera transacción. De esta forma se liberan los bloqueos establecidos por SQL Server y se potencia así la concurrencia en el acceso a los datos.

Cuando se han recogido todos los datos que el usuario quiere modificar, si éste acepta los cambios, se inicia una nueva transacción en la que, tras volcar la nueva información a la base de datos, se liberan todos los recursos que se habían ocupado. Si el usuario ha cancelado los cambios, se inicia una nueva transacción con el fin de liberar todos los recursos ocupados. Después se cierra la transacción.

Si entre la primera y la segunda transacción se produce un corte en el suministro eléctrico, el ordenador se bloquea, etc., los recursos ocupados permanecerán en este estado, hasta que su marca de ocupación caduque, por lo que en este caso no es necesario un plan de contingencia que libere los recursos, aunque es cierto que puede implementarse esta opción por si se necesitara la liberación inmediata de los recursos.

Por otro lado, en aquellos mantenimientos en los que no se requiere interactuación con el usuario, el esquema de trabajo varía en algunos aspectos. El pseudocódigo en este caso sería:

(*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod,DatosModif) : boolean;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod,DatosModif) : boolean;
begin
   StartTransaction;
   MarcaTemp:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTemp) then begin
      VolcarDatosABD(Cod,MarcaTemp,DatosModif);
      LiberarTodosRecursos(Cod,MarcaTemp);
      CommitTransaction;
      ModificarDatos:=true;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=false;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   if ModificarDatos(Cod,DatosModif) then
      Mensaje('Datos modificados')
   else
      MostrarError('Imposible modificar datos');
end; // Principal

Como puede apreciarse, en este caso, la ocupación y liberación de los recursos se produce dentro de la misma transacción, dado que ya se tienen los nuevos datos a introducir en la base de datos. En este tipo de mantenimientos, el proceso que ocupa los registros siempre podrá terminar con éxito el proceso, ya que SQL Server mantiene los bloqueos que estableció sobre las filas cuando el proceso de modificación ocupó los recursos. Los procesos de este tipo deben tener la transacción abierta el menor tiempo posible para evitar reducir el nivel de concurrencia de acceso a los datos.

Los procesos que sólo visualicen datos sin llegar a modificarlos no tendrán en cuenta el valor de la columna “FC_OCUPACION”, no estando para estos procesos bloqueada la fila en cuestión.

Esta forma de trabajo impide que un usuario bloquee indefinidamente un recurso, sin embargo, puede ocasionar pérdida de tiempo de trabajo si tras introducir un gran volumen de datos no puede llevarse a cabo la modificación porque la ocupación haya caducado y otro proceso haya ocupado alguno de los recursos que necesitaba el primer proceso. Si la elección del tiempo de caducidad se ha hecho adecuadamente, esta situación no debería darse nunca, a no ser que un usuario la forzara de forma intencionada.

“Caducidad” de bloqueo sin pérdidas de actualización

En procesos muy delicados realizados por usuarios altamente cualificados que no provoquen un bloqueo innecesario de recursos y en los que exista la necesidad de interactuación con el usuario, existen dos esquemas que evitan que se pierdan datos de actualización.

El primero de estos esquemas consiste en mezclar la forma de trabajo en mantenimientos que necesitan interactuación con el usuario con la de los que no necesitan dicha interactuación, de forma que las ocupaciones no caduquen. En este caso el pseudocódigo sería:

const
   DATOS_MODIF    = 1;
   DATOS_CANCEL   = 2;
   DATOS_OCUPADOS = 3;   (*
 * ----------------------------------------------------------------
 * function ModificarDatos (Cod) : integer;
 * Cometido..: Modificación de datos de la BD.
 * ----------------------------------------------------------------
 *)
function ModificarDatos (Cod) : integer;
begin
   StartTransaction;
   MarcaTempOc:=ObtenerMarcaTemp;
   if OcuparTodosRecursos(Cod,MarcaTempOc) then begin
      CargarDatosModificables(Cod);
      DatosModificados:=RecogerDatosAModificar(DatosModif);
      if DatosModificados = OK then begin
         VolcarDatosABD(Cod,MarcaTempOc,DatosModif);
         LiberarTodosRecursos(Cod,MarcaTempOc);
         CommitTransaction;
         ModificarDatos:=DATOS_MODIF;
      end;
      if DatosModificados = CANCEL then begin
         RollBackTransaction;
         ModificarDatos:=DATOS_CANCEL;
      end;
   end
   else begin
      RollBackTransaction;
      ModificarDatos:=DATOS_OCUPADOS;
   end;
end; // ModificarDatos   (*
 * ----------------------------------------------------------------
 * rutina Principal;
 * Cometido..: Esqueleto de la aplicación que maneje esta forma de
 *             trabajo.
 * ----------------------------------------------------------------
 *)
rutina Principal;
begin
   Resultado:=ModificarDatos(Cod);
   if Resultado = DATOS_MODIF then
      Mensaje('Datos modificados');
   if Resultado = DATOS_CANCEL then
      Mensaje('Modificación cancelada');
   if Resultado = DATOS_OCUPADOS then
      MostrarError('Los datos están siendo modificados'+
                   ' por otro usuario.'+#13+
                   'Por favor, inténtelo más tarde.');
end; // Principal

Como puede apreciarse, todo el proceso se realiza dentro de la misma transacción por lo que SQL Server mantiene los bloqueos establecidos sobre los recursos que bloqueó el proceso hasta el final de la misma. De esta forma, ningún otro proceso podrá bloquear los recursos, aunque la marca haya caducado. De hecho, otro proceso que intentara bloquear los recursos mediante el enfoque optimista se quedaría bloqueado hasta que se terminara la transacción.

Este esquema de trabajo sólo debe implementarse en aquellos procesos extremadamente delicados y cortos en duración, realizados por personal altamente cualificado que no provoque bloqueos innecesarios de los recursos dado que, al mantener los bloqueos establecidos por SQL Server hasta el final de la transacción, ningún otro proceso podrá bloquear dichos recursos hasta la finalización de la misma, llegando incluso a quedarse bloqueados hasta ese momento, reduciéndose de esta forma el nivel de concurrencia de acceso a los datos.

El segundo de estos esquemas presenta la ventaja de que no provocará que ningún otro proceso quede bloqueado por SQL Server, aunque representa una mayor carga para el gestor de bases de datos.

Consiste en asociar un temporizador al formulario en el que se están recogiendo los datos a actualizar de forma que, cuando se dispare éste, se actualice el campo en el que se almacena la caducidad del bloqueo, antes de que se produzca dicha caducidad. De esta forma, otros procesos podrán visualizar datos sin llegar a bloquearse y se asegura que el proceso que ocupó los recursos finalice exitosamente la actualización de los datos.

Del Sr. Alcalde

Posted in .NET, DataAccess | Etiquetado: | 1 Comment »

Transacciones: Gestión de bloqueos en SQL Server

Posted by kiquenet en 18 septiembre 2010

Se ha comentado el funcionamiento de las transacciones en este gestor de base de datos (SGBD). Sin embargo, para acabar de comprender su funcionamiento debemos conocer cómo se manejan los bloqueos en SQL Server. El correcto funcionamiento y la eficiencia de nuestra aplicación que maneja usuarios concurrentes accediendo a nuestra base de datos dependen de la configuración de SQL Server y de la programación de la aplicación, así que, tanto para una cosa como para la otra, debemos comprender y manejar estos conceptos.

Bloqueos y rendimiento

Los bloqueos son los encargados de hacer que las transacciones tengan la propiedad de aislamiento. La realización de bloqueos en un entorno concurrente proporciona aislamiento en los siguientes casos:

  • Múltiples transacciones intentan modificar la misma fila.
  • Datos no confirmados por una transacción son leídos por otras transacciones. Esto se conoce como lecturas sucias.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a valores diferentes con el mismo conjunto de datos. Conocido como lecturas no repetibles.
  • Los datos leídos con la misma cláusula “select” durante varias etapas de la transacción dan lugar a diferentes conjuntos de datos. Es lo que se conoce como “lecturas fantasma”.

El establecimiento de los bloqueos es automático en SQL Server, no teniéndose que preocupar, por tanto, el programador directamente por ellos, aunque debe tener en cuenta qué tipo de bloqueos establece cada operación atendiendo a las cláusulas incluidas, etc., para minimizar los interbloqueos.

Recursos de los bloqueos. Granularidad del bloqueo

El establecimiento de bloqueos es automático en SQL Server. El gestor de bloqueos es el encargado de decidir el tipo y la granularidad del bloqueo.

Los bloqueos se pueden producir tanto a nivel de página de datos como a nivel de página de índice. El gestor de bloqueo puede decidir bloquear los recursos: tabla, extensión, página, fila y rango de clave.

La granularidad del bloqueo incide de forma directa sobre la concurrencia y el rendimiento del sistema. El optimizador de consultas decide la granularidad del bloqueo automáticamente. No obstante, SQL Server proporciona extensiones de T-SQL que proporcionan un mecanismo para controlar la granuralidad de los bloqueos.

Tipos de bloqueo en SQL Server

El procedimiento almacenado “sp_lock” proporciona los bloqueos activos en el sistema gestor de bases de datos. También ofrecen información sobre bloqueos el administrador corporativo, el monitor de rendimiento, el administrador de memoria y el objeto de SQL Server bloqueos.

Dependiendo del tipo de transacción, el gestor de bloqueos establece distintos tipos de bloqueos.

Bloqueos compartidos

Se establecen para operaciones de sólo lectura, por ejemplo “select”.

Varias transacciones pueden mantener varios bloqueos compartidos simultáneamente sobre el mismo recurso ya que dichas transacciones no interfieren entre sí.

Si una transacción intenta modificar un dato de un recurso sobre el que se ha establecido un bloqueo compartido, se bloqueará hasta que todos los bloqueos compartidos se liberen.

Un bloqueo compartido se libera en cuanto se finaliza la operación de lectura de datos, es decir, no permanece a lo largo de la transacción. Existen cláusulas de las que se hablará más adelante para modificar este comportamiento.

Bloqueos de actualización

Los bloqueos de actualización se usan para instrucciones que modifican datos.

Cuando una transacción intenta actualizar una fila primero ha de leerla para asegurarse de que la fila en cuestión es realmente la que se quiere modificar. En este momento establece un bloqueo compartido. Una vez se ha asegurado que es la fila correcta procederá a modificarla, para lo que necesita establecer un bloqueo exclusivo que será liberado al terminar la transacción. Los bloqueos de actualización se emplean como bloqueo intermedio entre los dos anteriores para evitar interbloqueos.

Bloqueos exclusivos

Se concede un bloqueo de este tipo a una transacción cuando ésta está lista para modificar los datos. Un bloqueo exclusivo sobre un recurso asegura que ninguna otra transacción pueda interferir las acciones llevadas a cabo por la primera transacción sobre los recursos bloqueados.

SQL Server libera el bloqueo al finalizar la transacción.

Bloqueos de intención

El bloqueo de intención es un mecanismo que utilizan las transacciones para declarar su intención de obtener un bloqueo compartido, de actualización o exclusivo sobre un recurso.

No establece ningún bloqueo en si, excepto por el hecho de que otra transacción no podrá adquirir un bloqueo de actualización sobre un recurso sobre el que se haya establecido un bloqueo de intención.

Bloqueos de esquema

Se utilizan para mantener la integridad estructural de las tablas SQL Server. A diferencia de otros bloqueos, que proporcionan aislamiento para los datos, los bloqueos de esquema proporcionan aislamiento para el esquema de objetos de la base de datos.

Compatibilidad de bloqueos

En la siguiente lista se muestra la compatibilidad entre los distintos tipos de bloqueo. Para cada bloqueo que puede llegar a estar activo sobre una conexión al sistema gestor de bases de datos SQL Server se lista qué tipos de bloqueo pueden solicitarse concurrentemente (por ejemplo, si existe un bloqueo de modificación de esquema no puede solicitarse ningún otro tipo de bloqueo, tal y como muestra el último elemento de esta lista).

  • De intención compartido: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo y de estabilidad de esquema.
  • Compartido: de intención compartido, compartido, de actualización y de estabilidad de esquema.
  • De actualización: de intención compartido, compartido y de estabilidad de esquema.
  • De intención exclusivo: de intención compartido, de intención exclusivo y de estabilidad de esquema.
  • Compartido de actualización exclusivo: de intención compartido y de estabilidad de esquema.
  • Exclusivo: estabilidad de esquema.
  • Estabilidad de esquema: de intención compartido, compartido, de actualización, de intención exclusivo, compartido de intención exclusivo, exclusivo y de estabilidad de esquema.
  • Modificación de esquema: ningún bloqueo es compatible con el bloque de modificación de esquema.
Bloqueos de índices

De forma similar a los bloqueos de páginas de datos, SQL Server gestiona los bloqueos de las páginas de índices internamente.

Con el comando “sp_indexoption” se puede modificar el gestor permitiendo o no bloqueos a nivel de filas.

Se recomienda no realizar modificaciones sobre el comportamiento por defecto del gestor de bloqueos en lo referente a las páginas de índices ya que suele hacer buenas elecciones.

Notas:

Todas las técnicas comentadas tratan en todo momento de mantener la integridad de la base de datos si no, solucionar el problema de mejorar el acceso concurrente nos crearía un problema más grave aún.

A esto que acabo de decir, tengo que añadir dos matices:
El primero de ellos es que siempre hay que tener presente que las cosas que hagas se pueden hacer bien o mal. Por ejemplo, cambiar los niveles de aislamiento de las transacciones puede provocar el colapso de la base de datos, que tus aplicaciones empiecen a mostrar datos sobre los que no se ha hecho commit (que puede ser un problema o no)… En fin, que hay que saber lo que se está haciendo.
El segundo es, en parte, continuación de lo anterior, y es que cada solución, como todo en este mundo tiene sus cosas buenas y sus cosas malas por lo que se adaptan mejor o peor a un tipo de problema determinado, dependiendo del coste de implementación, de la forma en que resuelven el problema de bloqueo, etc. Por poner un ejemplo, la solución más “sencillita” que sería utilizar un flag con un plan de contingencia, puede dejar registros bloqueados en el caso que comentas, por eso es necesario un plan de contingencia que consiste en una aplicación auxiliar que libere esos registros en caso de que se vaya la luz, etc… Sin embargo, implementarla es muy sencillo y no hay que controlar ningún otro efecto colateral… El bloqueo pesimista utilizando caducidad del bloqueo es, en general, el que mejor se ajusta a todo tipo de problemas pero requiere más implementación…

Como comentaba, todo depende del problema en cuestión.

Del Mister Alcalde

Posted in .NET, DataAccess | Etiquetado: , , | Leave a Comment »

Transacciones: Gestión de transacciones en SQL Server

Posted by kiquenet en 18 septiembre 2010

Conocer la gestión de transacciones que realiza SQL Server, qué son y cómo funcionan, es la clave para desarrollar aplicaciones eficaces y eficientes que se conectan a un gestor de bases de datos (SGBD) para consultar y modificar datos, con usuarios accediendo concurrentemente para realizar acciones de distinta naturaleza sobre los mismos (no es lo mismo consultar información que actualizarla) y teniendo que gestionar acciones de actualización que afectan a varios datos a la vez.

Procesamiento de transacciones

Una transacción es una unidad lógica de trabajo que tiene ciertas propiedades conocidas como propiedades ACID: atomicidad, coherencia, aislamiento y permanencia.

Es responsabilidad del sistema gestor de bases de datos el asegurar que se cumplan estas propiedades. Sin embargo, el programador dispone de un cierto grado de control sobre la forma en que SQL Server gestiona algunas de estas propiedades.

Existen tres formas de llevar a cabo este procesamiento: autoconfirmación, explícita e implícita (que no voy a abordar en este post pero que os animo a comentar si lo creéis interesante).

Transacciones y bloqueos

SQL Server realiza y mantiene una serie de bloqueos durante la transacción. Existen dos formas de modificar el tratamiento de los bloqueos.

La primera de ellas consiste en introducir cláusulas en las sentencias “select”. Estas cláusulas modificarán el tratamiento de los bloqueos de las unidades afectadas (filas, tablas, páginas; la granularidad también es seleccionable y se tratará más adelante) por la sentencia y se mantendrán hasta la finalización de la transacción.

La segunda, se mantiene durante toda la sesión. Para modificar la forma en que una sentencia “select” gestiona una transacción y sus bloqueos se utiliza la sentencia “set transaction isolation level” que tiene las siguientes opciones o niveles de aislamiento, mutuamente excluyentes:

  • “read committed”: Es el comportamiento predeterminado. La transacción mantiene bloqueos compartidos durante el tiempo que dure la lectura. La transacción puede encontrarse con lecturas no repetibles o datos fantasma dado que el bloqueo no impide a otras transacciones modificar los datos que se han leído.
  • “read uncommitted”: Con este nivel de aislamiento una transacción puede leer datos sucios de otras transacciones. Es el nivel de aislamiento menos restrictivo. Esta opción tiene el mismo efecto que usar la opción “nolock” en una sentencia “select”.
  • “repeatable read”: A medida que se leen datos se establecen bloqueos de forma que otra transacción no pueda modificar datos leídos. Este nivel de aislamiento es más restrictivo y puede hacer que otras transacciones se bloqueen. No evita que se puedan añadir nuevas filas.
  • “snapshot”: Con este nivel de aislamiento las consultas de la transacción en curso sólo leerán los datos confirmados antes del comienzo de la misma. Dado que la transacción en curso trabaja con una instantánea de los datos, capturada en el momento de inicio de la transacción, las lecturas de datos de esta transacción no solicitan bloqueos y otras transacciones que realicen operaciones de escritura no bloquearán a ésta.
  • “serializable”: Nivel de aislamiento más restrictivo ya que aplica un bloqueo al rango de datos leídos. Proporciona, por tanto, protección ante lectura de filas fantasma.
Supervisión de transacciones

La supervisión de transacciones se apoya en el registro de transacciones. Para llevar la cuenta del número de transacciones abiertas, SQL Server utiliza una variable global de sesión denominada “@@trancount”.

Codificación de transacciones eficientes

Las transacciones tienen un efecto negativo en la concurrencia de acceso a datos. Para reducir la contienda de bloqueo sobre los recursos, las transacciones deben ser tan cortas y eficientes como sea posible. A continuación se enumeran algunas reglas que se deben observar al programar transacciones:

  • No devolver conjuntos de resultados dentro de una transacción. Dicha acción prolonga la transacción innecesariamente. Se debe realizar la recuperación y análisis de los datos fuera de la transacción.
  • Se debe evitar, en la medida de lo posible, pedir al usuario una respuesta cuando se haya iniciado una transacción. En caso de error se debe cerrar primero la transacción y después mostrar el mensaje de error.
  • La transacción debe ser lo más corta posible. Se debe abrir justo en el momento en el que se deseen realizar las modificaciones y cerrarla justo después de haberlas realizado.
  • Iniciar y terminar transacciones es un proceso que necesita una gran cantidad de trabajo por parte del servidor, por ello se deben abrir transacciones únicamente cuando sea necesario.

Conexiones acopladas

Como se ha comentado anteriormente, las transacciones tienen un efecto negativo en la concurrencia al acceso de datos.

Si es necesario implementar una transacción de larga duración manteniendo, por tanto, bloqueos exclusivos, y que continúe siendo posible algún tipo de procesamiento operativo, las conexiones acopladas o enlazadas pueden ser una solución. Existen dos tipos de conexiones acopladas: locales y distribuidas:

Las conexiones acopladas locales son conexiones dentro de un único servidor que comparten un espacio de transacciones.

Las conexiones acopladas distribuidas hacen uso del coordinador de transacciones distribuidas para que se comparta el mismo espacio de transacciones entre conexiones procedentes de más de un servidor.

Las conexiones enlazadas son útiles al desarrollar procedimientos almacenados extendidos que deben ejecutar instrucciones de Transact-SQL en nombre del proceso que las llama. Pasar el proceso que realiza la llamada en un testigo enlazado como un parámetro del procedimiento almacenado extendido permite al procedimiento combinar el espacio de transacciones del proceso que realiza la llamada y, por ello, integrar el procedimiento almacenado extendido con el proceso que realiza la llamada.

Se puede utilizar conexiones enlazadas para desarrollar aplicaciones en tres estratos en las que la lógica comercial se representa en programas independientes que funcionan en colaboración en una sola transacción comercial.

El ejemplo siguiente de conexiones enlazadas ilustra cómo pueden tener acceso dos conexiones a la misma transacción: Un cliente decide adquirir un producto a unos grandes almacenes locales. El vendedor obtiene acceso al sistema de transacciones de ventas que inserta una fila en la tabla de transacciones de ventas e incluye un número de autorización de tarjeta de crédito. Se realizan dos conexiones con el mismo servidor, la conexión C1 y la conexión C2. C1 comienza una transacción que agrega una fila de venta de productos a la tabla de ventas. Se debe agregar un número de autorización de tarjeta de crédito a la nueva fila de transacciones de ventas. Durante el proceso de autorización de la tarjeta de crédito, el procedimiento almacenado extendido crea la conexión C2 para conectar mediante la línea telefónica con la compañía de tarjetas de crédito y modifica la fila de transacciones de ventas con el número de autorización de la tarjeta de crédito. Solamente mediante la utilización de conexiones enlazadas, ambas conexiones pueden tener acceso a la misma fila sin que se produzcan conflictos de bloqueo.

Existe una importante desventaja de las transacciones acopladas: el procesamiento secuenciado. Sólo una de entre todas las conexiones acopladas puede estar realizando una operación. Cualquier intento de efectuar una tarea mientras que la anterior operación está teniendo lugar dará como resultado un error, que debe detectarse para su posterior tratamiento.

Transacciones distribuidas

Dada la creciente utilización de los sistemas distribuidos, existe la necesidad de acceder y modificar datos distribuidos.

Es necesaria una forma de asegurar que la transacción distribuida opere de la misma forma que lo haría una transacción local y que respete las propiedades ACID de cualquier transacción.

SQL Server proporciona las sentencias “begin distributed transaction” para comenzar una transacción distribuida.

El manejo de estas transacciones se realiza mediante el protocolo de confirmación en dos fases.

Del Profesor Señor Alcalde

Posted in .NET, DataAccess | Etiquetado: | 1 Comment »

El maravilloso mundo de las Transacciones II

Posted by kiquenet en 18 septiembre 2010

En el analizador de consultas de SQL SERVER:

begin tran

select * from CORC_TEMP_COMPROMISOS where it_accion = ‘M’
select @@trancount

select * from CORC_TEMP_COMPROMISOS where it_accion = ‘A’
select @@trancount

Insert into CORC_COMPROMISOS (cd_linea_financiera, cd_asociacion,
cd_accion, nm_grupos, nm_participantes)
values ( ‘5’,’0001′,33,33,33)
— falla por triggers , es indiferente para el commit/rollbak de la transacción

select @@trancount — aqui vale 0 !!!!!!!!!! DEBERIA VALER 1 !!!!!
commit tran

Es responsabilidad del programador de Transact-SQL utilizar COMMIT
TRANSACTION sólo en el punto donde todos los datos a los que hace referencia la transacción sean lógicamente correctos.

Si la transacción que se ha confirmado era una transacción Transact-SQL
distribuida, COMMIT TRANSACTION hace que MS DTC utilice el protocolo de confirmación en dos fases para enviar confirmaciones a los servidores
involucrados en la transacción. Si una transacción local afecta a dos o más
bases de datos del mismo servidor, SQL Server utiliza una confirmación
interna en dos fases para confirmar todas las bases de datos involucradas en la transacción.

Cuando se utiliza en transacciones anidadas, las confirmaciones de las transacciones anidadas no liberan recursos ni hacen permanentes sus modificaciones.

Las modificaciones sobre los datos sólo quedan permanentes y se liberan los recursos cuando se confirma la transacción más externa.

Cada COMMIT TRANSACTION que se ejecute cuando @@TRANCOUNT sea mayor que 1 sólo reduce @@TRANCOUNT en 1.

Cuando @@TRANCOUNT llega a 0, se confirma la transacción externa entera. Como SQL Server omite transaction_name, la ejecución de una instrucción COMMIT TRANSACTION que haga referencia al nombre de una transacción externa cuando haya transacciones anidadas pendientes sólo reduce @@TRANCOUNT en 1.

La ejecución de COMMIT TRANSACTION cuando @@TRANCOUNT es 0 produce un error que indica que no hay ninguna instrucción BEGIN TRANSACTION asociada.

No se puede cancelar una transacción después de ejecutar una instrucción
COMMIT TRANSACTION, porque las modificaciones sobre los datos ya son parte permanente de la base de datos.

Este ejemplo utiliza @@TRANCOUNT para comprobar si hay transacciones
abiertas que haya que confirmar.

BEGIN TRANSACTION
UPDATE authors SET au_lname = upper(au_lname)
WHERE au_lname = ‘White’

IF @@ROWCOUNT = 2
COMMIT TRAN

IF @@TRANCOUNT > 0
BEGIN
PRINT ‘A transaction needs to be rolled back’
ROLLBACK TRAN
END

Fuentes: V.Popov (Foros Microsoft)

Posted in .NET, DataAccess | Etiquetado: | Leave a Comment »