Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5   вперед  Ctrl      все
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
"обречены на провал" у вас означает
Если кратко - да.

Но стоит уточнить что именно пытаешься сымитировать.

Цель недопустить двух одинаковых ключей в таблице или сэмулировать поведение с уникальным индексом, то есть
1. При наличии закомиченой записи* - exception
2. При наличии незакомиченной записи* - висим на блокировке до тех пор пока
2.1 commit - получаем exception
2.2 rollback - отлипаем
2.3 ключ изменен или удалён - отлипаем
3. При полном отсутствии записи* идём дальше

?

* - понимается [с тем же ключом]

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

Сообщение было отредактировано: 15 ноя 20, 19:31
15 ноя 20, 19:35    [22232441]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
SY:

я перестарался с выявлением дупликатов во время post, емулируя dirty reads. Без этого код должен стать проще.

Кобанчег,

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

В этих ситуациях имеет смысл говорить об уменьшении кода в критической секции (или другом механизме синхронизации доступа).
Даже сиквенс имеет сериализованный код, это не значет что сиквенсы плохие или медленные.
15 ноя 20, 22:28    [22232518]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Кобанчег

Цель недопустить двух одинаковых ключей в таблице или сэмулировать поведение с уникальным индексом


Цель - второе. Была высказана гипотеза что это невозможно, но доказать невозможность с моим уровнем знаний довольно трудно, поэтому я работаю над контрпримером, заодно обучаясь тонкостям многосессионности.
15 ноя 20, 22:32    [22232520]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
предпринимаю новую попытку реализовать subj максимально приближенный по поведению к pk_key.

Оговорки: пока работает только для операции insert. Полагается на строковый лок через update. Может содержать ошибки.
Я протестировал из двух разных сессий commits и rollbacks. Вроде работает.

create table TEST23(  uq INTEGER ); -- это таблица с триггером, реализующим constraint
create table TEST_U(  id  INTEGER not null,  val INTEGER default 0 not null ); -- вспомогательная

-- вспомогательная функция, которая вставляет во вспомогательную таблицу ID и коммитит ее, 
-- чтобы показать всем сессиям. Функция должна быть атомарная (атомическая?), DBMS лок не показан
create or replace procedure CommitForAll(uq in integer) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
begin
  select id into n from test_u where id = uq;
exception
  when NO_DATA_FOUND then 
    insert into test_u values (uq,0);
    commit write nowait batch;
end CommitForAll;

-- собственно триггер. эмуляция поведения при вставлении дупликата реализована через update.
-- поскольку update после освобождения строчного лока не вылетает, а срабатывает,
-- приходится повторно проверять главную таблицу на предмет закоммиченных UQ
create or replace trigger tr_test23
  before insert on test23  
  for each row
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- remember the posted id for other sessions
  CommitForAll( :new.uq );

  -- first, check among session-visible data (commited +posted in this session)
  for t in (select * from test23 where uq = :new.uq)
  loop
    raise DUP_VAL_ON_INDEX;
  end loop;

  -- try causing row-level lock in helper table of uncommitted inserts
  update test_u u
     set val = u.val+1
   where u.id = :new.uq;

  -- if updated, lock worked; see if uq now among committed
  if SQL%ROWCOUNT > 0 then
    for r in (select * from test23 where uq = :new.uq)
    loop
      raise DUP_VAL_ON_INDEX;
    end loop;
  end if;
end tr_test23;
16 ноя 20, 02:14    [22232564]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
Про транзакционность и блокировки следует узнавать из документации или на худой конец из книг а не от всяких кабанов с форумов.
Ну о том, что надо сначала читать, потом переваривать и думать и только потом выкладывать свой бред на потеху публике высказалось уже множество людей.
НеофитSQL
Может содержать ошибки.
Это одна сплошная ошибка.
SQL> insert into test23 select 0 from dual;
insert into test23 select 0 from dual
            *
ERROR at line 1:
ORA-04091: table TEST.TEST23 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'
16 ноя 20, 07:08    [22232572]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Кобанчег,


У меня почему-то работало.
Для "before" триггера таблица не должна мутировать.

Завтра разберусь.
16 ноя 20, 08:29    [22232590]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18398
НеофитSQL,
применение dbms_lock по сути не отличается от Вашего исходного варианта с unique constraint на дополнительной таблице, поскольку примитивом синхронизации остается PK на доп. таблице:
+

select referenced_owner, referenced_name
  from dba_dependencies
 where (owner, name, referenced_type) = any(('SYS', 'DBMS_LOCK', 'TABLE'))
;
 
REFERENCED_OWNER          REFERENCED_NAME
------------------------- -------------------------
SYS                       DUAL
SYS                       DBMS_LOCK_ALLOCATED

