Buscar este blog

lunes, 29 de junio de 2015

Bloqueos en Sql Server (Qué Son / Cómo Detectarlos y Monitorearlos / Cómo Minimizar su Ocurrencia)

Amigos,

     Como DBA’s de una compañía todos alguna vez hemos escuchado la famosa frase “el sistema está lento” o bien, de boca de un desarrollador, “por favor me puedes dar una mano, el sistema tira time out y tengo muchos usuarios furiosos, no entiendo que pasa”…

     Desde ya que uno supone que, cuando el reclamo ha llegado a nuestra área, se han descartado o bien se están descartando,  todo un abanico de cuestiones que exceden a nuestra función  tales como, problema de conexiones saturadas o lentas, problemas de hardware, etc. etc.

     Hablando de Base de Datos, no siempre la lentitud  está directamente relacionada con la existencia de constantes bloqueos, pero si es nuestra responsabilidad tratar disminuir al mínimo posible los mismos.

     Para ello, sin querer aburrirlos con teoría, les propongo:

-          Un poco de teoría (lo mínimo para entender realmente este importante tema)
-            ontSistematizar rar la forma de detectarlos (queries y algo más..)
-          Aprender tips para minimizar su ocurrencia

Lo prometido, un poco de teoría…

    Empecemos por definir qué es un Bloqueo?...

    Pues bien, en términos sencillos, un bloqueo es aquella demora que se produce en el acceso a un recurso de una base de datos (tablas, índices, etc),  cuando dos procesos “compiten” por tener la exclusividad sobre el mismo. En este caso se produce el famoso “bloqueo” que hace que uno de los procesos deba esperar a que el otro termine de utilizar el recurso, para poder hacer uso del mismo.

    Pero por qué un proceso querría tomar la exclusividad sobre un recurso? Pues bien… para entender ello debemos repasar los dos principales tipos de Locks del SQL Server … (hay otros derivados de ellos pero que no van a ser abordados en el presente art.)

    Muy brevemente ..

·         Shared Lock (S): es el loqueo que establece cualquier SELECT  sobre una tabla (lectura).

·         Exclusive Lock (X): es el nivel de lockeo necesario para cualquier instrucción de modificación de data (INSERT/UPDATE/DELETE).

    De la existencia de estos dos tipos de lockeos es fácil inferir que, si un usuario “A” está realizando un SELECT sobre una tabla,  y otro usuario “B”  quiere hacer una modificación sobre los datos de la misma (INSERT/UPDATE/DELETE),  “B”, tendrá que esperar a que “A” termine dicha operación de lectura, quedando momentáneamente BLOQUEADA la tabla.
    Del mismo modo… , si un usuario está modificando los datos de una tabla, los demás usuarios que deseen hacer desde una simple lectura hasta una modificación de  datos de esa tabla deberán esperar, puesto que, como hemos dicho.. todo INSERT/UPDATE/DELETE requiere de un lockeo exclusivo de la tabla…

    Pero por qué Microsoft  ha dispuesto la necesidad de “lockeos exclusivos” ? Tranquilos amigos, no se trata de mentes malvadas, se trata de que SQL SERVER es una base de datos relacional que toma este tipo de comportamiento para asegurarnos la integridad de la data. (es decir, que uno lea datos reales, no datos que ya no existen o han sido modificados al momento de la lectura)

   Se imaginan Uds en caso contrario, qué ocurriría si un usuario “A” está consultando el stock de un artículo cuyo stock  está siendo modificado  por otro usuario “B” al haber hecho una venta del mismo?. Pues el usuario “A” contaría con un estado falso del stock… ya que fue modificado por el usuario “B”. Eso es lo que se llama una “lectura sucia” y es lo que el sql server por default nos evita.

   Uds me dirán “Entonces es normal que existan bloqueos?”. Pues bien, todo sistema de base de datos tiene bloqueos, el problema es cuando los mismos son prolongados,  ya que esto produce  encolamientos y una degradación general todas las operaciones que afectan al sistema

    Y en este punto amigos es donde nosotros como DBA debemos intervenir…. Ya veremos cómo, no sin antes hablar de algo directamente relacionado con el tema que nos convoca… el “Isolation Level” (nivel de aislamiento en castellano).
   No es otra cosa más que un “seteo” que determina el grado de concurrencia simultánea con el que los datos pueden ser accedidos dentro de una sesión de Base de Datos.

   Por defecto, el SQL Server viene seteado con un “isolation leve”l llamado “READ COMMITED” . Este nivel de aislamiento no permite lecturas sucias.  Es decir, si “A” quiere leer o modificar un recurso que “B” está modificando, como explicamos anteriormente,  no puede hacerlo.

    Es posible sin embargo “setear”, por sesión o sólo para una determinada sentencia,  el Isolation Level para que permita lecturas sucias. Es decir,  lograr que por defecto ningún usuario al realizar una lectura establezca un lockeo, esto si vamos a la prática permitirá al proceso “A”  leer si “B” está modificando datos y, a la vez a “B” modificar datos si “A” está leyendo. Este tipo de seteo se llama “READ UNCOMITTED” y es el más laxo de los isolation levels, aptos para entornos donde la integridad de la data no sea un tema a tener en consideración.
