KEY VIOLATION при выполнении процедуры.

← →
Vuk (2004-01-21 14:38) [0]

Давно не ковырялся с Interbase, но тут пришлось...
Результаты странные. Получил KEY VIOLATION при выполнении поцедуры со следующей логикой:
CREATE PROCEDURE SomeProc( KeyField1 INTEGER, KeyField2 INTEGER, value INTEGER) AS begin if (exists(select 1 from SomeTable where (KeyField1 =:KeyField1) and (KeyField2 = :KeyField2))) then update SomeTable set Value = :Value where (KeyField1 = :KeyField1) and (KeyField2 = :KeyField2); else insert into SomeTable ( KeyField1, KeyField2, Value ) values( :KeyField1, :KeyField2, :Value ); end

У кого-нибудь есть мысли, как такое может получиться?

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

← →
jack128 (2004-01-21 14:44) [1]

> select 1 from
??
value - зарезервированиое слово
А полный текст ошибки какой?

← →
Johnmen (2004-01-21 14:49) [2]

>Vuk © (21.01.04 14:38)

Ошибка устойчива ? Работает один клиент ?

← →
Vuk (2004-01-21 15:03) [3]

to jack128:
>select 1 from
>??
Какая разница что выбирать? Можно имя поля написать, только зачем? Проверяется-то только наличие записи.

>value - зарезервированиое слово
Я ж написал - это описание логики. Можно было бы и догадаться, что я подставил это слово вместо реального названия.

to Johnmen:
>Ошибка устойчива ? Работает один клиент ?
В том-то и дело, что нет. При следующей такой же (даже по составу данных) транзакции все прошло нормально. Пока повторов ошибки не было. Просто интересно, как вообще такое может получиться.
Клиент на обновление был запущен только один.

← →
Vuk (2004-01-21 15:05) [4]

Да, полный текст ошибки:
lock conflict on no wait transaction
violation of PRIMARY or UNIQUE KEY constraint "PK_WGOODSINFO" on table "WGOODSINFO"

← →
Johnmen (2004-01-21 15:19) [5]

>Vuk © (21.01.04 15:05)

Я думаю, что эта ошибка вследствие наличия в определенный момент конкурирующих транзакций. Причем конкурирующих за записи в указанной таблице... И это конкурирование приводит к конфликтной ситуации...

← →
Vlad (2004-01-21 15:22) [6]

> Vuk © (21.01.04 15:05) [4]

Вероятнее всего - тебе нужен уровень изоляции SNAPSHOT TABLE STABILITY
почитай тут:
http://www.ibase.ru/devinfo/ibxtrans.htm

← →
Sandman25 (2004-01-21 15:24) [7]

[4] Vuk © (21.01.04 15:05)

Судя по описанию, можно попробовать установить время ожидания, чтобы не было " no wait transaction"

← →
jack128 (2004-01-21 15:26) [8]

> Судя по описанию, можно попробовать установить время ожидания,
> чтобы не было "no wait transaction"
Тогда возможны deadlock"и.
Если у Vuk"a в программе действительно возникают конкурирующие тр-ции, то это весьма вероятно..

← →
Sandman25 (2004-01-21 15:29) [9]

[8] jack128 © (21.01.04 15:26)

установить время

SET LOCK MODE TO WAIT 10
Но это не для IB, конечно.

← →
Vuk (2004-01-21 15:54) [10]

to Johnmen:
Конкурирующих транзакций на обновление по идее не должно было быть. А выборки на обновления влиять не должны. К тому же ошибка то - key violation. Т.е. попытка вставить запись, которая нарушает PK. Но ведь вставка в процедуре происходит только в случае, если запись в таблице отсутствует. Или в IB такое сообщение об ошибке имеет другой смысл?

to Vlad:
>Вероятнее всего - тебе нужен уровень изоляции SNAPSHOT TABLE
>STABILITY
Вот это вполне может подойти по смыслу производимых изменений в БД. Сейчас стоит read commited.

← →
Johnmen (2004-01-21 16:07) [11]

>Vuk © (21.01.04 15:54)

Если нет, то странно...

>Но ведь вставка в процедуре происходит только в случае, если
>запись в таблице отсутствует