select constraint_type, constraint_name, table_name
  from dba_constraints
 where (owner, table_name) = any(('SYS','DBMS_LOCK_ALLOCATED'))
;
 
CONSTRAINT_TYPE CONSTRAINT_NAME           TABLE_NAME
--------------- ------------------------- -------------------------
P               SYS_C004841               DBMS_LOCK_ALLOCATED
U               SYS_C004842               DBMS_LOCK_ALLOCATED
 
SQL> 
16 ноя 20, 14:13    [22232919]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
andrey_anonymous,

Я не использую DBMS lock в последнем варианте кода.

> применение dbms_lock по сути не отличается от Вашего исходного варианта с unique constraint на дополнительной таблице, поскольку примитивом синхронизации остается PK на доп. таблице:

Такое можно было бы сказать, если бы я использовал DBMS_LOCK на каждую строчку, но до такого я не додумался :)

Использование одного уникального объекта схемы не приравнивается к использованию свойств PK.
16 ноя 20, 18:41    [22233223]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Кобанчег
НеофитSQL
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
Про транзакционность и блокировки следует узнавать из документации или на худой конец из книг а не от всяких кабанов с форумов.
Ну о том, что надо сначала читать, потом переваривать и думать и только потом выкладывать свой бред на потеху публике высказалось уже множество людей.
НеофитSQL
Может содержать ошибки.
Это одна сплошная ошибка.
SQL> insert into test23 select 0 from dual;
insert into test23 select 0 from dual
            *
ERROR at line 1:
ORA-04091: table TEST.TEST23 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'


Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?
Решение известно, например compound trigger. На время, сужаю применимость моего решения для одиночных insert.
(про delete, update, truncate было сказано в начале).
Можно ли считать мое решение неотличимым от PK в поведении для одиночных insert?
16 ноя 20, 18:57    [22233238]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
НеофитSQL

Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?


INSERT/UPDATE/DELETE - атомарная операция независимо от числа вставляемых/изменяемых/удаляемых строк.
Mutating - это когда состояние таблицы когда уже не до INSERT/UPDATE/DELETE но еще не после. А вот INSERT VALUES это тот единственный случай когда ORACLE точно знает - вставка одной строки и посему BEFORE EACH ROW это точно до INSERT.

SY.
16 ноя 20, 19:52    [22233308]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.

SY.
16 ноя 20, 20:17    [22233320]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
SY
НеофитSQL

Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?


INSERT/UPDATE/DELETE - атомарная операция независимо от числа вставляемых/изменяемых/удаляемых строк.
Mutating - это когда состояние таблицы когда уже не до INSERT/UPDATE/DELETE но еще не после. А вот INSERT VALUES это тот единственный случай когда ORACLE точно знает - вставка одной строки и посему BEFORE EACH ROW это точно до INSERT.
SY.


В случае "insert .. select 0 from dual" оракл тоже точно знает что одна строка, но не использует это знание.

Получается, Оракл особым образом обрабатывает insert..values() не так, как вставку таблицы из одной строки.
Я такой технической детали не знал. Если такое поведение задокументировано, я теперь могу написать триггер который позволит вставлять строчки только через values(), и никаким другим способом. Но вряд ли пригодится :)

Интересно почему для values() Оракл не использовал общий код мультистрочной вставки. Сомневаюсь что для оптимизации скорости, однострочные вставки заведомо не помогают производительности. Возможно, внутри у Оракла есть системный триггер которому удобно читать из модифицируемой таблицы, и оракл сделал специальную обработку insert..values() чтобы помочь себе.
16 ноя 20, 20:26    [22233326]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
SY
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.
SY.


Мне это уточнение непонятно.
Мой "before insert by row" триггер вылетал на строке которая только читала из таблицы триггера.

Вот максимально упрощенный пример:
SQL> create table test24 ( id integer );
Table created

SQL> create trigger tr_test24
  2  before insert on test24
  3  for each row
  4  declare
  5    n integer;
  6  begin
  7    select count(*) into n from test24;
  8  end;
  9  /
Trigger created

SQL> insert into test24 values(0);
1 row inserted

SQL> commit;
Commit complete

SQL> insert into test24 select 1 from dual;
insert into test24 select 1 from dual
ORA-04091: table TEST.TEST24 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST24", line 4
ORA-04088: error during execution of trigger 'TEST.TR_TEST24'
16 ноя 20, 20:36    [22233331]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Переделал свое решение чтобы позволить мультистрочные вставки.

Проверил из двух разных сессий, сессии помечены #1/#2.

---------------------------- session #1 ------------------------------
#1> insert into test23 select 1 from dual;
1 row inserted

#1> select * from test23;
                                     UQ
---------------------------------------
                                      1

---------------------------- session #2 ------------------------------
#2> insert into test23 select 2 from dual;

1 row created.

#2> select * from test23;

        UQ
----------
         2