-   Seteando una query de lectura para permitir lecturas sucias:

       select idarticulo from articulos with (nolock)

-           Seteando el isolation level de una sesión para permitir lecturas sucias:

      SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    Existe un tercer tipo de isolation level  que elimina un efecto llamado “REPETABLE READ”. Vamos a un ejemplo para una mejor comprensión. Supongamos que una transacción bancaria necesita leer el saldo inicial del cliente, para, a partir del mismo,  realizar cálculos y la adición de un nuevo registro con los intereses bancarios que modifica el saldo del cliente leído. Luego, como parte de la misma transacción, necesita volver a leer el saldo original dado que necesita insertar otro registro con x gasto bancario relacionado con el saldo original del cliente, (saldo que ya fue modificado como producto de la inserción previa de los intereses). Pues bien, la única forma de que esta transacción pueda contar con el saldo original es seteando la misma dentro un isolation level llamado “REPETABLE READ”, de lo contrario no podrá recuperar el saldo original. El saldo hasta finalizada la transacción siempre será el original si bien en la práctica ya  fue modificado por la adición de intereses.

-         -       Seteando el isolation level de una sesión:

    SET TRANSACTION ISOLATION LEVEL REPETEABLE READ

   Por último tenemos el más restrictivo de los Isolation Level, es llamado “SERIALIZABLE”. En este nivel de aislamiento el lockeo es sostenido durante toda la transacción, no pudiendo modificarse ni leerse dato alguno hasta que la misma no finalice.

-         -   Seteando el isolation level de una sesión:

   SET TRANSACTION ISOLATION LEVEL SERILIZABLE
 
    Pero como bonus, el SQLSrv nos entrega un Isolation Level que es muy interesante llamado “READ_COMITTED_SNAPSHOT”.  Este nivel de Isolation permite leer sin establecer lockeos, pero no nos entrega una lectura sucia (ahí lo interesante de este nivel). Bajo este isolation el sql server toma el último estado consistente (comitido) de un dato al comienzo de la lectura del mismo. Cómo lo hace? Leyendo desde la TempDb esta última versión consistente del dato….  Es decir que la integridad del dato está garantizada a nivel de sentencia sin establecer lockeos. Interesante no?. A tenerlo en cuenta ya que es muy útil…

-         - Seteando el isolation level de una sesión:
  
 SET TRANSACTION ISOLATION LEVEL READ_COMMITTED_SNAPSHOT


 Cómo Monitorear y Detectar Bloqueos ?

1) Utilizando la tabla del sistema sysprocesses


Ejemplo:


             Aquí la columna “blocked” nos dice qué proceso está causando el bloqueo. Para nuestro ejemplo el proceso 53 está siendo bloqueado por una requisitoria hecha por el proceso 52.


              Ahora pues podemos averiguar qué procesos están corriendo utilizando el comando DBCC INPUTBUFFFER, veamos:



   Si  quisiéramos entender qué tipo de lockeo está sosteniendo el proceso 52 …utilizamos SP_LOCK..


   Como podemos ver en la fila sombreada, el proceso 52 tiene un  intento de lockeo exclusivo permitido (IX), por lo tanto el proceso 53, que quiere hacer una lectura tiene que aguardar….


 2)      utilizando las vistas dinámicas del sistema

     A partir del Sql Server 2005 contamos con estas vistas que nos dan un nivel mayor de información que las viejas tablas del sistema.

     En este caso vamos a utilizar dos vistas a saber:

   sys.dm_exec_sessions: es una vista que, a nivel de server nos otorga información acerca de todas las conexiones de usuarios abiertas así como también las tareas internas del sistema (spid menor a 50).
   sys.dm_exec_requests: es una vista que nos devuelve información de cada solitud (request) que está pasando por el servidor.
   sys.dm_exec_sql_text: que actúa como una función que nos devuelve el texto del batch que se está ejecutando (identificado como sql_handle).

  Ahora que sabemos que función tiene cada vista, les cuento que las mismas pueden usarse combinadas con joins para encontrar en detalle:
·         El proceso bloqueado .
·         El proceso que está bloqueando.


Nada mejor que un buen ejemplo para poder entender su funcionamiento:
Vamos crear una tabla llamada “bloqueo” donde intentaremos insertar un dato y obteniendo pues el lockeo exclusivo de la tabla, el cual vamos a retardar con un waitfor delay de 2 minutos


-- creo la tabla y le insterto un registro, luego un wait..--
begin transaction
insert into bloqueo (id)
values (1)
waitfor delay  '00:02:00'

  Ahora abrimos otra session (otra pestaña de nuestro sql server) y tratemos de leer la tabla que está siendo bloqueada

-- intento leer la tabla lockeada en modo exlclusivo --
select * from bloqueo

Y llegó el momento de abrir una tercer pestaña y ejecutar nuestra query, la cual les recomiendo, guarden en su “botiquín de primeros auxilios”

 La query es la siguiente:

---------------------------------------------------------
--  Detalle: Muestra bloqueos en nuestra base de datos --
--  1) La sesion que está bloqueando                   --
--  2) La sesión bloqueada                             --
---------------------------------------------------------

SELECT
er.session_id ,
host_name,
program_name,
original_login_name,
er.status,
er.reads ,
er.writes,
er.cpu_time,
wait_type,
wait_time,
wait_resource,
blocking_session_id,
st.text,
er.transaction_isolation_level
FROM sys.dm_exec_sessions es
--
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
--
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
--
WHERE
er.blocking_session_id > 0
Union
SELECT
er.session_id ,
host_name,
program_name,
original_login_name,
er.status,
er.reads ,
er.writes,
er.cpu_time,
wait_type,
wait_time,
er.wait_resource,
er.blocking_session_id,
st.text,
er.transaction_isolation_level
FROM sys.dm_exec_sessions es
---
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
---
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
--
WHERE es.session_id IN ( SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0 )

Veremos pues como resultado de nuestra query en primer lugar al proceso que encuentra el bloqueo, el proceso nº 61, el cual intenta leer la tabla de nuestro ejemplo previamente lockeada en modo exclusivo por el proceso 67.

En “text” tenemos las sentencias que forman parte de los bloqueos…















     3)  ESTABLECIENDO UN “BASELINE” MEDIANTE EL PERFORMANCE MONITOR

   El “performance monitor” en si no es una herramienta que nos permita detectar la query que está bloqueando y la query que está esperando por la liberación de recursos. Mas su principal utilidad está en obtener un “baseline”, es decir un número promedio, de lo que serían los bloqueos promedio de nuestro sistema de base de datos.

   Para ello debemos utilizar los contadores “LOCK request/dec” – “Avg Wait Time (ms)” – “Lock Waits/sec” and “Number of Deadlocks”

Ejemplo:




 Cómo Minimizar La Ocurrencia de Bloqueos ?

 Amigos, he aquí unos consejos breves y concretos para tratar de minimizar la cantidad y duración de los bloqueos en nuestra base de datos:

  La premisa es que nuestras queries “lean y liberen lo más rápido posible”  Esto lo lograremos:

1)      Haciendo que nuestros códigos devuelvan la mínima cantidad de datos posibles

1.1) No a los Select * from ¡!!! Seleccionemos sólo los campos estrictamente necesarios.

1.2) No a las queries poco selectivas que devuelven muchos más datos de los necesarios,     filtremos bien en los where!!!

1.3) Revisemos los códigos que identificamos como conflictivos y rehagamos aquellos que sean necesario rehacer.

2)       Dotando a las tablas de los índices necesarios

2.1)Estudiemos los planes de ejecución a fondo y optimicemos las queries dotando las tablas              los índices necesarios para el funcionamiento óptimo de las queries.

3)      Implementando rutinas de mantenimiento de índices y estadísticas.

