Modo de implementar bloqueos en SQL Server

Id. de artículo: 550285 - Ver los productos a los que se aplica este artículo
Este artículo se publicó anteriormente con el número E10286
Expandir todo | Contraer todo

Resumen

En este artículo se describe cómo SQL Server implementa los bloqueos.

Síntomas

El tema de los bloqueos en los gestores de base de datos como es Microsoft SQL SERVER siempre ha sido crítico ya que afecta a la integridad de los datos como a la concurrencia de los usuarios cuando acceden simultáneamente a una tabla.

Un ejemplo muy habitual para explicar el porque de los bloqueos, es una aplicación bancaria la cual ejecuta una serie de transacciones sobre las cuentas bancarias, pero que a la vez tu puedes visualizar los datos. Así, si no existieran los bloqueos se podría estar viendo datos que ya no son válidos porque están siendo cambiados por otro lado; mientras que los bloqueos permiten que cuando alguien está modificando o leyendo unos datos nadie pueda estar modificándolos a la vez.

Más información

En Microsoft SQL SERVER los bloqueos se realizan a nivel de paginas de 2K, a diferencia de otros gestores de datos que lo realizan a nivel de registro.

Esta gestión de bloqueos por páginas es más optimo para consultas ya que lleva menos sobrecarga de punteros que si lo hiciéramos por registro, pero puede llegar a ser menos óptimo en inserciones masivas de registros en una tabla ( factor que la nueva versión de Microsoft SQL SERVER 6.5 con la nueva característica de Insert Row-Level Locking -inserciones con bloqueos a nivel de registro- ha sido optimizado).

En general los bloqueos son gestionados por Microsoft SQL SERVER de forma automatizada sin necesidad de gestión manual, pero existen dos factores los cuales necesitan una intervención manual, estos son en términos informáticos : BLOCKING y DEADLOCKS.
  1. BLOCKING.- Con este término se conoce la acción de espera de un proceso o varios por culpa de un bloqueo existente por otro proceso sobre los mismos datos. La solución para evitar mínimamente este tipo de bloqueo es optimizar el diseño de las tablas, los índices utilizados, la implementación de la sentencia sql y de la configuración hardware.
  2. DEADLOCK.- Con este término se conoce a el suceso de que dos procesos estén cada uno bloqueando un objeto y a la vez ambos necesitan también acceder a el objeto bloqueado por el otro. Esto crea un nudo, para lo que Microsoft SQL SERVER dependiendo del tiempo de espera en el procesador de los procesos, realiza un roll back y lanza el mensaje de error 1205 en uno de los procesos, permitiendo al otro continuar.




CÓMO PODEMOS VER LOS BLOQUEOS EN MICROSOFT SQL SERVER

En Microsoft SQL SERVER tenemos varias formas de visualizar los bloqueos que están ocurriendo:
  1. Desde Enterprise Manager en Current Activity y en la ficha Object Locks podemos ver de una forma visual los tipos de bloqueos que se están produciendo.
  2. Mediante el procedimiento almacenado sp_lock podemos ver lo mismo que el anterior pero de formato texto.
  3. En el Performance Monitor de Windows NT visualizando el objeto SQL Server-Locks
  4. Con el procedimiento almacenado sp_who, que reporta una lista de los procesos que están realizando bloqueos.
  5. Utilizando "trace flags" con la función DBCC traceon ( número):
    1. 1200: reporta los identificadores de los procesos y los tipos de bloqueos lanzados.
    2. 1204: reporta los bloqueos que están produciendo un "deadlock" y los comandos envueltos.
    3. 1205: Visualiza información sobre los comandos utilizados durante el "deadlock".
      
                  DBCC traceon(3604)
                  DBCC traceon(1200)
                  UPDATE t_1
                  SET c_1 = 0
      
                  Process 11 requesting page lock of type SH_PAGE on 7 25
                  Process 11 releasing page lock of type SH_PAGE on 7 25
                  Process 11 releasing page lock of type SH_PAGE on 7 26
                  Process 11 requesting table lock of type EX_TAB on 7 
                     80003316
                  Process 11 clearing all pss locks
      


TIPOS DE BLOQUEOS EN MICROSOFT SQL SERVER

Cualquier transacción SQL que lea o modifique datos (SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, etc.) generará algún tipo de bloqueo. Estos tipos pueden ser:
  1. BLOQUEOS DE PAGINAS (2k).

    Siempre Microsoft SQL Server intentará realizar un bloqueo a nivel de página (2k) que un bloqueo a nivel de tabla, ya que esto generará un menor número de procesos en espera. Hay 3 tipos de bloqueos de página:
    1. Compartido.- Este bloqueo se da cuando realizamos una sentencia SELECT sobre una tabla y solo permite ejecutar otros SELECT sobre la misma página bloqueada; por lo que no podrás ejecutar una sentencia modificadora de datos como el INSERT. Este tipo de bloqueos es liberado en el momento que la sentencia es ejecutada.
    2. Exclusivo.- Este bloqueo se produce al intentar realizar una modificación de datos, no permitiendo ejecutar cualquier otra sentencia sobre esta página hasta que la sentencia se haya ejecutado.
    3. Actualización.- Este bloqueo se produce cuando ejecutas una sentencia UPDATE o DELETE sobre una tabla, transformándose en un bloqueo exclusivo de página en el momento que modifica los datos.
  2. BLOQUEOS A NIVEL DE TABLA.

    Un bloqueo a nivel de tabla bloquea la tabla y los índices. Microsoft SQL SERVER siempre intenta en primer momento realizar bloqueos a nivel de página pero llega un momento que la gestión no es optima a este nivel, cuando se llega a más de 200 bloqueos de páginas (por defecto), y se escala el bloqueo a nivel de tabla automáticamente. Claro está que con este tipo de bloqueo no se podrá ejecutar ningún tipo de sentencia sobre la tabla hasta que no sea liberada. Hay dos tipos:
    1. Compartido.- Lo mismo que a nivel de página, solo que con la tabla entera bloqueada.
    2. Exclusivo.- Lo mismo que a nivel de página, solo que con la tabla entera bloqueada.
    3. De Intención.- Un bloqueo de tipo intencionado ocurre cuando SQL tiene la intención de adquirir un bloqueo exclusivo o compartido de una tabla. De esta manera SQL Server se guarda de que no se pueda realizar sobre esta tabla otro bloqueo por otro proceso.