В описанном случае это не совсем так, я думаю. Потому, что между проверкой существования (SELECT..) и модифицирующими действиями (UPDATE..., INSERT...) проходит какое-то время, в течении которого другой запрос другой транзакции тоже что-то сделает...:)
Но это опять же, если более одной транзакции одновременно. А ты говоришь, что одна...

← →
Sandman25 (2004-01-21 16:13) [12]

Vuk

Хранимая с предпроверкой перед вставкой уязвима для подобных ошибок. Лучше пытайтесь сразу сделать update, и если он не прошел (RowsAffected=0), то значит, придется делать insert. Надеюсь, в IB есть аналог RowsAffected.

PS. По крайней мере в одной другой СУБД он есть.

← →
Johnmen (2004-01-21 16:20) [13]

>Sandman25 © (21.01.04 16:13)

Только наоборот :) Сначала insert, и если ошибка, то update.

← →
jack128 (2004-01-21 16:25) [14]

> Sandman25 © (21.01.04 16:13) [12]
> Johnmen © (21.01.04 16:20) [13]
На больших объмах тормоза будут, ого-го. Лудше комбинированный вариант.
if exists() then
insert ..
else
begin
update ...
when <не знаю, что тут писать :-) > do
begin
insert...
end
end
Хотя наверно, бредово выглядит..

А вчем причина ошибки, все таки не понятно..

← →
Johnmen (2004-01-21 16:35) [15]

>jack128 © (21.01.04 16:25)

Б ольшие тормоза как раз в приведенном автором примере - выполнение "лишнего" запроса (SELECT...).

← →
jack128 (2004-01-21 16:39) [16]

> Johnmen © (21.01.04 16:35) [15]

http://www.ibase.ru/devinfo/testiu.htm

← →
jack128 (2004-01-21 16:45) [17]

> > Johnmen © (21.01.04 16:35) [15]
Ты почему то не учитываешь, что операция записи значительно "тяжелее", чем операция чтения. А уж если, чтение индексированное...

← →
Johnmen (2004-01-21 16:45) [18]

>jack128 © (21.01.04 16:39) [16]

А ведь когда-то я читал это...:)
Наверное, авторы статьи правы. И тоже прав. А я нет...

← →
Johnmen (2004-01-21 16:52) [19]

>jack128 © (21.01.04 16:45)

Учитываю.
Кстати, примеры, приведенные в статье, не совсем "состыковываются" с приведенным Vuk. Причем принципиально-существенно. Посему подымаю свою реплику [15]
:)

← →
Sandman25 (2004-01-21 16:54) [20]

[13] Johnmen © (21.01.04 16:20)
[14] jack128 © (21.01.04 16:25)

Да нет, именно сначала update, а потом insert. И никакого замедления, потому что не будет ни Exception, ни затрат на подсчет RowsAffected.
Дело в том, что Informix для любой команды SQL всегда считает количество затронутых записей и получение этого числа - всего лишь вызов специальной функции, выполняющей чтение из специальной структуры, находящейся в RAM. Кстати, в эту же структуру INSERT заносит вставленное значение автоинкрементного поля.

Имеем нечто такое:

UPDATE table my_table SET my_value = pMyValue WHERE id = pMyId;
IF DBINFO("SQLCA.SQLERRD1") = 0 THEN
INSERT INTO my_table ...
END IF;

← →
Johnmen (2004-01-21 17:05) [21]

>Sandman25 © (21.01.04 16:54)

Мы же IB ковыряем. Хранимую процедуру. Узнать, сколько проапдейтилось, это проблема в рамках ХП...

← →
jack128 (2004-01-21 17:05) [22]

> Кстати, примеры, приведенные в статье, не совсем "состыковываются"
> с приведенным Vuk. Причем принципиально-существенно.
Это почему? Я разницу увидел, тоолько в том, что в статье в процедуре вставляется сразу много записей, а в у Vuk"a - одна. Ну так я и сказал, что "при больших объемах"

> UPDATE table my_table SET my_value = pMyValue WHERE id =
> pMyId;
/*В этот момент другой клиент вставляет запись с нашим PK*/
> IF DBINFO("SQLCA.SQLERRD1") = 0 THEN
> INSERT INTO my_table ... /*здесь ошибка*/
> END IF;

