Usando la instrucción SELECT con una instrucción INSERT o UPDATE en curso , coloque un bloqueo exclusivo en las filas o posiblemente en toda la tabla, hasta que la transacción de la operación se confirme o revierta. Suponga que está trabajando en una tabla muy grande con miles de filas y la tabla de la base de datos no está diseñada de manera eficiente. Recuerde, en la vida real, usted no es el único que trabaja con una base de datos, es posible que se acceda a esa base de datos en los dispositivos de muchos usuarios al mismo tiempo. Por lo tanto, si está leyendo la tabla usando la declaración SELECT y alguien más está intentando ejecutar la declaración INSERT, entonces puede ocurrir un bloqueo y las dos transacciones se bloquean entre sí.
Entonces, la solución en SQL Server es que la sugerencia de tabla NOLOCK le permite indicar al optimizador de consultas que lea una tabla determinada sin obtener un bloqueo exclusivo o compartido.
Paso 1: crear la base de datos usando la base de datos
Consulta:
CREATE DATABASE GFG_Demo; USE GFG_Demo;
Producción:
Paso 2: Definición de la tabla
Tenemos la siguiente tabla de demostración en nuestra base de datos GFG_Demo.
Consulta:
CREATE Table GFG_Demo_Table ( Order_date date, Sales int);
Producción:
Paso 3: agregar datos a la tabla
Use la siguiente declaración para agregar datos a GFG_Demo_Table.
Consulta:
INSERT INTO GFG_Demo_Table(Order_date,Sales)
VALUES('2021-01-01',20),('2021-03-02',32),('2021-02-03',45),
('2021-01-04',31),('2021-03-05',33),('2021-01-06',19),
('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45),('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-01',20),('2021-03-02',32),
('2021-02-03',45), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-01-04',31),('2021-03-05',33),
('2021-01-06',19), ('2021-04-07',21),('2021-03-08',10),
('2021-02-09',40), ('2021-03-10',20),('2021-03-11',26),
('2021-04-12',22), ('2021-04-13',10),('2021-01-14',28),
('2021-03-15',15), ('2021-01-16',12),('2021-04-17',10),
('2021-02-18',18), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-03-22',51),('2021-02-23',13),
('2021-03-24',15), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-02-25',30),('2021-03-26',14),
('2021-04-27',16), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-04-19',14),('2021-01-20',16),
('2021-02-21',12), ('2021-02-28',15),('2021-01-29',20),
('2021-01-30',18);
Producción:
Paso 4: Uso de NOLOCK
Ahora que nuestra tabla está lista, hagamos una prueba y veamos cómo es útil NOLOCK.
Consulta:
BEGIN TRAN UPDATE GFG_Demo_Table SET Sales= 25 WHERE Sales=20;
Paso 5: La instrucción BEGIN TRAN iniciará la transacción que iniciará la transacción que ejecutará la siguiente instrucción UPDATE en la tabla GFG_Demo_Table en la sesión de SQL número 52 (podría ser diferente en nuestro sistema), sin finalizar la transacción mediante confirmación o ejecución de vuelta
Consulta:
SELECT * FROM GFG_Demo_Table;
Paso 6: cuando ejecuta esta consulta, puede notar que la declaración SELECT está tardando un poco más de lo habitual. La diferencia es más notable en tablas más grandes. Prueba esto, con miles de filas. Es porque la transacción aún no se ha comprometido ni revertido. Es por eso que está bloqueando otras consultas que intentan leer los datos de la tabla. Puede verificar eso usando el siguiente comando.
Consulta:
sp_who2 52
Nota: Para esta tabla en particular, la declaración SELECT no está bloqueada por mucho tiempo ya que la tabla no es tan grande, pero en el caso de una tabla con miles de datos, puede ver el número de sesión de la consulta que está bloqueando el instrucción SELECT ejecutando el comando anterior.
Paso 7: para ejecutar la consulta bloqueada, debe eliminar , confirmar o revertir la transacción. Sin embargo, esta no es la solución más preferible. Aquí es donde CON (NOLOCK) entra en escena.
Consulta:
SELECT * FROM GFG_Demo_Table WITH (NOLOCK);
Ahora, ejecute la consulta anterior y luego verifique el estado de la declaración SELECT usando el comando anterior.
Producción:
Paso 8: La columna BlkBy está en blanco, lo que significa que el bloqueo se eliminó ahora. También puede usar WITH (READUNCOMMITTED) , hace lo mismo que WITH (NOLOCK). También puede leer los datos no confirmados, sin esperar a que la instrucción UPDATE libere el bloqueo.
Producción:
Nota: solo puede usar estas sugerencias de tabla con la declaración SELECT , no con ninguna otra declaración.
Método 2:
En lugar de utilizar las sugerencias de tabla que permiten lecturas sucias en el nivel de consulta, puede cambiar el nivel de aislamiento de la transacción en el nivel de conexión para que sea LECTURA NO COMPROMETIDA mediante la instrucción SET TRANSACTION ISOLATION LEVEL .
Consulta:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM GFG_Demo_Table;
Producción:
Esta consulta también recuperará los mismos datos directamente, sin usar ninguna sugerencia de tabla y sin esperar a que la instrucción UPDATE libere el bloqueo que realizó en la tabla.
Inconvenientes de usar NOLOCK
- Usar NOLOCK puede parecer una buena idea al principio, ya que obtenemos los datos solicitados más rápido sin esperar a que se confirme la otra operación. Sin embargo, la lectura sucia es un gran problema, el resultado que obtenemos después de eso puede no ser preciso. Por lo tanto, agregar automáticamente sugerencias de NOLOCK generalmente no es una buena práctica en un entorno de procesamiento de transacciones en línea (OLTP) .
- Agregar la sugerencia NOLOCK o cambiar el nivel de aislamiento cambia la forma en que se maneja el bloqueo para una consulta individual o para todos los comandos en la sesión. Pruebe estos cambios a fondo para comprobar si se ajustan a sus necesidades.