Привет, всем.
Вопрос для тех, кто работает с Oracle. Помогите выбрать тип транзакции вот для какой ситуации: имеется таблица, в таблице есть две важные колонки, комбинация которых уникальна для данного отношения. Первый ключ является внешним foreign ключём. Второе поле также уникально, но в пределах для внешнего ключа, т.е. комбинации могут быть такими:

|_A(FK)_|__B__|                            такая комбинация не возможна:     |_A(FK)_|__B__|
|______1|____1|                                                                                         |______1|____1| 
|______1|____2|                                                                                         |______1|____2| 
|______1|____3|                                                                                         |______1|____2| 
|______2|____1| 
|______2|____2| 
|______2|____3|

При анализе ключа В, необходимо сделать так, чтобы никакие другие транзакции не имели возможности не считывать информацию, не делать записи в эту таблицу. Как быть?

1. select ... for update
2. dbms_lock

тут главное не пытатся блокировать всю таблицу. тот кто хочет заблокировать делает на нужные записи select ... for update, остальные соответственно тоже при чтении используют select ... for update.

Здравствуйте, kallisto, Вы писали:

В первую очередь, не слишком понятен вопрос. Совершенно нормальная ситуация — делаете первичный или уникальный ключ на эти два поля, и в общем заботиться больше не о чем.

K>При анализе ключа В, необходимо сделать так, чтобы никакие другие транзакции не имели возможности не считывать информацию, не делать записи в эту таблицу.

Это весьма странное желание, на самом деле, но кроме того, в Oracle практически не реализуемое. Проще всего, пожалуй, переименовать таблицу и никому не говорить новое имя — но это, мягко говоря, чревато последствиями. С помощью команды lock table X in exclusive mode можно заблокировать любую запись в эту таблицу — и этого практически всегда достаточно. Более того, сам вопрос, скорее всего, означает недостаточное понимание механизмов изоляции.

В чем реальная задача?

Здравствуйте, Softwarer, Вы писали:

S>Это весьма странное желание, на самом деле, но кроме того, в Oracle практически не реализуемое. Проще всего, пожалуй, переименовать таблицу и никому не говорить новое имя — но это, мягко говоря, чревато последствиями. С помощью команды lock table X in exclusive mode можно заблокировать любую запись в эту таблицу — и этого практически всегда достаточно. Более того, сам вопрос, скорее всего, означает недостаточное понимание механизмов изоляции.

S>В чем реальная задача?

Реальная задача состоит в генерации только уникальных значений ключа В в рамках заданного А. Причём, генерация ключа В происходит, скажем так, постоянно из разных сессий.

Здравствуйте, kallisto, Вы писали:

K>Реальная задача состоит в генерации только уникальных значений ключа В в рамках заданного А. Причём, генерация ключа В происходит, скажем так, постоянно из разных сессий.

Если нет дополнительных условий — разумнее всего генерировать B из секвенсора. Это тривиально в реализации и обеспечит наилучшую масштабируемость (в частности, наилучшую работу в условиях большого количества конкурирующих сессий). Если дополнительные условия есть — изложите, какие.

K>Реальная задача состоит в генерации только уникальных значений ключа В в рамках заданного А. Причём, генерация ключа В происходит, скажем так, постоянно из разных сессий.

что значит из разных ? зачем из разных ? вы должны при создании записи сгенерить все ключи и после закомитить транзакцию. пока транзакция не закомичена никто ничего лишнего не увидит.

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, kallisto, Вы писали:

K>>Реальная задача состоит в генерации только уникальных значений ключа В в рамках заданного А. Причём, генерация ключа В происходит, скажем так, постоянно из разных сессий.

S>Если нет дополнительных условий — разумнее всего генерировать B из секвенсора. Это тривиально в реализации и обеспечит наилучшую масштабируемость (в частности, наилучшую работу в условиях большого количества конкурирующих сессий). Если дополнительные условия есть — изложите, какие.

sequence — это хорошо, но вся проблема заключается в том, что, если абстрагироваться от других колонок, и смотреть только на колонку В, то видить мы будем примерно такое : 1 2 3 4 1 2 3 1 2 3 4 5 1 2 3 4 и т.д. причём нумерация будет продолжаться от любой цифры, стоящей перед "1" и из "любого" места

Здравствуйте, <Аноним>, Вы писали:

А>что значит из разных ? зачем из разных ? вы должны при создании записи сгенерить все ключи и после закомитить транзакцию. пока транзакция не закомичена никто ничего лишнего не увидит.

база — многопользовательская, количество сессий ограничено, но их число до граничной точки не определено

Здравствуйте, kallisto, Вы писали:

K>sequence — это хорошо, но вся проблема заключается в том, что, если абстрагироваться от других колонок, и смотреть только на колонку В, то видить мы будем примерно такое : 1 2 3 4 1 2 3 1 2 3 4 5 1 2 3 4 и т.д. причём нумерация будет продолжаться от любой цифры, стоящей перед "1" и из "любого" места

Хм. При sequence мы будем видеть как раз противоположную картину.

Если Вы хотите поддерживать нумерацию по порядку без пропусков (в рамках одного значения ключа) — прежде всего советую подумать, так ли это нужно. По моему опыту, практически всегда это каприз, а не объективная необходимость, и если начать задавать вопрос "зачем" — оказывается, что желающий этого не может внятно ответить (максимум — "так будет красивее").

Давайте пока предположим, что записи не удаляются (сами понимаете, в рамках поддержания непрерывности это отдельный геморрой).

В таких условиях необходима полная сериализация — есть я вставляю запись с ключом [A,5], до тех пор, пока я не сделаю commit, никто не имеет права вставить запись [A,6] — вдруг я откачусь и получится дырка? То есть масштабируемости и нормальной работе многих пользователем делаем ручкой.

Соответственно, перед вставкой записи нужно делать эксклюзивную блокировку на все записи с ключом A. Первый кандидат на это — dbms_lock, вызываемый триггером, тем же, который вычисляет очередное B. Тут еще придется разбираться с мутацией. Можно также блокировать запись [A,1] — но тут дополнительно потребуется защищаться от конкурентной вставки этой первой записи.

Здравствуйте, Softwarer, Вы писали:

S>Хм. При sequence мы будем видеть как раз противоположную картину.

S>Если Вы хотите поддерживать нумерацию по порядку без пропусков (в рамках одного значения ключа) — прежде всего советую подумать, так ли это нужно. По моему опыту, практически всегда это каприз, а не объективная необходимость, и если начать задавать вопрос "зачем" — оказывается, что желающий этого не может внятно ответить (максимум — "так будет красивее").

S>Давайте пока предположим, что записи не удаляются (сами понимаете, в рамках поддержания непрерывности это отдельный геморрой).

S>В таких условиях необходима полная сериализация — есть я вставляю запись с ключом [A,5], до тех пор, пока я не сделаю commit, никто не имеет права вставить запись [A,6] — вдруг я откачусь и получится дырка? То есть масштабируемости и нормальной работе многих пользователем делаем ручкой.

S>Соответственно, перед вставкой записи нужно делать эксклюзивную блокировку на все записи с ключом A. Первый кандидат на это — dbms_lock, вызываемый триггером, тем же, который вычисляет очередное B. Тут еще придется разбираться с мутацией. Можно также блокировать запись [A,1] — но тут дополнительно потребуется защищаться от конкурентной вставки этой первой записи.

Решение этой проблемы я видела так: перед тем, как делать вставку в таблицу, с заданным ключём in(А), анализируется и вычисляется new(В), потом выполняется вставка

insert into...(in(A), new (B), ...)

. Дабы в промежуток времени, между получением new(В) и вставки в таблицу, в другой сессии не было получено аналогичное new(В), необходимо блокировать все записи для чтения из других сессий для in(A)... а вот как это сделать средствами Oracle, не знаю

Соглашусь с Вами, что решить сие можно с помощью sequence, будь нумерация порядковой и уникальной в пределах одной колонки, но увы...

А>>что значит из разных ? зачем из разных ? вы должны при создании записи сгенерить все ключи и после закомитить транзакцию. пока транзакция не закомичена никто ничего лишнего не увидит.

K>база — многопользовательская, количество сессий ограничено, но их число до граничной точки не определено

сколько у вас сессий неважно, важны транзакции.
итак у вас тригер, он генерит ключи для новой записи первая транзакция делает select * from table where a=1 for update wait 5 и болокирует записи где a=1 и дальше занимается своими расчетами.
вторая транзакция дергает тот же тригер т.е. select * from table where a=1 for update wait 5 , но т.к. записи заблокированы она или отвалится через 5 секунд (wait 5) или дождется пока первая даст комит и только после этого пойдет дальше.

Здравствуйте, kallisto, Вы писали:

K>Решение этой проблемы я видела так: перед тем, как делать вставку в таблицу, с заданным ключём in(А), анализируется и вычисляется new(В), потом выполняется вставка
K>

insert into...(in(A), new (B), ...)

. Дабы в промежуток времени, между получением new(В) и вставки в таблицу, в другой сессии не было получено аналогичное new(В), необходимо блокировать все записи для чтения из других сессий для in(A)... а вот как это сделать средствами Oracle, не знаю

