и смех и грех: LOCK TABLE ... IN EXCLUSIVE MODE

T_34 (из Баварии)
Дата: 11.08.2005 18:05:59
у нас из клиентов (EXE-шников) (которые на Delphi) периодически вызываются функции типа:

CREATE OR REPLACE PROCEDURE GET_TANUMMER(NewID out integer)
is
begin
 LOCK TABLE SIDNumbers  IN EXCLUSIVE MODE;
  select ID+1 into NewID from SIDNUMBERS where NR = 40 For Update;
  update SIDNUMBERS set ID= ID + 1 where NR = 40;
end;

начальник утверждает, что такое необходимо! дабы не допустить дырок в номерах (которые были бы критичны для заказчиков из соображений
бизнес-правил...) И поэтому secuence-ы использовать нельзя... :-)

Как мягко убедить его, что он неправ, учитывая тот факт, что у клиентов часто возникают deadloсk-и (вижу в Alert*.log) и даже "зависы", так как ежедневно как минимум по разу в день все (40 станций) вдруг ждут блокированную (IN EXCLUSIVE MODE) таблицу SIDNUMBERS

:-) :-) :-)
SY
Дата: 11.08.2005 18:13:17
And what will happen when:

1. SessionA executes the procedure and gets new id, for example 22.
2. SessionB executes the procedure and gets new id 23.
3. SessionC executes the procedure and gets new id 24.
4. SessionB rolls back (explicitly, or implicitly - e.g. connection is lost)

As a result дырка в номерах.

SY.
Leonid_B
Дата: 11.08.2005 18:19:12
Почему не просто:
  select ID+1 into NewID from SIDNUMBERS where NR = 40 For Update Wait 5;
  update SIDNUMBERS set ID= ID + 1 where NR = 40;
Partos
Дата: 11.08.2005 18:40:09
SY
And what will happen when:

1. SessionA executes the procedure and gets new id, for example 22.
2. SessionB executes the procedure and gets new id 23.
3. SessionC executes the procedure and gets new id 24.
4. SessionB rolls back (explicitly, or implicitly - e.g. connection is lost)

As a result дырка в номерах.

SY.


Сесия С получит результат 24 только после того как сесия Б закомитится....следовательно откатить сесию Б нельзя ...раньше она не увидит число 24 (у Oracle нет грязного чтения)...а будет думать что максимальное это 23...и при попытке вставки сесией С, когда ещё не закомитилась сесия Б, числа 23 - вы получите блокировку, которая будет ждать закомитится или нет сесия Б и если закомитится то сесия С автоматически откатится из-за нарушения требований первичного ключа...(если это для него используется такой приём....в противном случае - нарушение бизнес-логики из-за повторяемости значений в случае не наложение ограничения unique)

SY

Почему не просто:
select ID+1 into NewID from SIDNUMBERS where NR = 40 For Update Wait 5;
update SIDNUMBERS set ID= ID + 1 where NR = 40;


А что помешает второй сессии взять и прочитать эту строку??? Да, она не сможет её изменить при наложении for update....Чтение то при for update не блокируется! Прочитать она её сможет...и получит такой же первичный ключ....какая-то из них первая вставляет строку, какая-то пытается вставить второй.... снова мы нарываемся на блокировку ....и снова одна из двух транзакций должна откатиться...Обе успешно не завершаться....(этьо если это первичный ключ или unique....в противном случае - нарушение бизнес-правил)


Если это касается первичных ключей....Начальник гонит беса....Пусть почитает Тома Кайта и книжку про нормализацию БД и поймёт что первичные ключи не должны нести в себе никакой бизнес-логики...
RA\/EN
Дата: 11.08.2005 19:00:31
Выделите диапазон непрерывных ID-ков в отдельной таблице, начальные берите из сиквенса (допустим, всего 10 знаков - тогда сиквенс начинайте с 10^10), а потом, по окончании операции, перед commit'ом непосредственно, изменяйте ID по текущей технологии, но лочьте таблицу с ID-ками - deadlock-ов не будет, т.к. операция микроскописечкая по времении, ит перед commit'ом нет операций по другим таблицам, которые могут привески к дедлоку.
dmidek
Дата: 11.08.2005 19:06:17
А у нас тоже была похожая проблема, это наверное немецкая специфика :-)
Ну у нас до такого идиотизма не додумались. Были покушения на CACHE 20.
У нас пользователи вводили в Forms от ... до... и были недовольны, что много номеров пропадает зря. Ну мы и говорим, что бизнес- номера не должны нести (дальше читайте Partosa). Отстояли мы пока наш CACHE 20.
Stax.
Дата: 11.08.2005 19:36:11
Leonid_B
Почему не просто:
  select ID+1 into NewID from SIDNUMBERS where NR = 40 For Update Wait 5;
  update SIDNUMBERS set ID= ID + 1 where NR = 40;

Между select i update может успеть другая сессия,
поэтому посоветовал бы использовать
кляузу returning и убрать селект
тогда имхо
LOCK TABLE SIDNumbers IN EXCLUSIVE MODE лишнее
T_34 (из Баварии)
Дата: 11.08.2005 19:36:26
1.
Начальник настаивает, что если таблицу не запирать
(IN EXCLUSIVE MODE) то ситуация, когда

Session A:

select id  from SIDNUMBERS where NR = 41 For Update;
select id  from SIDNUMBERS where NR = 40 For Update; 

Session B: (одновременно с A!)

select id  from SIDNUMBERS where NR = 40 For Update;
select id  from SIDNUMBERS where NR = 41 For Update; 

будет гораздо чаще давать Deadlock....

...
2.
нет, это не для первичных ключей, (но для некоего столбца с констр. UNIQUE)


3. Теперь еcть идея сделать так:

  ...
  begin
    select ID+1 into NewID from SIDNUMBERS where NR = 40 For Update wait 1;
    update SIDNUMBERS set ID= ID + 1 where NR = 40;
  execption
    when ORA-30006: Ressource busy then
       'вызвать повтор того же кода n-раз...'
  end;
Anton Demidov
Дата: 11.08.2005 19:42:35
Согласен, что правильнее использовать sequence, но вот ещё вариант для кучи

CREATE OR REPLACE PROCEDURE GET_TANUMMER(NewID out integer)
is
begin
update SIDNUMBERS set ID= ID + 1 where NR = 40 returning ID into NewID;
end;
T_34 (из Баварии)
Дата: 11.08.2005 20:08:59
Anton Demidov
Согласен, что правильнее использовать sequence, но вот ещё вариант для кучи

CREATE OR REPLACE PROCEDURE GET_TANUMMER(NewID out integer)
is
begin
update SIDNUMBERS set ID= ID + 1 where NR = 40 returning ID into NewID;
end;


пожалуй на этом и остановимся. А чем лучше sequence такого варианта?