transaccion - Diagnóstico de interbloqueos en SQL Server 2005

tipos de bloqueos sql server (22)

Estamos viendo algunas condiciones perniciosas, pero raras, de interbloqueo en la base de datos Stack Overflow SQL Server 2005.

Anexé el generador de perfiles, establecí un perfil de rastreo usando este excelente artículo sobre resolución de problemas y capturé un montón de ejemplos. Lo extraño es que la escritura de bloqueo es siempre la misma :

UPDATE [dbo].[Posts] SET [AnswerCount] = @p1, [LastActivityDate] = @p2, [LastActivityUserId] = @p3 WHERE [Id] = @p0

El otro enunciado de interbloqueo varía, pero generalmente es una lectura trivial y simple de la tabla de publicaciones. Este siempre es asesinado en el punto muerto. Aquí hay un ejemplo

SELECT [t0].[Id], [t0].[PostTypeId], [t0].[Score], [t0].[Views], [t0].[AnswerCount], [t0].[AcceptedAnswerId], [t0].[IsLocked], [t0].[IsLockedEdit], [t0].[ParentId], [t0].[CurrentRevisionId], [t0].[FirstRevisionId], [t0].[LockedReason], [t0].[LastActivityDate], [t0].[LastActivityUserId] FROM [dbo].[Posts] AS [t0] WHERE [t0].[ParentId] = @p0

Para ser perfectamente claros, no estamos viendo bloqueos de escritura / escritura, sino de lectura / escritura.

Tenemos una mezcla de LINQ y consultas SQL parametrizadas en este momento. Hemos agregado with (nolock) a todas las consultas SQL. Esto puede haber ayudado a algunos. También tuvimos una consulta de credencial única (muy) mal escrita que arreglé ayer, que tardó más de 20 segundos en ejecutarse cada vez, y que estaba funcionando cada minuto además de eso. ¡Esperaba que esta fuera la fuente de algunos de los problemas de bloqueo!

Desafortunadamente, recibí otro error de interbloqueo hace aproximadamente 2 horas. Los mismos síntomas exactos, la misma culpa exacta de escribir.

Lo realmente extraño es que la sentencia SQL de bloqueo de bloqueo que ves arriba es parte de una ruta de código muy específica. Solo se ejecuta cuando se agrega una nueva respuesta a una pregunta; actualiza la pregunta principal con el nuevo recuento de respuestas y la última fecha / usuario. Obviamente, esto no es tan común en relación con la gran cantidad de lecturas que estamos haciendo. Por lo que puedo decir, no estamos haciendo un gran número de escrituras en ninguna parte de la aplicación.

Me doy cuenta de que NOLOCK es una especie de martillo gigante, pero la mayoría de las consultas que corremos aquí no necesitan ser tan precisas. ¿Te importa si tu perfil de usuario está desactualizado?

Usar NOLOCK con Linq es un poco más difícil ya que Scott Hanselman discute aquí .

Estamos flirteando con la idea de usar

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

en el contexto base de la base de datos para que todas nuestras consultas LINQ tengan este conjunto. Sin eso, tendríamos que ajustar cada llamada LINQ que hacemos (bueno, las de lectura simple, que es la gran mayoría de ellas) en un bloque de código de transacción de 3-4 líneas, lo cual es feo.

Creo que estoy un poco frustrado de que las lecturas triviales en SQL 2005 puedan bloquear las escrituras. Pude ver bloqueos de escritura / escritura siendo un gran problema, pero ¿lee? No estamos ejecutando un sitio bancario aquí, no necesitamos una precisión perfecta todo el tiempo.

Ideas? ¿Pensamientos?

¿Estás instanciando un nuevo objeto LINQ to SQL DataContext para cada operación o quizás estás compartiendo el mismo contexto estático para todas tus llamadas?

Jeremy, estamos compartiendo un contexto de datos estáticos en la base del controlador en su mayor parte:

private DBContext _db; /// <summary> /// Gets the DataContext to be used by a Request''s controllers. /// </summary> public DBContext DB { get { if (_db == null) { _db = new DBContext() { SessionName = GetType().Name }; //_db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"); } return _db; } }

¿Recomiendas que creemos un nuevo contexto para cada controlador, o por página, o ... más a menudo?

¿Te importa si tu perfil de usuario está desactualizado?

No, eso es perfectamente aceptable. Establecer el nivel de aislamiento de transacción base es probablemente la mejor / más limpia manera de ir.

¿Te importa si tu perfil de usuario está desactualizado?