В этом варианте выгоднее не блокировать, а обрабатывать ошибку DUP_VAL_ON_INDEX — просто перезапрашивать new(B) (например, B=B+1) и делать новую попытку. Но таким образом Вы не получите плотной последовательности — две транзакции могут получить последовательные значения B, после чего транзакция с меньшим B вдруг откатится.

Здравствуйте, kallisto, Вы писали:

таблицу, в другой сессии не было получено аналогичное new(В), необходимо блокировать все записи для чтения из других сессий для in(A)...

Блокировать для чтения в этом случае не надо. Если параллельно кто-то строит отчет — пусть себе строит, блокировать его незачем. Нужна эксклюзивная блокировка, которая не даст одновременно вставлять — а тут я уже называл варианты; dbms_lock или select for update.

Здравствуйте, kallisto, Вы писали:

Все-таки задача не совсем ясна: вам нужны красивые номера или чтобы сессии не мешали друг другу (масштабируемость)? Уникальность B обеспечивается сиквенсом, что еще нужно?

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, kallisto, Вы писали:

W>Все-таки задача не совсем ясна: вам нужны красивые номера или чтобы сессии не мешали друг другу (масштабируемость)? Уникальность B обеспечивается сиквенсом, что еще нужно?

Как уже было написано: sequence — это хорошо, но вся проблема заключается в том, что, если абстрагироваться от других колонок, и смотреть только на колонку В, то видить мы будем примерно такое : 1 2 3 4 1 2 3 1 2 3 4 5 1 2 3 4 и т.д. причём нумерация будет продолжаться от любой цифры, стоящей перед "1" и из "любого" места

Здравствуйте, kallisto, Вы писали:

K>Как уже было написано: sequence — это хорошо, но вся проблема заключается в том, что, если абстрагироваться от других колонок, и смотреть только на колонку В, то видить мы будем примерно такое : 1 2 3 4 1 2 3 1 2 3 4 5 1 2 3 4 и т.д. причём нумерация будет продолжаться от любой цифры, стоящей перед "1" и из "любого" места

1. И какая в этом проблема? Кто будет "смотреть только на колонку В"?

2. Если использовать sequence, такого как раз не будет.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, kallisto, Вы писали:

K>>Как уже было написано: sequence — это хорошо, но вся проблема заключается в том, что, если абстрагироваться от других колонок, и смотреть только на колонку В, то видить мы будем примерно такое : 1 2 3 4 1 2 3 1 2 3 4 5 1 2 3 4 и т.д. причём нумерация будет продолжаться от любой цифры, стоящей перед "1" и из "любого" места

W>1. И какая в этом проблема? Кто будет "смотреть только на колонку В"?

W>2. Если использовать sequence, такого как раз не будет.

Проблема не в просмотре колонки В, её видеть никто не будет и проблема не в её уникальности, уникальна она только в пределах определённых значений колонки А. Значения для колонки В генерятся внутри процедуры и зависят от колонки А, где А — это foreign key, т.е. в данном отношении, он будет неоднократно повторяться. Обьединение А и В уникально для этого отношения.

K>Проблема не в просмотре колонки В, её видеть никто не будет и проблема не в её уникальности, уникальна она только в пределах определённых значений колонки А. Значения для колонки В генерятся внутри процедуры и зависят от колонки А, где А — это foreign key, т.е. в данном отношении, он будет неоднократно повторяться. Обьединение А и В уникально для этого отношения.

вам просто нужно с помощью select for update заблокировать в функции генерации значений все что нужно для вычисления B и не будет проблем. просто это будет гораздо хуже маштабироватся чем генерация значения сиквенса.

Здравствуйте, <Аноним>, Вы писали:

А>вам просто нужно с помощью select for update заблокировать в функции генерации значений все что нужно для вычисления B и не будет проблем. просто это будет гораздо хуже маштабироватся чем генерация значения сиквенса.

Сиквенс там в принципе нельзя использовать, т.к. значения генеряться не попорядку, а хаотично:

select NVL((select lit.itemid
                         from LookupItemTranslation lit
                           where lit.tableid = pTableID
                       ), 0)
            into lcurrItemID
     from dual;
     
    if lcurrItemID = 0
         then lItemID := 1;
    else lItemID := lcurrItemID+1;
    end if;

Здравствуйте, kallisto, Вы писали:

K>Проблема не в просмотре колонки В, её видеть никто не будет и проблема не в её уникальности, уникальна она только в пределах определённых значений колонки А. Значения для колонки В генерятся внутри процедуры и зависят от колонки А, где А — это foreign key, т.е. в данном отношении, он будет неоднократно повторяться. Обьединение А и В уникально для этого отношения.

Так в чем же проблема наконец?

A и B это суррогатные ключи или нет? Если B зависит от A, то причем тут sequence?