una - ultimos 15 registros sql
Forzar a Oracle a devolver las filas TOP N con SKIP LOCKED (5)
Hay few questions sobre cómo implementar una tabla similar a una cola (bloquear filas específicas, seleccionar un número determinado de ellas y omitir filas actualmente bloqueadas) en Oracle y SQL Server.
¿Cómo puedo garantizar que recupero un número determinado de filas ( N ), asumiendo que hay al menos N filas elegibles?
Por lo que he visto, Oracle aplica el predicado WHERE antes de determinar qué filas omitir. Esto significa que si quiero extraer una fila de una tabla y dos subprocesos ejecutan simultáneamente el mismo SQL, uno recibirá la fila y el otro un conjunto de resultados vacío (incluso si hay más filas elegibles).
Esto es contrario a cómo SQL Server parece manejar las sugerencias de bloqueo READPAST , ROWLOCK y READPAST . En SQL Server, parece que TOP mágicamente limita el número de registros después de alcanzar con éxito los bloqueos.
Tenga en cuenta, dos artículos interesantes here y here .
ORÁCULO
CREATE TABLE QueueTest (
ID NUMBER(10) NOT NULL,
Locked NUMBER(1) NULL,
Priority NUMBER(10) NOT NULL
);
ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY (ID);
CREATE INDEX IX_QueuePriority ON QueueTest(Priority);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (1, NULL, 4);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (2, NULL, 3);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (3, NULL, 2);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (4, NULL, 1);
En dos sesiones separadas, ejecute:
SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
SELECT ID
FROM
(SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED
Tenga en cuenta que la primera devuelve una fila y la segunda sesión no devuelve una fila:
Sesión 1
ID ---- 4
Sesion 2
ID ----
SERVIDOR SQL
CREATE TABLE QueueTest (
ID INT IDENTITY NOT NULL,
Locked TINYINT NULL,
Priority INT NOT NULL
);
ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY NONCLUSTERED (ID);
CREATE INDEX IX_QueuePriority ON QueueTest(Priority);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 4);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 3);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 2);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 1);
En dos sesiones separadas, ejecute:
BEGIN TRANSACTION
SELECT TOP 1 qt.ID
FROM QueueTest qt
WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE Locked IS NULL
ORDER BY Priority;
Tenga en cuenta que ambas sesiones devuelven una fila diferente.
Sesión 1
ID ---- 4
Sesion 2
ID ---- 3
¿Cómo puedo obtener un comportamiento similar en Oracle?
"Por lo que he visto, Oracle aplica el predicado WHERE antes de determinar qué filas omitir".
Sip. Es la única manera posible. No puede omitir una fila de un conjunto de resultados hasta que haya determinado el conjunto de resultados.
La respuesta es simplemente no limitar el número de filas devueltas por la instrucción SELECT. Aún puede utilizar las sugerencias FIRST_ROWS_n para indicar al optimizador que no capturará el conjunto completo de datos.
El software que llama a SELECT solo debe seleccionar las primeras n filas. En PL / SQL, sería
DECLARE
CURSOR c_1 IS
SELECT /*+FIRST_ROWS_1*/ qt.ID
FROM QueueTest qt
WHERE Locked IS NULL
ORDER BY PRIORITY
FOR UPDATE SKIP LOCKED;
BEGIN
OPEN c_1;
FETCH c_1 into ....
IF c_1%FOUND THEN
...
END IF;
CLOSE c_1;
END;
En tu primera sesión, cuando ejecutas:
SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
SELECT ID
FROM
(SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED
Tu intento interno de selección para agarrar solo id = 4 y bloquearlo. Esto tiene éxito porque esta única fila aún no está bloqueada.
En la segunda sesión, su selección interior TODAVÍA intenta agarrar SOLAMENTE id = 4 y bloquearlo. Esto no tiene éxito porque esa fila individual todavía está bloqueada por la primera sesión.
Ahora, si actualizó el campo "bloqueado" en la primera sesión, la próxima sesión para ejecutar esa selección tomará id = 3.
Básicamente, en su ejemplo usted depende de una bandera que no se está configurando. Para usar tu bandera bloqueada, probablemente quieras hacer algo como:
- Seleccione las identificaciones que desee en función de algunos criterios.
- Actualice de inmediato la marca de bloqueo = 1 para estas ID (si el recurso está ocupado, otra sesión lo superará en este paso para 1 o más ID, vuelva a 1)
- Haz lo que sea en estas identificaciones
- actualizar la bandera bloqueada de nuevo a nulo
A continuación, puede utilizar su selección para omitir la actualización de la declaración bloqueada ya que se mantiene su marca bloqueada.
Personalmente, no me gustan todas las actualizaciones de las marcas (su solución puede requerirlas por alguna razón), así que probablemente solo intente seleccionar las ID que quiero actualizar (según el criterio) en cada sesión:
seleccione * de queuetest donde ... para actualización saltar bloqueado;
Por ejemplo (en realidad, mi criterio no se basaría en una lista de identificadores, pero la tabla queuetest es demasiado simplista):
sess 1: seleccione * de queuetest donde id en (4,3) para la actualización se salta bloqueado;
sess 2: seleccione * de queuetest donde id en (4,3,2) para la actualización se salta bloqueado;
Aquí sess1 bloquearía 4,3 y sess2 bloquearía solo 2.
No puedo, por lo que sé, hacer un top-n o usar group_by / order_by, etc en la selección para el estado de actualización, obtendrá un ORA-02014.
La solución que Gary Meyers publicó es sobre todo lo que puedo pensar, aparte de usar AQ, que hace todo esto por usted y mucho más.
Si realmente desea evitar el PLSQL, debería poder traducir el PLSQL a las llamadas JDBC de Java. Todo lo que necesita hacer es preparar la misma instrucción SQL, ejecutarla y luego continuar con las recuperaciones de una sola fila (o las recuperaciones de N filas).
La documentación de Oracle en http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642 da una idea de cómo hacerlo a nivel de declaración:
Para establecer el tamaño de búsqueda para una consulta, llame a setFetchSize () en el objeto de la declaración antes de ejecutar la consulta. Si establece el tamaño de recuperación en N, entonces se recuperan N filas con cada viaje a la base de datos.
Así que podrías codificar algo en Java que se parece a algo como (en Pseudo código):
stmt = Prepare(''SELECT /*+FIRST_ROWS_1*/ qt.ID
FROM QueueTest qt
WHERE Locked IS NULL
ORDER BY PRIORITY
FOR UPDATE SKIP LOCKED'');
stmt.setFetchSize(10);
stmt.execute();
batch := stmt.fetch();
foreach row in batch {
-- process row
}
commit (to free the locks from the update)
stmt.close;
ACTUALIZAR
Sobre la base de los comentarios a continuación, se sugirió usar ROWNUM para limitar los resultados recibidos, pero eso no funcionará en este caso. Considera el ejemplo:
create table lock_test (c1 integer);
begin
for i in 1..10 loop
insert into lock_test values (11 - i);
end loop;
commit;
end;
/
Ahora tenemos una tabla con 10 filas. Tenga en cuenta que he insertado cuidadosamente las filas en orden inverso, la fila que contiene 10 es primero, luego 9, etc.
Digamos que quieres las primeras 5 filas, ordenadas ascendentes, es decir, de 1 a 5. Tu primer intento es este:
select *
from lock_test
where rownum <= 5
order by c1 asc;
Lo que da los resultados:
C1
--
6
7
8
9
10
¡Eso está claramente mal, y es un error que casi todos cometen! Mira el plan explicativo para la consulta:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 65 | 4 (25)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| LOCK_TEST | 10 | 130 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=5)
Oracle ejecuta el plan desde abajo hacia arriba: observe que el filtro en Rownum se realiza antes de la clasificación, Oracle toma las filas en el orden en que las encuentra (orden en que se insertaron aquí {10, 9, 8, 7, 6}) , se detiene después de obtener 5 filas, y luego ordena ese conjunto.
Entonces, para obtener los primeros 5 correctos, primero debe hacer el ordenamiento y luego el orden utilizando una vista en línea:
select * from
(
select *
from lock_test
order by c1 asc
)
where rownum <= 5;
C1
--
1
2
3
4
5
Ahora, para finalmente llegar al punto, ¿puede poner un salto de actualización bloqueado en el lugar correcto?
select * from
(
select *
from lock_test
order by c1 asc
)
where rownum <= 5
for update skip locked;
Esto da un error:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc
Intentar mover la actualización en la vista genera un error de sintaxis:
select * from
(
select *
from lock_test
order by c1 asc
for update skip locked
)
where rownum <= 5;
Lo único que funcionará es lo siguiente, que DA EL RESULTADO INCORRECTO :
select *
from lock_test
where rownum <= 5
order by c1 asc
for update skip locked;
De hecho, si ejecuta esta consulta en la sesión 1, y luego la ejecuta de nuevo en la sesión dos, la sesión dos dará cero filas, ¡lo que realmente está mal!
¿Entonces que puedes hacer? Abra el cursor y busque cuántas filas desea de él:
set serveroutput on
declare
v_row lock_test%rowtype;
cursor c_lock_test
is
select c1
from lock_test
order by c1
for update skip locked;
begin
open c_lock_test;
fetch c_lock_test into v_row;
dbms_output.put_line(v_row.c1);
close c_lock_test;
end;
/
Si ejecuta ese bloque en la sesión 1, se imprimirá ''1'' al bloquear la primera fila. Luego, ejecútalo nuevamente en la sesión 2, y se imprimirá ''2'', ya que saltó la fila 1 y obtuvo la siguiente gratis.
Este ejemplo está en PLSQL, pero al usar setFetchSize en Java, debería poder obtener exactamente el mismo comportamiento.
Me encontré con este problema, pasamos mucho tiempo para resolverlo. Algunos de los usos for update for update skip locked , en Oracle 12c, un nuevo método es usar fetch first n rows only . Pero usamos oracle 11g.
Finalmente, probamos este método, y encontramos que funciona bien.
CURSOR c_1 IS
SELECT *
FROM QueueTest qt
WHERE Locked IS NULL
ORDER BY PRIORITY;
myRow c_1%rowtype;
i number（5）:=0;
returnNum := 10;
BEGIN
OPEN c_1;
loop
FETCH c_1 into myRow
exit when c_1%notFOUND
exit when i>=returnNum;
update QueueTest set Locked=''myLock'' where id=myrow.id and locked is null;
i := i + sql%rowcount;
END
CLOSE c_1;
commit;
END;
Lo escribo en el bloc de notas, por lo que, tal vez, algo esté mal, puedes modificarlo como un procedimiento o si no
Mi solución es escribir un procedimiento almacenado como este:
CREATE OR REPLACE FUNCTION selectQueue
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
rt_cursor SYS_REFCURSOR;
i number(19, 0);
BEGIN
open st_cursor for
select id
from my_queue_table
for update skip locked;
fetch st_cursor into i;
close st_cursor;
open rt_cursor for select i as id from dual;
return rt_cursor;
END;
Este es un ejemplo simple: devolver TOP FIRST fila no bloqueada. Para recuperar filas TOP N: reemplace la recuperación individual en la variable local ("i") con la recuperación en bucle en la tabla temporal.
PD: cursor de retorno - es para hibernar amistad.