#2> insert into test23 select 2 from dual;
insert into test23 select 2 from dual
            *
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 43
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'

#2> insert into test23 select 1 from dual;
--(sqlplus подвис)
---------------------------- session #1 ------------------------------
#1> rollback;
Rollback complete

#1> select * from test23;
                                     UQ
---------------------------------------

---------------------------- session #2 ------------------------------
#2 (sql+ проснулся, был блокирован на 'insert into test23 select 1 from dual;')

1 row created.

#2> select * from test23;

        UQ
----------
         2
         1
---------------------------- session #1 ------------------------------
#1> insert into test23 values(2);
(сессия 1 подвисла)
---------------------------- session #2 ------------------------------
#2> commit;

Commit complete.
---------------------------- session #1 ------------------------------
#1 (проснулась, висела на 'insert into test23 values(2);')
insert into test23 values(2)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 52
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'
16 ноя 20, 21:01    [22233336]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
С мультистрочными insert пока не тестировал.
Просматриваю свой код на предмет race conditions (когда commit/rollback произойдет в другой сессии во время исполнения триггера, и сразу несколько ожидающих сессий разблокируются).

Проверю с двумя-тремя ожидающими сессиями, потом запощу код.
16 ноя 20, 21:26    [22233343]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
На время, сужаю применимость моего решения для одиночных insert.
Пора уже сузить применимость до нуля. :)

1. Может случится, что две параллельные сессии проверят что в test_u нет строк и каждая вставит по одной.
2. Нет никаких гарантий что вторая сессия (в которой инсерт запустили после первой) не захватит блокировку в test_u первой.

итд

Градус абсурда зашкаливает, я уже пожалел что влез в эту тему.
16 ноя 20, 21:40    [22233353]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Кобанчег
НеофитSQL
На время, сужаю применимость моего решения для одиночных insert.
Пора уже сузить применимость до нуля. :)

1. Может случится, что две параллельные сессии проверят что в test_u нет строк и каждая вставит по одной.
2. Нет никаких гарантий что вторая сессия (в которой инсерт запустили после первой) не захватит блокировку в test_u первой.

итд

Градус абсурда зашкаливает, я уже пожалел что влез в эту тему.


1. не влияет на логику. test_u позволительно содержать дубликаты.
2. одинаково относится к PK, там нет гарантий очередности насколько мне известно.
16 ноя 20, 22:02    [22233368]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
НеофитSQL
SY
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.
SY.


Мне это уточнение непонятно.


drop table test purge;
create table test(n number);
create or replace
  trigger test_trg
  for insert
  on test
  compound trigger
      v_cnt number;
  before statement
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before statement;
  before each row
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before each row;
end;
/
insert
  into test
  values(1)
/

1 row created.

create or replace
  package level_pkg
    is
        g_level number := 0;
end;
/
create or replace
  trigger test_trg
  for insert
  on test
  compound trigger
      v_cnt number;
  before statement
    is
      begin
          if level_pkg.g_level = 0
            then
            level_pkg.g_level := 1;
            insert
              into test
              select  rownum
                from  emp;
          end if;
  end before statement;
  before each row
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before each row;
end;
/
insert
  into test
  values(1)
/
  into test
       *
ERROR at line 2:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TEST_TRG", line 18
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRG'
ORA-06512: at "SCOTT.TEST_TRG", line 9
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRG'

SQL>


SY.
17 ноя 20, 00:44    [22233419]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
SY,

Спасибо за иллюстрацию!

В этом коде триггер пишет в таблицу, это выглядит невероятно взрывоопасно.

Я так понял глобальная переменная помогает избежать бесконечной рекурсии триггера,
но неужели кто-то такое пишет?
17 ноя 20, 02:32    [22233453]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
НеофитSQL
С мультистрочными insert пока не тестировал.
Просматриваю свой код на предмет race conditions (когда commit/rollback произойдет в другой сессии во время исполнения триггера, и сразу несколько ожидающих сессий разблокируются).

Проверю с двумя-тремя ожидающими сессиями, потом запощу код.


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

+
create or replace trigger tr_test23
  before insert on test23  
  for each row
declare
  procedure PrepLock is
    PRAGMA AUTONOMOUS_TRANSACTION;
    n integer;
  begin
    select id into n from test_u where id = :new.uq;
  exception
    when NO_DATA_FOUND then 
      insert into test_u values (:new.uq);
      commit write nowait batch;
  end PrepLock;

  function FindCommitted return integer is
    PRAGMA AUTONOMOUS_TRANSACTION;
  begin
    for t in (select count(*) n from dual where exists 
            (select null from test23 where uq = :new.uq))
    loop
      return t.n;
    end loop;
  end FindCommitted;
  
  function FindPosted return integer is
  begin
    for t in (select count(*) n from dual where exists 
            (select null from test_t where id = :new.uq))
    loop
      return t.n;
    end loop;
  end FindPosted;
  
  function WaitOnLock return integer is
    n integer;
  begin
    select 1 into n from test_u where id = :new.uq for update nowait;
    return 0; -- first lock, no wait
  exception
    when others then -- catching ORA-00054 resource busy and NOWAIT specified
      select 0 into n from test_u where id = :new.uq for update;
      return 1;
  end WaitOnLock;