← →
Vuk (2004-01-21 17:07) [23]

to Johnmen:
>Но это опять же, если более одной транзакции одновременно. А ты
>говоришь, что одна...
А даже если и не одна. В режиме read commited, если одна транзакция нарвется на данные, меняемые другой транзакцией, получим deadlock. То есть должна была пройти именно вставка, но такой транзакции быть не могло однозначно - шла отладка приложения и оно было только на одной машине. Могли ли повлиять предыдущие транзакции, результатом которых была вставка строк в эту же таблицу? Думаю вряд ли, все-таки режим read commited. Может, конечно, как-то помешал открытый IBExpert... Но опять же транзакций на обновление/вставку оттуда не было...

>Большие тормоза как раз в приведенном автором примере -
>выполнение "лишнего" запроса (SELECT...).
Это по PK тормоза? Нафига тогда такой сервер, если выборки по PK в нем тормозят?

to Sandman25:
>Надеюсь, в IB есть аналог RowsAffected.
Вот именно этого-то там как раз и нет.

to jack128:
>А вчем причина ошибки, все таки не понятно..
Во-во. :o) Было б понятно - не писал бы.

В общем - фиг с ним. Пока больше проблема не повторялась. Может больше и не повторится. Спишем на полтергейст. :o)

← →
jack128 (2004-01-21 17:08) [24]

> Johnmen © (21.01.04 17:05) [21]

Вообще, то многие пишут IB6.x имея в виду все клоны :-)
А в FB1.5 есть ROW_COUNT

← →
Johnmen (2004-01-21 17:18) [25]

>jack128 © (21.01.04 17:05)

Да. Это понятно...
Возможно, именно из-за "долгой" обработки ексепшенов...

>Vuk © (21.01.04 17:07)
>Спишем на полтергейст.

Разумно, однозначно...:))))))

← →
Sandman25 (2004-01-21 17:19) [26]

[22] jack128 © (21.01.04 17:05)

И в чем проблема? Или нужно засунуть данные невзирая на UNIQUE INDEX? :)
Я к чему это написал вообще? К тому, чтобы отказаться от проверки (select count(*)) и за счет этого ускорить ХП.

[23] Vuk © (21.01.04 17:07)
Вот именно этого-то там как раз и нет.
Жаль. Понятно.

← →
Johnmen (2004-01-21 17:20) [27]

>jack128 © (21.01.04 17:08)
>А в FB1.5 есть ROW_COUNT

Можно поподробней ? С киданием куска кода, как многие любят :))))

← →
Vuk (2004-01-21 17:22) [28]

to Sandman25:
>К тому, чтобы отказаться от проверки (select count(*))
А вот select count(*) и exists(select 1...) - это две большие разницы, т.к. первое приводит к выборке всех записей по условию, а второе - только одной.

← →
Sandman25 (2004-01-21 17:23) [29]

Vuk

В-общем, такие ошибки время от времени возникают при многопользовательской работе. И это нормально. Просто 2 человека захотели добавить одни и те же данные. В этом случае у второго из них возникает указанная ошибка. Нужно ее всего лишь поймать и сообщить юзеру, что запись с указанными данными уже введена.

jack128

Я извиняюсь, я забыл контекст. Действительно, первоначально я пытался уменьшить вероятность такой ошибки.

← →
Sandman25 (2004-01-21 17:26) [30]

[28] Vuk © (21.01.04 17:22)

Согласен, невнимательно посмотрел на первый пост.
Впрочем, обычно это почти одно и тоже - все равно проверяется по уникальному индексу :)

← →
kaif (2004-01-21 17:28) [31]

Я здесь недавно видел вопрос насчет применения WHEN в хранимой процедуре. Нельзя сделать вставку, а в случае ошибки, обработав ее в WHEN в той же процедуре, сделать UPDATE? Я не работал с WHEN. Но по-моему это правильное решение, особенно если вставок больше, чем апдейтов. Кстати, не факт, что это будет медленнее работать...

← →
Sandman25 (2004-01-21 17:36) [32]

← →
jack128 (2004-01-21 17:44) [33]

> Johnmen © (21.01.04 17:20) [27]