Unos segundos definitivamente serían aceptables. No parece que sea tan largo, de todos modos, a menos que una gran cantidad de personas esté enviando respuestas al mismo tiempo.

¿Estás instanciando un nuevo objeto LINQ to SQL DataContext para cada operación o quizás estás compartiendo el mismo contexto estático para todas tus llamadas? Originalmente probé el último enfoque, y por lo que recuerdo, causó un bloqueo no deseado en el DB. Ahora creo un nuevo contexto para cada operación atómica.

@Jeff - Definitivamente no soy un experto en esto, pero he tenido buenos resultados al crear un nuevo contexto en casi cada llamada. Creo que es similar a crear un nuevo objeto Connection en cada llamada con ADO. La sobrecarga no es tan mala como cabría pensar, ya que la agrupación de conexiones se seguirá utilizando de todos modos.

Solo uso un helpers estático global como este:

public static class AppData { /// <summary> /// Gets a new database context /// </summary> public static CoreDataContext DB { get { var dataContext = new CoreDataContext { DeferredLoadingEnabled = true }; return dataContext; } } }

y luego hago algo como esto:

var db = AppData.DB; var results = from p in db.Posts where p.ID = id select p;

Y haría lo mismo para las actualizaciones. De todos modos, no tengo casi tanto tráfico como tú, pero definitivamente me estaba quedando bloqueado cuando usaba un DataContext compartido desde el principio con solo un puñado de usuarios. Sin garantías, pero podría valer la pena intentarlo.

Actualización : Por otra parte, al mirar su código, solo está compartiendo el contexto de datos durante el tiempo de vida de esa instancia de controlador en particular, que básicamente parece estar bien a menos que de alguna manera se utilice simultáneamente por varias llamadas dentro del controlador. En un hilo sobre el tema, ScottGu dijo:

Los controladores solo viven para una sola solicitud, por lo que al finalizar el procesamiento de una solicitud son basura recolectada (lo que significa que DataContext se recoge) ...

De todos modos, puede que no sea así, pero de nuevo probablemente valga la pena intentarlo, tal vez junto con algunas pruebas de carga.

Ahora que veo la respuesta de Jeremy, creo recordar que escuché que la mejor práctica es usar un nuevo DataContext para cada operación de datos. Rob Conery escribió varias publicaciones sobre DataContext, y siempre las actualiza en lugar de usar un singleton.

Este es el patrón que usamos para Video.Show ( enlace a la vista fuente en CodePlex ):

using System.Configuration; namespace VideoShow.Data { public class DataContextFactory { public static VideoShowDataContext DataContext() { return new VideoShowDataContext(ConfigurationManager.ConnectionStrings["VideoShowConnectionString"].ConnectionString); } public static VideoShowDataContext DataContext(string connectionString) { return new VideoShowDataContext(connectionString); } } }

Luego, en el nivel de servicio (o incluso más granular, para las actualizaciones):

private VideoShowDataContext dataContext = DataContextFactory.DataContext(); public VideoSearchResult GetVideos(int pageSize, int pageNumber, string sortType) { var videos = from video in DataContext.Videos where video.StatusId == (int)VideoServices.VideoStatus.Complete orderby video.DatePublished descending select video; return GetSearchResult(videos, pageSize, pageNumber); }

Antes de quemar la casa para atrapar una mosca con NOLOCK por todas partes, es posible que desee echarle un vistazo al gráfico de interbloqueo que debería haber capturado con Profiler.

Recuerde que un punto muerto requiere (al menos) 2 bloqueos. La conexión 1 tiene el Bloqueo A, quiere el Bloqueo B y viceversa para la Conexión 2. Esta es una situación sin solución, y alguien tiene que dar.

Lo que has mostrado hasta ahora se resuelve con un simple bloqueo, que Sql Server está feliz de hacer todo el día.

Sospecho que usted (o LINQ) están comenzando una transacción con esa declaración ACTUALIZADA en ella, y SELECCIONANDO alguna otra pieza de información de antemano. Pero, realmente necesita retroceder a través del gráfico de interbloqueo para encontrar los bloqueos mantenidos por cada subproceso, y luego retroceder a través de Profiler para encontrar las declaraciones que causaron los bloqueos que se otorgarán.

Espero que haya al menos 4 enunciados para completar este acertijo (o una afirmación que requiera varios bloqueos, ¿quizás hay un activador en la tabla Publicaciones?).

Continuaría sintonizando todo; ¿Cómo está funcionando el subsistema de disco? ¿Cuál es la longitud promedio de la cola del disco? Si las E / S están realizando una copia de seguridad, el problema real podrían no ser estas dos consultas que están bloqueando, podría ser otra consulta que está obstaculizando el sistema; Usted mencionó una consulta que tardó 20 segundos en ajustarse, ¿hay otras?

Concéntrese en acortar las consultas de larga duración, apuesto a que los problemas de interbloqueo desaparecerán.

De acuerdo con MSDN:

http://msdn.microsoft.com/en-us/library/ms191242.aspx

Cuando las opciones de la base de datos LEÍDO COMPROMETIDO INSTANTÁNEO o PERMITIR INSTANTÁNEO AISLAMIENTO están en ON, se mantienen copias lógicas (versiones) para todas las modificaciones de datos realizadas en la base de datos. Cada vez que se modifica una fila por una transacción específica, la instancia de Database Engine almacena una versión de la imagen previamente comprometida de la fila en tempdb. Cada versión está marcada con el número de secuencia de transacción de la transacción que realizó el cambio. Las versiones de las filas modificadas están encadenadas utilizando una lista de enlaces. El valor de fila más nuevo siempre se almacena en la base de datos actual y se encadena a las filas versionadas almacenadas en tempdb.
Para transacciones de corta duración, una versión de una fila modificada puede almacenarse en caché en el grupo de búferes sin que se escriba en los archivos del disco de la base de datos tempdb. Si la necesidad de la fila versionada es de corta duración, simplemente se eliminará del grupo de búferes y no necesariamente incurrirá en gastos indirectos de E / S.

Parece que hay una pequeña penalización de rendimiento por la sobrecarga adicional, pero puede ser insignificante. Debemos probar para estar seguros.

Intenta configurar esta opción y QUITAR todos los NOCKOCK de las consultas de código, a menos que sea realmente necesario. Los NOLOCK o el uso de métodos globales en el controlador de contexto de la base de datos para combatir los niveles de aislamiento de transacción de la base de datos son curitas para el problema. NOLOCKS enmascara problemas fundamentales con nuestra capa de datos y posiblemente conduzca a la selección de datos no confiables, donde el control de versiones de selección / actualización automática parece ser la solución.

ALTER Database [.Beta] SET READ_COMMITTED_SNAPSHOT ON

Debe implementar lecturas sucias.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Si no requiere absolutamente la integridad transaccional perfecta con sus consultas, debe utilizar lecturas sucias al acceder a tablas con alta concurrencia. Supongo que su tabla de Publicaciones sería una de esas.

Esto puede proporcionarle las llamadas "lecturas fantasmas", que es cuando su consulta actúa sobre datos de una transacción que no se ha confirmado.

No estamos ejecutando un sitio bancario aquí, no necesitamos una precisión perfecta todo el tiempo

Use lecturas sucias Tienes razón en que no te darán una precisión perfecta, pero deberían aclarar tus problemas de bloqueo.

Sin eso, tendríamos que ajustar cada llamada LINQ que hagamos (bueno, las de lectura simple, que es la gran mayoría de ellas) en un bloque de código de transacción de 3-4 líneas, que es feo

Si implementa lecturas sucias en "el contexto de la base de datos base", siempre puede ajustar sus llamadas individuales utilizando un nivel de aislamiento superior si necesita la integridad transaccional.

Definitivamente desea que READ_COMMITTED_SNAPSHOT esté activado, que no es el predeterminado. Eso te da semántica de MVCC. Es lo mismo que usa Oracle de forma predeterminada. Tener una base de datos MVCC es increíblemente útil, NO usar una es una locura. Esto le permite ejecutar lo siguiente dentro de una transacción:

Actualizar USUARIOS Establecer nombre = ''foobar''; // decido dormir por un año.

Mientras tanto, sin comprometer lo anterior, todos pueden continuar seleccionando de esa mesa sin problemas. Si no está familiarizado con MVCC, se sorprenderá de que haya podido vivir sin él. Seriamente.

El punto muerto típico de lectura / escritura proviene del acceso a la orden de índice. Read (T1) localiza la fila en el índice A y luego busca la columna proyectada en el índice B (generalmente agrupada). Escribir (T2) cambia el índice B (el clúster) y luego tiene que actualizar el índice A. T1 tiene S-Lck en A, quiere S-Lck en B, T2 tiene X-Lck en B, quiere U-Lck en A. Punto muerto , puff. T1 es asesinado. Esto es frecuente en entornos con mucho tráfico OLTP y un poco demasiados índices :). La solución es hacer que la lectura no tenga que saltar de A a B (es decir, la columna incluida en A, o eliminar la columna de la lista proyectada) o que T2 no tenga que saltar de B a A (no actualice la columna indexada). Desafortunadamente, linq no es tu amigo aquí ...

