Транзакции в Oracle - Базы данных

Привет, всем.
Вопрос для тех, кто работает с 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|

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

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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>В чем реальная задача?

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

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

Здравствуйте, 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" и из "любого" места

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

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

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

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

Здравствуйте, 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, будь нумерация порядковой и уникальной в пределах одной колонки, но увы...

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

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), ...)

В этом варианте выгоднее не блокировать, а обрабатывать ошибку 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" и из "любого" места

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

Здравствуйте, 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, т.е. в данном отношении, он будет неоднократно повторяться. Обьединение А и В уникально для этого отношения.

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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;

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

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

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

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

K>Сиквенс там в принципе нельзя использовать, т.к. значения генеряться не попорядку, а хаотично:
за счет этого "хаоса" и маштабируемость, а вот чтоб по порядку вы выстраиваете все транзакции в очередь.

K>

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

просто добавте
select lit.itemid from LookupItemTranslation lit where lit.tableid = pTableID for update wait 5;
в начало и все транзакции выстроятся в очередь.

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

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

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

Суть проблемы заключается вот в чём: вставка в эту таблицу происходит постоянно, причём не в определённой последовательности, а хаотично. Из-за этого существует высокая вероятность возникновения такого события — сессия S1 читает данные о колонке В из таблицы lit, т.е. temp := current(lit.B for in(A)), далее выполняется анализ переменной temp --> new(temp), в это время другая сессия S2 также получает temp := current(lit.B for in(A)) и new(temp) (т.е. S1.temp = S2.temp, а соответственно и new(S1.temp) = (newS2.temp)). После чего одна из сессий делает вставку --> данные хранящиеся в другой сессии уже устаревшие и при попытке вставки, генерится исключение

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

Так и не ясно, B суррогатный ключ или нет, но похоже что суррогатный.

Смысл выражений temp := current(lit.B for in(A)) и temp --> new(temp) для меня весьма туманен, но из того что я понял, могу сделать вывод, что генерация значений для B из последовательности решит вашу проблему. B будет уникальным ключом, а (A, B) — тем более. К тому же B в таком случае можно сделать PK.

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

K>

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

Это пожалуй достойно медали "За ясность мысли и лаконичность"

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

W>Так и не ясно, B суррогатный ключ или нет, но похоже что суррогатный.

CREATE TABLE SUN.LOOKUPITEMTRANSLATION
(
  TABLEID  NUMBER(4)                            NOT NULL,
  ITEMID   NUMBER(3)                            NOT NULL,
  TEXT     VARCHAR2(30 BYTE)                    NOT NULL
)

CREATE UNIQUE INDEX SUN.UQ_TABLEITEM ON SUN.LOOKUPITEMTRANSLATION
(TABLEID, ITEMID)
LOGGING
TABLESPACE ...

ALTER TABLE SUN.LOOKUPITEMTRANSLATION ADD (
  CONSTRAINT UQ_TABLEITEM UNIQUE (TABLEID, ITEMID)
    USING INDEX 
    TABLESPACE ...

ALTER TABLE SUN.LOOKUPITEMTRANSLATION ADD (
  CONSTRAINT FK_TABLEID FOREIGN KEY (TABLEID) 
    REFERENCES SUN.LOOKUPITEM (TABLEID)
    ON DELETE CASCADE)
/

W>Смысл выражений temp := current(lit.B for in(A)) и temp --> new(temp) для меня весьма туманен, но из того что я понял, могу сделать вывод, что генерация значений для B из последовательности решит вашу проблему. B будет уникальным ключом, а (A, B) — тем более. К тому же B в таком случае можно сделать PK.

Сам по себе ключ В не уникален, он уникален в комплексе с ключём А. Если использовать последовательность, то её значение с какой-то переодичностью придёться сбрасывать, ведь нумерация идёт не по порядку. И как потом последовательность будет возвращать, например число 5, если последнее значение, которое она сохранила, было 12?

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

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

W>>Так и не ясно, B суррогатный ключ или нет, но похоже что суррогатный.
K>

K>CREATE TABLE SUN.LOOKUPITEMTRANSLATION
K>...

Может быть вы не знаете, что значит суррогатный ключ?

K>Сам по себе ключ В не уникален, он уникален в комплексе с ключём А. Если использовать последовательность, то её значение с какой-то переодичностью придёться сбрасывать, ведь нумерация идёт не по порядку.

Давайте попробуем по-другому. Используя последовательность, вы получите такие значения

A    B
-----------
1    1
1    2
1    3
1    4
2    5
2    6
2    7
3    8
4    9
4    10
...

B уникален в комплексе с A, равно как и без A. Это вас устроит?

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

W>Давайте попробуем по-другому. Используя последовательность, вы получите такие значения
W>

W>A    B
W>-----------
W>1    1
W>1    2
W>1    3
W>1    4
W>2    5
W>2    6
W>2    7
W>3    8
W>4    9
W>4    10
W>...
W>

W>B уникален в комплексе с A, равно как и без A. Это вас устроит?

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

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

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

K>при таком раскладе, нарушется логика, хотя ...

Если ключи суррогатные, нарушаться нечему. Сделав B уникальным, получите из него простой PK, это лучше чем составной.

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

W>Если ключи суррогатные, нарушаться нечему. Сделав B уникальным, получите из него простой PK, это лучше чем составной.

Изначально предполагалось, что В в пределах одного значения А, будет нумероваться заново, например
[sql]
Зверь
--------------
ID_An| Title
--------------
1 | Заяц
2 | Лиса

/*--------------*/

Порода
----------------------
ID_An|ID_Аamily| Title
----------------------
1 | 1 | русак
1 | 2 | беляк
1 | 3 | толай
-----------------------
2 | 1 | афганская
2 | 2 | большеухая
2 | 3 | карликовая
2 | 4 | серая

[sql]

... << RSDN@Home 1.1.4 beta 3 rev. 0>>

	От:	kallisto
	Дата:	23.02.05 13:09
	Оценка:

	От:	Аноним
	Дата:	23.02.05 14:13
	Оценка:	+1

От:	Softwarer	http://softwarer.ru
Дата:	23.02.05 14:17
Оценка:

	От:	kallisto
	Дата:	23.02.05 14:27
	Оценка:

От:	Softwarer	http://softwarer.ru
Дата:	23.02.05 14:36
Оценка:

	От:	wildwind
	Дата:	24.02.05 13:55
	Оценка:	1 (1) +1