Есть несколько вопросов о том, как реализовать таблицу, подобную очереди (блокировать определенные строки, выбирать определенное их количество и пропускать текущие заблокированные строки) в Oracle и SQL Server.
Как я могу гарантировать получение определенного количества ( N
) строк, предполагая, что имеется не менее N
строк?
Из того, что я видел, Oracle применяет предикат WHERE
, прежде чем определять, какие строки следует пропустить. Это означает, что если я хочу извлечь одну строку из таблицы, и два потока одновременно выполняют один и тот же SQL, один получит строку, а другой - пустой набор результатов (даже если есть больше подходящих строк).
Это в отличие от того, как SQL Server обрабатывает подсказки блокировки UPDLOCK
, ROWLOCK
и READPAST
. В SQL Server TOP
волшебным образом кажется ограничивающим количество записей после успешного достижения блокировок.
Обратите внимание, две интересные статьи здесь и здесь .
ORACLE
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);
В двух отдельных сеансах выполните:
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
Обратите внимание, что первый возвращает строку, а второй сеанс не возвращает строку:
Session 1
ID ---- 4
Session 2
ID ----
SQL SERVER
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);
В двух отдельных сеансах выполните:
BEGIN TRANSACTION
SELECT TOP 1 qt.ID
FROM QueueTest qt
WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE Locked IS NULL
ORDER BY Priority;
Note что оба сеанса возвращают разные строки.
Сессия 1
ID ---- 4
Сессия 2
ID ---- 3
Как добиться аналогичного поведения в Oracle?