begin
  -- get ready to use the row lock for this value
  PrepLock; -- :new.uq is implicit

  -- first, check among posted values in this session
  if FindPosted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- second, check among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- dequeue multiple sessions one by one
  while WaitOnLock != 0
  loop
    null;
  end loop;

  -- check again among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- count this id as posted within this session
  insert into test_t values ( :new.uq );
end tr_test23;



Пришлось в дополнение к вспомогательной таблице test_u, которая используется для row-level locks, завести GTT test_t, в которой находятся posted, not committed строчки из каждой сессии. Тело триггера стало напоминать псевдокод:

  -- get ready to use the row lock for this value
  PrepLock; -- :new.uq is implicit

  -- first, check among posted values in this session
  if FindPosted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- second, check among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- dequeue multiple sessions one by one
  while WaitOnLock != 0
  loop
    null;
  end loop;

  -- check again among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- count this id as posted within this session
  insert into test_t values ( :new.uq );
17 ноя 20, 02:44    [22233454]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
НеофитSQL
Пришлось

Но и это не помогло реализовать unique

SQL> create table dropme_t (uq integer) partition by range(uq) interval (100) (partition p0 values less than (10)) enable row movement;

Table created.

SQL> create table dropme_t2 (uq integer);

Table created.

SQL> alter table dropme_t2 add constraint dropme_t2_ux unique (uq);

Table altered.

SQL> insert into dropme_t select mod(level,17) from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> alter table dropme_t exchange partition p0 with table dropme_t2;
alter table dropme_t exchange partition p0 with table dropme_t2
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> alter table dropme_t exchange partition p0 with table test23;

Table altered.

SQL> select uq, count(*) from test23 group by uq order by uq;

        UQ   COUNT(*)
---------- ----------
         0         58
         1         59
         2         59
         3         59
         4         59
         5         59
         6         59
         7         59
         8         59
         9         59

10 rows selected.
17 ноя 20, 09:48    [22233504]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
Реализация уникальности через триггер - идея заведомо обречённая на провал, т.к. есть операции при которых триггер не будет задействован.

Можно сделать, что вы и продемонстрировали, кучу кода для вырожденного случая, но не более того.
17 ноя 20, 09:53    [22233507]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
НеофитSQL
SY,

Спасибо за иллюстрацию!

В этом коде триггер пишет в таблицу, это выглядит невероятно взрывоопасно.

Я так понял глобальная переменная помогает избежать бесконечной рекурсии триггера,
но неужели кто-то такое пишет?


За всю Одессу я не скажу. Мой пример просто иллюстрация что в принципе таки можно.

SY.
17 ноя 20, 13:36    [22233682]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
env
Реализация уникальности через триггер - идея заведомо обречённая на провал, т.к. есть операции при которых триггер не будет задействован.

Можно сделать, что вы и продемонстрировали, кучу кода для вырожденного случая, но не более того.


Я не сомневаюсь что самодельная реализация будет проигрывать встроенной, тут иллюзий нет.
Мой constraint не покажется в списке constraints, его можно отключить деактивацией триггера, со вспомогательными таблицами можно проводить манипуляции, и т.д.

Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.
Уникальность была выбрана, потому что это наипростейший из встроенных, и его поведение хорошо описано.

В данный момент я разбираюсь, в какой очередности select... for update отпускает сессии которые на нем застряли,
и есть ли способ узнать попал ли select.. for update на лок, или успешно отработал.
Мне не нравится моя конструкция с "nowait/retry", из-за возможных race conditions, а сериализировать эту часть кода нельзя.

 function WaitOnLock return integer is
    n integer;
  begin
    select 1 into n from test_u where id = :new.uq for update nowait;
    return 0; -- first lock, no wait
  exception
    when others then -- catching ORA-00054 resource busy and NOWAIT specified
      select 0 into n from test_u where id = :new.uq for update;
      return 1;
  end WaitOnLock;
17 ноя 20, 14:06    [22233722]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
Из моих экспериментов, Оракл соблюдает очередность для select.. for update, и мой row-locking код значительно упростился.
Чтобы не утомлять публику частыми апдейтами, дам триггеру еще помариноваться.

Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?

Эффективно - значит без table lock или строгой сериализации доступа к таблице.
17 ноя 20, 14:15    [22233739]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5   вперед  Ctrl      все
Все форумы / Oracle Ответить