TABLA RESUMEN DE BLOQUEOS A NIVEL DE TABLA Y PÁGINAS

Usando Sentencia Bloqueos nivel tabla Bloqueos nivel pagina
Indice
N/A INSERT Intención de Exclusiva Exclusiva
SI SELECT Intención de Compartida Compartida
SI SELECT with HOLDLOCK Intención de Compartida Compartida
SI UPDATE Intención de Exclusiva Actualización y Exclusiva
SI DELETE Intención de Exclusiva Exclusiva
No SELECT Intención de Compartida Compartida
No SELECT with HOLDLOCK Compartida Ninguna
No UPDATE Exclusiva Ninguna
No DELETE Exclusiva Ninguna
N/A Create clustered index Exclusiva Ninguna
N/A Create nonclustered index Compartida Ninguna


TRUCOS A TENER EN CUENTA PARA MINIMIZAR LOS BLOQUEOS
  1. Truco 1: Usar índices con sentencias UPDATE y DELETE.
  2. Truco 2: Evitar ejecutar un INSERT INTO e implementarlo con un bucle de un INSERT
    
          declare @col1 varchar(11)
          declare sample_cursor cursor
    
          for select col1 from table1
             open sample_cursor
    
          fetch next from sample_cursor into @col1
    
          while @@fetch_status = 0
    
             begin
    
                insert into table2 values (@col1)
                fetch next from sample_cursor into @col1
    
             end
    
          deallocate sample_cursor
    
  3. Truco 3: En tablas sobre las que vas a realizar un número frecuente de inserciones, crear índices de tipo cluster.
  4. Truco 4: Reducir y si es posible dividir las transacciones grandes. Por ejemplo:
    
          INSERT INTO t_1
          SELECT * FROM t_2
    
    Dividirlo en:
    
          INSERT INTO t_1
    
          SELECT * FROM t_2
             WHERE t_2.id <= 50
    
          INSERT INTO t_1
    
          SELECT * FROM t_2
             WHERE t_2.id > 50
    
  5. Truco 5: Minimizar el número de índices no-cluster sobre una tabla.
  6. Truco 6: Reducir el número de columnas por tabla.
  7. Truco 7: Ejecutar las transacciones que modifiquen un gran número de datos en horas fuera de oficina.
  8. Truco 8: Añadir más memoria al servidor.
  9. Truco 9: Modificar los parámetros de configuración del servidor SQL en lo que respecta a los bloqueos: LE threshold percent, LE threshold minimun, LE threshold maximun.


PARÁMETROS DE CONFIGURACIÓN DE BLOQUEOS
  1. A nivel de servidor.- Tenemos los siguientes:
    1. LOCKS: El máximo número de bloqueos que se puede dar en el servidor. Teniendo en cuenta que cada bloqueo adquiere 32 bytes de memoria.
    2. OPEN OBJECTS: El máximo número de objetos que puede estar abiertos a la vez en el servidor, teniendo en cuenta que cada objeto abierto usa 70 bytes de memoria.
    3. Parámetros que tiene en cuenta el servidor para escalar un bloqueo de página a tabla:
      1. LE hreshold percent: el porcentaje de bloqueos de paginas sobre una tabla que se tiene que producir para escalar a un bloqueo de tabla.
      2. LE threshold minimun: el mínimo número de bloqueos que se tienen que dar de páginas para escalar a un bloqueo de tabla.
      3. LE threshold maximun: el número máximo de bloqueos de páginas para escalar a un bloqueo de tabla.
  2. A nivel de transacciones:
    1. Utilizando la sentencia SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}. Esto afecta a toda la sesión.
    2. Explícitamente configurando la sentencia SELECT:
      SELECT select_list

      FROM table list [HOLDLOCK | UPDLOCK | NOLOCK | PAGLOCK | TABLOCK | TABLOCKX]

Propiedades

Id. de artículo: 550285 - Última revisión: martes, 16 de marzo de 2004 - Versión: 3.0
La información de este artículo se refiere a:
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
Palabras clave: 
bloqueo sqlserver uso KB550285
Renuncia a responsabilidad de los contenidos de la KB sobre productos a los que ya no se ofrece asistencia alguna
El presente artículo se escribió para productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com