CREATE PROCEDURE TEST
RETURNS (
ROW_COUNT1 INTEGER)
AS
begin
/* Procedure Text */
delete from addresses
where name = "test";
row_count1 = row_count;
suspend;
end процедура возвращает кол - во удаленных записей

А вот что пишут Changes.txt
* New ROWS_AFFECTED system variable (SF #451927).
Return number of rows affected by the last INSERT/UPDATE/DELETE statement.
Notes:
1. Available in PSQL only.
2. For any other statement than INSERT/UPDATE/DELETE, result is always zero.
Contributor(s):
Dmitry Yemanov
примещание: начиная с rc1 ROWS_AFFECTED переименованно в ROW_COUNT

← →
jack128 (2004-01-21 17:47) [34]

> kaif © (21.01.04 17:28) [31]
посмотри ссылку, которую я дал выше, там проведен тест на эту тему и показан АБОЛЮТНО лудший вариант для любого соотнашения апдейтов/вставок

← →
Johnmen (2004-01-21 17:49) [35]

← →
Petr V. Abramov (2004-01-21 18:03) [36]

> Vuk
> if (exists(select 1 from SomeTable where (KeyField1 =:KeyField1) and (KeyField2 = :KeyField2))) then
Ну, выдумать KV сервер сам не мог (надеюсь :), поэтому конкурирующая транзакция, скорее всего, была ( возможно, она откатилась)
Все далее изложенное имеет смысл, если isolation level - read_commited rec_version, что скорее всего.
> if (exists(select 1 from SomeTable where (KeyField1 =:KeyField1) and (KeyField2 = :KeyField2))) then...
Раз read_commited, if (exists(select ... смысла не имеет, т.к. select не увидит незакоммиченную запись с заданным ключом, процедура уйдет на insert. К тому времени запись может успеть закоммититься, и получаем subj. Если не успеет, subj получит конкурирующая транзакция, причем, с её точки зрения, "на ровном месте".

kaif © (21.01.04 17:28) [31]
Работать будет медленне, факт, проверенный. Но ничего не поделаешь, это выход, если нет row_count, RowsAffcted, SQL%rowcount и т.д.

← →
Vuk (2004-01-21 18:40) [37]

>Раз read_commited, if (exists(select ... смысла не имеет
Бр-р-р-р.... Проверку if exists имеет смысл делать в любом случае.

← →
jack128 (2004-01-21 19:39) [38]

> Бр-р-р-р.... Проверку if exists имеет смысл делать в любом
> случае.
Это как посмотреть ;-)
Никакой уровень изоляции не гарантирует, что приду я с IBE
между if (exists(...)) и insert"ом не добавлю запись с вашим PK. C этой точки зрения if (exists ) вообще не имеет смысла делать никогда ;-)
А если говорить только о вашем разрабатываемом приложении, то тут ИМХО нужен no_rec_version, который как раз гарантирует, что послt if (exists) не начнет апдейт "вашей" записи, но уж точно не кто не гарантирут, что я не вставлю, раньше Вас запись, которую хотите вставить.

← →
Vuk (2004-01-21 20:01) [39]

to jack128:
>Никакой уровень изоляции не гарантирует, что приду я с IBE
>между if (exists(...)) и insert"ом не добавлю запись с вашим PK.
Зато в любом случае получим Key Violation, если запись вставлена еще пару дней назад. :o)

← →
Petr V. Abramov (2004-01-21 20:28) [40]

> Vuk © (21.01.04 18:40) [37]
> Бр-р-р-р.... Проверку if exists имеет смысл делать в любом
> случае.
Проводим эксперимент:
create table t1 ( f1 integer, primary key(f1))
create procedure brrr as
begin
if (exists (select 1 from t1 where f1 = 1)) then
exception some_exception;
end;

Запускаем 2 транзакции, А и B, обе read_commited rec_version
20:06 Transaction A
insert into t1 values(1)
20:07 Transaction B
execute procedure brrr -- все тихо
20:08 Transaction A
commit
20:09 Transaction B
execute procedure brrr -- поднимается исключение

если, как советует jack128 ©, использовать no_rec_version, транзакция B подождет завершения A, чтобы определиться, "exists" или "не exists". Но no_rec_version - это не развязанные чтение с записью, со всеми прелестями

KEY VIOLATION при выполнении процедуры. Найти похожие ветки