3.1) En entornos OLTP (de mucha modificación de datos), los índices tienden a degradarse rápidamente. Se fragmentan y pierden efectividad. Es necesario implementar rutinas de optimización y mantenimiento de los mismos (en este blog encontraran un script para ello)

3.2) Las estadísticas son importantísimas para que nuestros planes de ejecución elijan la mejor alternativa para ejecutar una query. Tengan en cuenta que si bien Microsoft nos da la chance de setear su actualización automáticamente dicha actualización no es efectiva. Utilicen una rutina para tal fin. La pueden encontrar también en este blog.

4)      Utilizando el Isolation Level apropiado para nuestro negocio.

4.1) Hemos estudiado los distintos tipos de Isolation Level existentes. La idea es utilizar el que se adecue a las exigencias de nuestro sistema. Si por ejemplo estamos en un sistema que permite utilizar lecturas sucias sería una buena idea abolir los lock utilizando el nivel Read Uncommitted a nivel de sesión o con el hint with (no lock) luego del from.

4.2) Tal vez no podemos realizar lecturas sucias pero si utilizar el Isolation Level “READ_COMMITED_SNAPSHOT” y asi evitar lockeo y los posteriores bloqueos.

5)      Particionando tablas cuando sea conveniente hacerlo.

5.1) El particionar tablas puede agilizar mucho nuestras consultas en tablas que necesitan mantener grandes cantidades de datos históricos on line y que por lo tanto tienen varios millones de registros.  Si en este tipo de tablas, existen consultas donde se puede llegar a necesitar datos históricos y actuales de un cliente (por ejemplo la consulta histórica de la cuenta corriente de una empresa), pues bien, sería una buena idea particionar por año y generar, distribuir datafiles en distintos discos y generar lecturas paralelas. OJO NO ABUSAR de las PARTCIONES, ya que su mantenimiento es complejo (más de todo este tema en este blog)


6)      Ubicando correctamente los datafiles de nuestras bases de datos.

6.1)  El gran punto fuerte del SQL Server es la posibilidad de realizar lectoescrituras paralelas Para ello un escenario ideal es el tener separado en unidades físicas diferentes:
 - El OS
 - El motor de Base de Datos
 - Los archivos de Datos
 - Los archivo de índices
-  Los archivos de log.

6.2)  Traten siempre de que sea posible de lograr este escenario. No olviden una vez hecho esto el analizar la carga de los discos (i/o) valiéndose del perfomance monitor y, llegado el caso, dettachar y reubicar los files que sean necesarios.

6.3) Para asegurarse de que el SqlSrv está seteado para utilizar todos los procesadores disponibles controlen el valor de “Server Properties”àAdvancedàParallelismàMaxDegree of Parallelism, el mismo debe ser de cero (0)-

7)      Manteniendo en línea sólo la data necesaria

El realizar el pase a files “off line” o bien a backups, es una tarea que si la reglas de negocio la permiten debería ser planificada y ejecutada con periodicidad.


8)      Parametrizando consultas

Si podemos utilizar una consulta y llegar a ella con parámetros, vamos a poder reutilizar los planes de ejecución cacheados en memoria. Lo opuesto sería el utilizar la misma consulta pero con valores hardcodeados cada vez que la utilicemos.

9)      Manteniendo los discos desfragmentados

Es muy importante que tengamos corriendo en períodos de baja o nula concurrencia alguna herramienta eficiente de desfragmentado de nuestros hard disk.

10) Monitoreando permanentemente el nivel de actividad de nuestro servidor

Es vital la uilización del performance monitor y el event viewer y el establecimiento de baselines. Si vemos por ejemplo que constantemente nuestro sistema pagina, puede ser una buena idea revisar la memoria ram asignada a nuestro sql server.

7 comentarios:

  1. Qué increible articulo!!!

    Super genial!,

    Muchas gracias!

    ResponderEliminar
  2. Hace tiempo vengo buscando este tipo de blog, me parece genial. Tengo bloqueos en la base de datos, intentare utilizar Isolations leave haber como me va

    ResponderEliminar
  3. Vaya currada que te has metido (o te metiste en su día) para facilitar toda esta información a tipejos como yo. Gran blog con material interesante; felicidades.

    ResponderEliminar
  4. Excelente!!!!! como esta explicación facilita el conocimiento para el tratamiento de los bloqueos.
    Gustavo muy agradecido por tu aporte con esta información.

    ResponderEliminar
  5. gracias por tus aportaciones

    ResponderEliminar