Entonces, ¿cuál es el problema con la implementación de un mecanismo de reintento? Siempre existirá la posibilidad de que se produzca un punto muerto, entonces ¿por qué no tener cierta lógica para identificarlo y solo intentarlo de nuevo?

¿Al menos algunas de las otras opciones no introducirán penalizaciones de rendimiento que se toman todo el tiempo cuando un sistema de reintento se activará en raras ocasiones?

Además, no olvides algún tipo de registro cuando se produce un reintento para que no te metas en esa situación de rara vez.

Establecer su valor predeterminado para leer no confirmado no es una buena idea. Sin duda, su voluntad presentará inconsistencias y terminará con un problema que es peor que el que tiene ahora. El aislamiento de instantáneas puede funcionar bien, pero es un cambio drástico en la forma en que funciona el servidor Sql y pone una gran carga en tempdb.

Esto es lo que debe hacer: use try-catch (en T-SQL) para detectar la condición de interbloqueo. Cuando sucede, simplemente vuelva a ejecutar la consulta. Esta es una práctica estándar de programación de bases de datos.

Hay buenos ejemplos de esta técnica en la Biblia Sql Server 2005 de Paul Nielson.

Aquí hay una plantilla rápida que uso:

-- Deadlock retry template declare @lastError int; declare @numErrors int; set @numErrors = 0; LockTimeoutRetry: begin try; -- The query goes here return; -- this is the normal end of the procedure end try begin catch set @lastError=@@error if @lastError = 1222 or @lastError = 1205 -- Lock timeout or deadlock begin; if @numErrors >= 3 -- We hit the retry limit begin; raiserror(''Could not get a lock after 3 attempts'', 16, 1); return -100; end; -- Wait and then try the transaction again waitfor delay ''00:00:00.25''; set @numErrors = @numErrors + 1; goto LockTimeoutRetry; end; -- Some other error occurred declare @errorMessage nvarchar(4000), @errorSeverity int select @errorMessage = error_message(), @errorSeverity = error_severity() raiserror(@errorMessage, @errorSeverity, 1) return -100 end catch;

