Comprensión de LOCKS de SQL Server en consultas SELECT

Me pregunto cuál es el beneficio de usar SELECT WITH (NOLOCK) en una tabla si las únicas otras consultas que afectan a esa tabla son las consultas SELECT .

¿Cómo es manejado por SQL Server? ¿Una consulta SELECT bloquearía otra consulta SELECT ?

Estoy usando SQL Server 2012 y un DataContext Linq-to-SQL.

(EDITAR)

Sobre el rendimiento:

  • ¿Tendría que esperar un 2do SELECT para que un 1er SELECT termine si usa un SELECT bloqueado?
  • ¿Versus a SELECT WITH (NOLOCK) ?

Gracias.

Un SELECT en SQL Server colocará un locking compartido en una fila de la tabla, y un segundo SELECT también requerirá un locking compartido, y esos son compatibles entre sí.

Entonces, ningún SELECT puede bloquear otro SELECT .

Para lo que se utiliza la sugerencia de consulta WITH (NOLOCK) es para poder leer datos que están en proceso de ser insertados (por otra conexión) y que aún no se han confirmado.

Sin esa sugerencia de consulta, una SELECT INSERT (o UPDATE ) podría bloquear un SELECT leyendo una tabla que coloca un locking exclusivo en las filas (o posiblemente en toda la tabla), hasta que la transacción de esa operación se haya confirmado (o revertido).

El problema de la WITH (NOLOCK) es: es posible que esté leyendo filas de datos que no se van a insertar, al final (si la transacción INSERT se retrotrae), por lo que su informe podría mostrar datos que nunca son realmente correctos. estado comprometido con la base de datos.

Hay otra sugerencia de consulta que podría ser útil: WITH (READPAST) . Esto instruye al comando SELECT para omitir cualquier fila que intente leer y que esté bloqueada exclusivamente. El SELECT no se bloqueará y no leerá ningún dato “sucio” no comprometido, pero podría omitir algunas filas, por ejemplo, no mostrar todas las filas en la tabla.

En cuanto al rendimiento, sigues centrándote en seleccionar.
Shared no bloquea las lecturas.
Actualización de bloque de locking compartido.
Si tiene cientos de lockings compartidos, llevará una actualización un tiempo para obtener un locking exclusivo, ya que debe esperar a que se liberen los lockings compartidos.

Por defecto, seleccionar (leer) toma un locking compartido.
Los lockings compartidos (S) permiten que las transacciones concurrentes lean (SELECCIONE) un recurso.
Un locking compartido como ningún efecto en otro selecciona (1 o un 1000).

La diferencia es cómo actualizar nolock versus locking compartido o operación de inserción.

Ninguna otra transacción puede modificar los datos mientras existen lockings compartidos (S) en el recurso.

¡Un locking compartido bloquea una actualización!
Pero nolock no bloquea una actualización.

Esto puede tener un gran impacto en el rendimiento de las actualizaciones. También impacta insertos.

Dirty read (nolock) suena sucio. Nunca obtendrás datos parciales. Si una actualización cambia a John a Sally, nunca lo conseguirás.

Uso lockings compartidos mucho para concurrencia. Los datos están desactualizados tan pronto como se leen. Una lectura de John que cambia a Sally en el siguiente milisegundo es información obsoleta. Una lectura de Sally que se revierte John en el siguiente milisegundo son datos obsoletos. Eso es en el nivel de milisegundo. Tengo un cargador de datos que demora 20 horas para ejecutarse si los usuarios toman lockings compartidos y 4 horas para ejecutarlo, ya que los usuarios no tienen locking. Los lockings compartidos en este caso hacen que los datos estén atascados 16 horas.

No use nolocks mal. Pero ellos tienen un lugar. Si va a cortar un cheque cuando un byte se establece en 1 y luego lo establece en 2 cuando se corta el cheque, no es un momento para un nolock.

En mi trabajo, tenemos un sistema muy grande que se ejecuta en muchas PC al mismo tiempo, con tablas muy grandes con cientos de miles de filas y, a veces, muchos millones de filas.

Cuando realiza un SELECTO en una tabla muy grande, digamos que quiere saber cada transacción que un usuario ha realizado en los últimos 10 años, y la clave principal de la tabla no está construida de manera eficiente, la consulta puede tardar varios minutos. correr.

Entonces, nuestra aplicación me podría ejecutar en muchas PC de usuario al mismo tiempo, accediendo a la misma base de datos. Entonces, si alguien intenta insertar en la tabla que está leyendo el otro SELECT (en páginas que SQL está tratando de leer), entonces puede ocurrir un BLOQUEO y las dos transacciones se bloquean entre sí.

Tuvimos que agregar un “SIN BLOQUEO” a nuestra instrucción SELECCIONAR, porque era una SELECCIÓN enorme en una mesa que muchos usuarios usaban mucho al mismo tiempo y teníamos CERROJOS todo el tiempo.

No sé si mi ejemplo es lo suficientemente claro? Este es un ejemplo de la vida real.

Tengo que agregar un comentario importante. Todos mencionan que NOLOCK solo lee datos sucios. Esto no es preciso También es posible que obtenga la misma fila dos veces o que toda la fila se omita durante la lectura. La razón es que puede solicitar algunos datos al mismo tiempo cuando SQL Server está reequilibrando b-tree.

Verificar otros hilos

https://stackoverflow.com/a/5469238/2108874

http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx )

Con la sugerencia de NOLOCK (o establecer el nivel de aislamiento de la sesión para LEER SIN COMPROMISO), le dice a SQL Server que no espera consistencia, por lo que no hay garantías. Tenga en cuenta que los “datos inconsistentes” no solo significan que es posible que vea cambios no confirmados que luego se retrotraeron, o cambios de datos en un estado intermedio de la transacción. También significa que en una consulta simple que escanea todos los datos de tabla / índice, SQL Server puede perder la posición de escaneo, o puede terminar obteniendo la misma fila dos veces.

SELECT WITH (NOLOCK) permite lecturas de datos no confirmados, lo que equivale a tener el nivel de aislamiento READ UNCOMMITTED establecido en su base de datos. La palabra clave NOLOCK permite un control más detallado que establecer el nivel de aislamiento en toda la base de datos.

Wikipedia tiene un artículo útil: Wikipedia: Aislamiento (sistemas de bases de datos)

También se discute extensamente en otros artículos de stackoverflow.

seleccionar sin locking – seleccionará registros que pueden o no insertarse. leerás datos sucios.

por ejemplo, digamos que una transacción inserta 1000 filas y luego falla.

cuando selecciones, obtendrás las 1000 filas.