|
|
Publicado en 20/7/2016 12:37:53
|
|
|
Resumen de las esclusas
1. Por qué introducir cerraduras
Cuando varios usuarios realizan operaciones simultáneas en la base de datos, se producen las siguientes inconsistencias en los datos:
Actualizaciones perdidas
Dos usuarios, A y B, leen los mismos datos y los modifican, y el resultado de la modificación de un usuario destruye el resultado de la otra modificación, como el sistema de reserva de billetes
Lectura sucia
El usuario A modifica los datos, y luego el usuario B los lee, pero el usuario A cancela la modificación por alguna razón, y los datos vuelven a su valor original
No leas repetidamente
El usuario A lee los datos, y luego el usuario B los lee y los modifica
El principal método de control de concurrencia es el bloqueo, que consiste en prohibir a los usuarios realizar ciertas operaciones durante un periodo de tiempo para evitar inconsistencias en los datos
2. Clasificación de esclusas
Hay dos divisiones en categorías de esclusas:
1 . Desde la perspectiva del sistema de bases de datos: se divide en bloqueos exclusivos (es decir, bloqueos exclusivos), bloqueos compartidos y bloqueos de actualización
MS - SQL Server utiliza los siguientes patrones de bloqueo de recursos.
Descripción del modo de bloqueo
Share(s) se utiliza para operaciones que no cambian ni actualizan datos (operaciones de solo lectura), como las sentencias SELECT.
Update (U) se utiliza en recursos actualizables. Previene formas comunes de bloqueos cuando se leen varias sesiones, se bloquean y posiblemente puede producirse una actualización de recursos.
Exclusivo (X) se utiliza para operaciones de modificación de datos, como INSERT, UPDATE o DELETE. Asegúrate de que no se realicen múltiples actualizaciones en el mismo recurso al mismo tiempo.
Los candados de intención se utilizan para establecer una jerarquía de cerraduras. Los tipos de bloqueos de intención son: Intención Compartida (IS), Exclusiva de Intención (IX) y Exclusiva de Intención (SEIS).
Los bloqueos de esquema se utilizan al realizar operaciones que dependen del esquema de la tabla. Los tipos de bloqueos de esquema son: modificación de esquema (Sch -M) y estabilidad de esquema (Sch -S).
Las actualizaciones masivas (BUs) se utilizan cuando se copian grandes volúmenes de datos a una tabla y se especifica una pista TABLOCK.
Cerraduras compartidas
Un bloqueo compartido(s) permite que transacciones concurrentes lean (SELECCIONEN) un recurso. Cuando existe un bloqueo compartido (S) en un recurso, ninguna otra transacción puede modificar los datos. Libera el bloqueo compartido (S) en el recurso tan pronto como se hayan leído los datos, a menos que el nivel de aislamiento de la transacción esté configurado como repetible o superior, o el bloqueo compartido (S) se mantenga con una pista de bloqueo durante toda la vida útil de la transacción.
Actualizar el bloqueo
Los bloqueos de actualización (U) evitan los bloqueos en su forma habitual. Un patrón típico de actualización consiste en una transacción que lee un registro, obtiene un bloqueo compartido (S) para un recurso (página o fila) y luego modifica una fila, lo que requiere que el bloqueo se convierta en un bloqueo exclusivo (X). Si dos transacciones adquieren un bloqueo en modo compartido en un recurso y luego intentan actualizar los datos al mismo tiempo, una transacción intenta convertir el bloqueo en un bloqueo exclusivo (X). La transición del modo compartido al bloqueo exclusivo debe esperar un tiempo porque el bloqueo exclusivo de una transacción no es compatible con el bloqueo en modo compartido de otra transacción; Se produce una espera de esclusa. La segunda transacción intenta obtener un bloqueo exclusivo (X) para una actualización. Se produce un bloqueo porque ambas transacciones se convierten en bloqueos exclusivos (X), y cada transacción espera a que la otra libere el bloqueo en modo compartido.
Para evitar este posible problema de bloqueo, utiliza un candado actualizado (U). Solo una transacción a la vez puede obtener un bloqueo (U) actualizado para un recurso. Si la transacción modifica el recurso, el bloqueo de actualización (U) se convierte en un bloqueo exclusivo (X). De lo contrario, la cerradura se convierte en una cerradura compartida.
Cerraduras exclusivas
Los bloqueos exclusivos (X) impiden que las transacciones concurrentes accedan a los recursos. Otras transacciones no pueden leer ni modificar los datos bloqueados por el bloqueo exclusivo (X).
Bloqueo de intención
Un bloqueo de intención indica que SQL Server necesita adquirir un bloqueo compartido (S) o un bloqueo exclusivo (X) sobre algunos de los recursos subyacentes de la jerarquía. Por ejemplo, un bloqueo de intención de compartir colocado a nivel de tabla indica que la transacción pretende colocar un bloqueo de acción(S) en una página o fila de la tabla. Establecer un bloqueo de intención a nivel de tabla impide que otra transacción adquiera posteriormente un bloqueo exclusivo (X) en la tabla que contiene esa página. Los bloqueos de intención pueden mejorar el rendimiento porque SQL Server solo verifica el bloqueo de intención a nivel de tabla para determinar si una transacción puede adquirir un bloqueo de forma segura en esa tabla. En lugar de comprobar los bloqueos en cada fila o página de la tabla para determinar si una transacción puede bloquear toda la tabla.
Los bloqueos de intención incluyen el Intercambio de Intención (IS), el Exclusivo de Intención (IX) y el Intercambio Exclusivo de Intención (SEIS).
Descripción del modo de bloqueo
El Intercambio de Intención (IS) indica que la intención de la transacción es algunos, no todos, los recursos subyacentes en la jerarquía de lectura al colocar bloqueos S en cada recurso.
Exclusivo de Intención (IX) indica que la intención de la transacción es modificar algunos, pero no todos, los recursos subyacentes en la jerarquía colocando un X-lock en cada recurso. IX es un superconjunto de IS.
El compartir exclusivo con intención (SEIS) indica que la intención de la transacción es leer todos los recursos subyacentes en la jerarquía y modificar algunos, pero no todos, de los recursos subyacentes colocando bloqueos IX en cada recurso. Permite bloqueos IS concurrentes en recursos de nivel superior. Por ejemplo, el bloqueo SIX de una tabla coloca un bloqueo SIX en la tabla (permitiendo bloqueos IS concurrentes) y un bloqueo IX en la página modificada actualmente (un bloqueo X en la fila modificada). Mientras que cada recurso solo puede tener un bloqueo SIX durante un periodo de tiempo para evitar que otras transacciones actualicen el recurso, otras transacciones pueden leer los recursos subyacentes en la jerarquía adquiriendo bloqueos IS a nivel de tabla.
Bloqueo exclusivo: Solo el programa que realiza la operación de bloqueo puede utilizarlo, y no se aceptarán otras operaciones sobre él. Cuando ejecutas un comando de actualización de datos, SQL Server utiliza automáticamente un bloqueo exclusivo. Cuando existen otros candados en un objeto, no puedes añadirle un bloqueo exclusivo.
Bloqueo compartido: El recurso bloqueado por el bloqueo compartido puede ser leído por otros usuarios, pero otros usuarios no pueden modificarlo.
Bloqueo de actualización: Cuando SQL Server está listo para actualizar datos, primero bloquea el objeto de datos para que los datos no puedan modificarse pero sí leerse. Cuando SQL Server determina que quiere actualizar datos, reemplazará automáticamente el bloqueo de actualización por un bloqueo exclusivo, y no podrá añadirle un bloqueo de actualización cuando existen otros bloqueos en el objeto.
2 . Desde el punto de vista del programador: se divide en bloqueo optimista y bloqueo pesimista.
Candado Optimism: Depende completamente de la base de datos para gestionar el funcionamiento de la cerradura.
Bloqueos pesimistas: Los programadores gestionan el manejo de bloqueos sobre datos u objetos.
MS - SQLSERVER utiliza bloqueos para implementar un control pesimista de concurrencia entre varios usuarios que realizan modificaciones en la base de datos al mismo tiempo
3. El tamaño de partículas de la cerradura
La granularidad del bloqueo es el tamaño del objetivo bloqueado, la granularidad de bloqueo pequeña es una concurrencia alta, pero la sobrecarga es grande, y la granularidad de bloqueo grande es una concurrencia baja, aunque la sobrecarga es pequeña
SQL Server soporta la granularidad de bloqueos para filas, páginas, claves, rangos de claves, índices, tablas o bases de datos
Descripción del recurso
Identificador de fila RID. Se usaba para bloquear una fila en una tabla individualmente.
Bloqueo de fila de llaves en el índice. Se utiliza para proteger el rango de claves en transacciones serializables.
8 kilobytes (KB) de páginas de datos o páginas de índice.
Disco Extendido Un conjunto de ocho páginas de datos o páginas de índice adyacentes.
Tabla La tabla completa incluyendo todos los datos e índices.
Base de datos de datos.
4. La duración del tiempo de confinamiento
El tiempo que una esclusa permanece es el tiempo necesario para proteger el recurso en el nivel solicitado.
El tiempo de retención del bloqueo compartido utilizado para proteger las operaciones de lectura depende del nivel de aislamiento de la transacción. Con el nivel de aislamiento predeterminado de transacciones de READ COMMITTED, el bloqueo compartido solo se controla durante la duración de la página de lectura. En un escaneo, el candado no se libera hasta que se adquiere en la siguiente página dentro del escaneo. Si especificas un aviso HOLDLOCK o configuras el nivel de aislamiento de la transacción en LECTURA REPETIBLE o SERIALIZABLE, el bloqueo no se libera hasta que la transacción termina.
Dependiendo de la opción de concurrencia establecida para el cursor, este puede adquirir un bloqueo de desplazamiento en modo compartido para proteger la extracción. Cuando se requiere un cierre de desplazamiento, este no se libera hasta la próxima vez que se extrae o cierra el cursor, lo que ocurra primero. Sin embargo, si especificas un HOLDLOCK, el scroll lock no se libera hasta el final de la transacción.
El bloqueo exclusivo utilizado para proteger la actualización no se liberará hasta el final de la transacción.
Si una conexión intenta adquirir un bloqueo que entra en conflicto con un bloqueo controlado por otra conexión, la conexión que intenta adquirir el bloqueo será bloqueada hasta que:
Se libera el bloqueo en conflicto y la conexión adquiere el bloqueo solicitado.
El tiempo de espera de la conexión ha expirado. Por defecto no hay intervalo de tiempo de espera, pero algunas aplicaciones establecen intervalos de tiempo de espera para evitar la espera indefinida
Cinco personalizaciones de los candados en SQL Server
1 Gestionar los bloqueos y establecer prioridades de bloqueo
El bloqueo es la espera interminable causada por varios usuarios que solicitan diferentes bloqueos, porque el solicitante tiene parte del derecho de bloqueo y espera el bloqueo parcial que posee otros usuarios
Puedes usar el DEADLOCK_PRIORITY SET para controlar cómo reacciona la sesión en caso de un bloqueo de situación. Si ambos procesos bloquean los datos y cada proceso no puede liberar su propio bloqueo hasta que el otro proceso lo libere, se produce una situación de bloqueo muerto.
2 Gestionar los tiempos de espera y establecer duraciones de tiempo de espera para los bloqueos.
@@LOCK_TIMEOUT Devuelve el ajuste de tiempo de espera actual para la sesión actual en milisegundos
La configuración SET LOCK_TIMEOUT permite a la aplicación establecer el tiempo máximo que la instrucción espera para bloquear el recurso. Cuando el tiempo de espera de la declaración es mayor que la configuración de LOCK_TIMEOUT, el sistema cancela automáticamente la sentencia de bloqueo y devuelve a la aplicación un mensaje de error 1222 indicando que se ha excedido el periodo de tiempo de espera de la solicitud de bloqueo
ejemplo
En el siguiente ejemplo, el periodo de tiempo de espera del bloqueo se establece en 1.800 milisegundos.
SET LOCK_TIMEOUT1800
3) Establecer el nivel de aislamiento de la transacción.
4) Utilizar pistas de bloqueo a nivel de tabla para las sentencias SELECT, INSERT, UPDATE y DELETE.
5) Configurar la granularidad de bloqueo del índice
Puedes usar sp_indexoption procedimientos almacenados del sistema para establecer la granularidad del bloqueo para la indexación
6. Ver la información de la cerradura
1 Realizar EXEC SP_LOCK informar información sobre la cerradura
2 Pulsa Ctrl + 2 en el analizador de consultas para ver la información del bloqueo
7. Precauciones para su uso
Cómo evitar los bloqueos
1. Al utilizar transacciones, intenta acortar el proceso lógico de procesamiento de las transacciones y presenta o reverte las transacciones con antelación.
2 Establecer el parámetro de tiempo de espera en un rango razonable, como: 3 minutos - 10 minutos; Tras ese tiempo, la operación se abandona automáticamente para evitar que el proceso se quede colgado;
3. Optimizar el programa, comprobar y evitar el fenómeno de bloqueo;
4. Prueba cuidadosamente todos los scripts y SPs antes de la versión exacta.
5 Todos los SP deben tener manejo de errores (vía @error)
6 No modifique el nivel predeterminado de las transacciones de SQL SERVER. No se recomienda el bloqueo forzado
Resuelve el problema Cómo bloquear una base de datos de tablas de filas
8. Varias preguntas sobre cerraduras
1 Cómo bloquear una fila de una mesa
ESTABLECER TRANSACTIONISOLATION LEVEL READUNCOMMITTED
SELECT *FROM table ROWLOCKWHEREWHERE id = 1
2 Bloquear una tabla en la base de datos
SELECT *FROM tabla CON( HOLD LOCK )
Declaración de bloqueo:
sybase:
Conjunto de tabla de actualización col1 = col1 donde 1= 0 ;
MSSQL:
selecciona col1de la tabla (tablockx) donde 1= 0 ;
oracle:
TABLA DE BLOQUEOS EN MODO EXCLUSIVO ;
Después de bloquear el bloqueo, nadie más puede operarlo hasta que el usuario bloqueado lo desbloquee, y se desbloquea con compromiso o retroceso
Algunos ejemplos te ayudarán a profundizar tu impresión
Mesa de preparación1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1) Bloqueo exclusivo
Crear dos nuevas conexiones
Ejecuta la siguiente sentencia en la primera conexión
Inicio de la transición
Tabla de actualización1
conjunto A= ' aa '
donde B= ' b2 '
espera retraso' 00:00:30' --espera 30 segundos
commit tran
Ejecuta la siguiente sentencia en la segunda conexión
Inicio de la transición
select *de la tabla1
donde B= ' b2 '
commit tran
Si las dos sentencias anteriores se ejecutan al mismo tiempo, la consulta select debe esperar a que se ejecute la actualización, es decir, esperar 30 segundos
2) Cerradura compartida
Ejecuta la siguiente sentencia en la primera conexión
Inicio de la transición
select *de table1 bloqueo de retención - El bloqueo de retención se añade artificialmente al candado
donde B= ' b2 '
espera retraso' 00:00:30' --espera 30 segundos
commit tran
Ejecuta la siguiente sentencia en la segunda conexión
Inicio de la transición
seleccione A, C de la tabla 1
donde B= ' b2 '
Tabla de actualización1
conjunto A= ' aa '
donde B= ' b2 '
commit tran
Si las dos sentencias anteriores se ejecutan al mismo tiempo, se puede ejecutar la consulta select en la segunda conexión
La actualización debe esperar a que la primera transacción libere el bloqueo compartido y convertirlo en un bloqueo exclusivo antes de poder ejecutarse, es decir, esperar 30 segundos
3) Empate
Tabla añadida2(D,E)
D E
d1 e1
d2 e2
Ejecuta la siguiente sentencia en la primera conexión
Inicio de la transición
Tabla de actualización1
conjunto A= ' aa '
donde B= ' b2 '
espera la demora' 00:00:30'
Tabla de actualización2
set D= ' d5'
donde E= ' e1 '
commit tran
Ejecuta la siguiente sentencia en la segunda conexión
Inicio de la transición
Tabla de actualización2
set D= ' d5'
donde E= ' e1 '
espera retraso' 00:00:10'
Tabla de actualización1
conjunto A= ' aa '
donde B= ' b2 '
commit tran
Al mismo tiempo, el sistema detecta el bloqueo y aborta el proceso
Para añadir:
Consejos de bloqueo a nivel de tabla soportados por SQL Server 2000
HOLDLOCK mantiene el bloqueo compartido hasta que se complete toda la transacción y debe liberarse tan pronto como el objeto bloqueado no sea necesario, igual al nivel de aislamiento de transacciones SERIALIZABLE
La instrucción NOLOCK se ejecuta sin emitir un bloqueo compartido, permitiendo lecturas sucias, que es igual al nivel de aislamiento de transacciones READ UNCOMMITTED
PAGLOCK utiliza múltiples bloqueos de página donde se usa un bloqueo de tabla
READPAST permite al servidor SQL saltar líneas bloqueadas y ejecutar transacciones, y para niveles de aislamiento de transacciones READ NO comprometidos, solo saltar bloqueos de RID, no bloqueos de página, zona y tabla
ROWLOCK impone el uso de rowlocks
TABLOCKX impone el uso de un bloqueo exclusivo a nivel de tabla, que impide que cualquier otra transacción utilice la tabla durante la transacción
UPLOCK obliga a usar actualizaciones al leer una tabla sin un bloqueo compartido
Bloqueo de la aplicación:
Un bloqueo de aplicación es un bloqueo generado por el código del cliente, no un bloqueo generado por SQL Server en sí
Dos procesos para gestionar bloqueos de aplicación
sp_getapplock Bloquear recursos de la aplicación
sp_releaseapplock Desbloquear los recursos de la aplicación
Nota: La diferencia entre bloquear una tabla en una base de datos
SELECT *FROM tabla WITH( HOLDLOCK ) Otras transacciones pueden leer la tabla, pero no pueden actualizarlas ni eliminar
SELECT *FROM tabla WITH(TABLOCKX) Otras transacciones no pueden leer, actualizar ni eliminar la tabla
|
Anterior:No había ningún punto final que escuchara en http://localhost:111/xxx.svc que c...Próximo:Bloqueos SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