Estoy de acuerdo con Jeremy en este caso. Usted pregunta si debe crear un nuevo contexto de datos para cada controlador o por página; tiendo a crear uno nuevo para cada consulta independiente.

Estoy construyendo una solución que solía implementar el contexto estático como lo hace, y cuando lancé toneladas de solicitudes por la bestia de un servidor (millones +) durante las pruebas de estrés, también recibía bloqueos de lectura / escritura aleatoriamente.

Tan pronto como cambié mi estrategia para usar un contexto de datos diferente en el nivel de LINQ por consulta, y confiaba en que el servidor SQL podría trabajar su magia de agrupación de conexiones, los bloqueos parecían desaparecer.

Por supuesto que estaba bajo presión de tiempo, por lo que intenté varias cosas al mismo tiempo, así que no puedo estar 100% seguro de que eso fue lo que lo solucionó, pero tengo un alto nivel de confianza. Digámoslo de esa manera .

La pregunta OP fue preguntar por qué ocurrió este problema. Este post espera responder a eso dejando al mismo tiempo posibles soluciones para que otros las solucionen.

Este es probablemente un problema relacionado con el índice. Por ejemplo, supongamos que la tabla Posts tiene un índice no agrupado X que contiene el ParentID y uno (o más) de los campos que se actualizan (AnswerCount, LastActivityDate, LastActivityUserId).

Se produciría un interbloqueo si el cmd SELECCIONAR hace un bloqueo de lectura compartida en el índice X para buscar por ParentId y luego necesita hacer un bloqueo de lectura compartida en el índice agrupado para obtener las columnas restantes mientras el cmd UPDATE realiza una escritura exclusiva bloquear el índice agrupado y necesita obtener un bloqueo exclusivo de escritura en el índice X para actualizarlo.

Ahora tiene una situación en la que A está bloqueada X y está tratando de obtener Y mientras que B bloqueó Y y está tratando de obtener X.

Por supuesto, necesitaremos el OP para actualizar su publicación con más información sobre qué índices están en juego para confirmar si esta es realmente la causa.

Me parece bien si mi perfil está incluso varios minutos desactualizado.

¿Estás volviendo a intentar leer después de que falla? Ciertamente es posible cuando se dispara una tonelada de lecturas aleatorias que unas pocas alcanzarán cuando no puedan leer. La mayoría de las aplicaciones con las que trabajo son muy pocas escritas en comparación con el número de lecturas y estoy seguro de que las lecturas no están cerca del número que se obtiene.

Si la implementación de "LEER SIN COMPROMISO" no resuelve su problema, entonces es difícil ayudar sin saber mucho más sobre el procesamiento. Puede haber alguna otra opción de ajuste que ayudaría a este comportamiento. A menos que algún gurú de MSSQL acuda al rescate, recomiendo enviar el problema al proveedor.

Me siento bastante incómodo con esta pregunta y las respuestas correspondientes. ¡Hay mucho "prueba este polvo mágico! ¡No ese polvo mágico!"

No puedo ver en ninguna parte que hayas analizado los bloqueos que se tomaron, y determiné qué tipo exacto de bloqueos están en punto muerto.

Todo lo que ha indicado es que se producen algunos bloqueos, no lo que está bloqueando.

En SQL 2005 puede obtener más información sobre qué bloqueos se están sacando mediante:

DBCC TRACEON (1222, -1)

para que cuando se produzca el punto muerto, tenga mejores diagnósticos.

P. ¿Por qué está almacenando AnswerCount en la tabla Posts en primer lugar?

Un enfoque alternativo consiste en eliminar el "write write" en la tabla Posts al no almacenar el AnswerCount en la tabla, sino calcular dinámicamente el número de respuestas a la publicación según sea necesario.

Sí, esto significará que está ejecutando una consulta adicional:

SELECT COUNT(*) FROM Answers WHERE post_id = @id

o más típicamente (si está visualizando esto para la página de inicio):

SELECT p.post_id, p.<additional post fields>, a.AnswerCount FROM Posts p INNER JOIN AnswersCount_view a ON <join criteria> WHERE <home page criteria>

pero esto normalmente resulta en una INDEX SCAN y puede ser más eficiente en el uso de los recursos que usar READ ISOLATION .

Hay más de una manera de despellejar a un gato. La des-normalización prematura de un esquema de base de datos puede presentar problemas de escalabilidad.

Tendría que estar de acuerdo con Greg siempre y cuando establecer el nivel de aislamiento para leer no comprometido no tenga ningún efecto negativo en otras consultas.

Me interesaría saber, Jeff, cómo la configuración en el nivel de la base de datos afectaría una consulta como la siguiente:

Begin Tran Insert into Table (Columns) Values (Values) Select Max(ID) From Table Commit Tran

Tuvo el mismo problema, y no puedo usar el "IsolationLevel = IsolationLevel.ReadUnCommitted" en TransactionScope porque el servidor no tiene DTS habilitado (!).

Eso es lo que hice con un método de extensión:

public static void SetNoLock(this MyDataContext myDS) { myDS.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"); }

Entonces, para los selectores que usan tablas de simultaneidad críticas, habilitamos el "nolock" de esta manera:

using (MyDataContext myDS = new MyDataContext()) { myDS.SetNoLock(); // var query = from ...my dirty querys here... }

¡Sugerencias son bienvenidas!

Una cosa que me ha funcionado en el pasado es asegurarme de que todas mis consultas y actualizaciones accedan a recursos (tablas) en el mismo orden.

Es decir, si una consulta se actualiza en orden Table1, Table2 y una consulta diferente la actualiza en orden de Table2, Table1, entonces puede ver deadlocks.

No estoy seguro de si es posible cambiar el orden de las actualizaciones ya que está utilizando LINQ. Pero es algo a mirar.

NOLOCK y READ UNCOMMITTED son una pendiente resbaladiza. Nunca debes usarlos a menos que entiendas por qué el punto muerto está ocurriendo primero. Me preocuparía que diga: "Hemos agregado (nolock) a todas las consultas SQL". La necesidad de agregar WITH NOLOCK en todas partes es una señal segura de que tienes problemas en tu capa de datos.

La declaración de actualización en sí misma parece un poco problemática. ¿Determina el conteo antes en la transacción, o simplemente lo extrae de un objeto? AnswerCount = AnswerCount+1 cuando se agrega una pregunta es probablemente una mejor manera de manejar esto. Entonces no necesita una transacción para obtener el recuento correcto y no tiene que preocuparse por el problema de concurrencia al que potencialmente se está exponiendo.

Una manera fácil de evitar este tipo de problema de interbloqueo sin mucho trabajo y sin habilitar lecturas sucias es usar el "Snapshot Isolation Mode" (nuevo en SQL 2005) que siempre le dará una lectura clara de los últimos datos no modificados. También puede atrapar y volver a intentar declaraciones bloqueadas con bastante facilidad si desea manejarlas con gracia.