Вопрос про T-SQL или PL/SQL

[Don Cherry]

MyTable
ItemID Integer [Primary Key]
... other fields

#1 DECLARE @NewItemID INT
#2 SET @NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable)
#3 INSERT INTO MyTable (ItemID, …) VALUES (@NewItemID, …)

Мне нужно, чтобы этот код работал правильно в multi-user environment, i.e. concurrency, synchronization, simultaneous access.
Подскажите/научите, как добавить и использовать две разные вещи - TRANSACTION и LOCK,
чтобы когда один User выполняет этот код, другие Users блокируются и ждут.
Чтобы при выполнении этого кода, между строками #2 и #3 не возникла проблема “Oh…, such key value already exists [i.e. such ItemID created by another User], primary index duplication…”.

Идея как Mutex locking.

Спасибо.

[ALV00]

Проблема в том, что нужен какой-то объект блокировки. Если в начале транзакции записи еще не существует, то что блокировать?
Варианты:

1. lock table in exclusive mode. Это плохо по понятным причинам
2. ставить максимальный уровень изоляции. Это тоже блокирует таблицу
3. использовать sequence - специальный объект как раз для таких случаев

[Don Cherry]

Мой вопрос в принципе, для всех баз данных “в мире”, с multi-user access, concurrency, … как пишется код – блокировать таблицу или нет?

В моём случае в базе данных 100 таблиц и 50 пользователей, в каждой таблице надо добавлять запись. А в некоторых случаях добавлять в несколько таблиц сразу
BEGIN TRANSACTION
...
INSERT... table1
INSERT... table2
...
COMMIT TRANSACTION

А у вас пример кода есть, не могли бы прислать?
Спасибо.

[oleg lebedev]

MyTable
ItemID Integer [Primary Key]
... other fields

#1 DECLARE @NewItemID INT
#2 SET @NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable)
#3 INSERT INTO MyTable (ItemID, …) VALUES (@NewItemID, …)

Мне нужно, чтобы этот код работал правильно в multi-user environment, i.e. concurrency, synchronization, simultaneous access.
Подскажите/научите, как добавить и использовать две разные вещи - TRANSACTION и LOCK,
чтобы когда один User выполняет этот код, другие Users блокируются и ждут.
Чтобы при выполнении этого кода, между строками #2 и #3 не возникла проблема “Oh…, such key value already exists [i.e. such ItemID created by another User], primary index duplication…”.

Идея как Mutex locking.

Спасибо.

Для решения этой проблемы вы должны сами понимать, что нужно предпринять, если выяснится, что unique constraint уже существует. Есть, как правило 2 выхода:
1. сделать update уже существующей строки с новыми значениями от сессии, которая пытается сделать insert
2. проигнорировать эту строку.

Некоторая логика может дополнять эти 2 решения. Сделать update если timestamp или новая строка имеет более высокий приоритет на основе некого критерия, в противном случае - просто проигнорировать.
Для Оракла пример:

CREATE OR REPLACE PROCEDURE insert_update ( key_IN VARCHAR2, val_IN NUMBER := NULL )
AS
BEGIN
Begin
insert into my_table ( id, name) values( key_IN, val_IN );
Exception
When DUP_VAL_ON_INDEX Then

update my_table
set name = val_IN
where id = key_IN;

-- NULL;
When Others Then
Raise;
End;
commit;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

Предполагается, что таблица my_table имеет unique constraint на колонку id.
Также этот пример для случая, когда происходит update. Если хотите проигнорировать insert, то вместо update нужно поставить NULL;

[Don Cherry]

Я прошу прощения, серьёзно и честно, но ваш ответ слишком "интеллектуальный" для меня, и не выглядит как элементарно-базовое решение для INSERT для всех баз данных "в мире" с multi-user access.

Нужно блокировать таблицу или нет?
Пожалуйста напишите код для блокирования таблицы для всех actions внутри transaction.

Хождение по Google и Yahoo не даёт елементарных знаний/ответов.

[ie]

Мой вопрос в принципе, для всех баз данных “в мире”, с multi-user access, concurrency, … как пишется код – блокировать таблицу или нет?

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

насколько я понял, вы пытаетесь сгенерить PK?
в SQL Server удобно использовать Auto-Inctement fields.

еще как вариант что-то типа такого:
inset into MyTable (ItemID)
SELECT MAX(ItemID)+1 FROM MyTable

BEGIN TRANSACTION
...
INSERT... table1
INSERT... table2
...
COMMIT TRANSACTION

такие вещи чреваты дедлоками и проблемами с перформансом.
особенно если вы не знаете что вы делаете.

[ie]

как пишется код – блокировать таблицу или нет?

опять же если мы говорим о SQL Server, SyBase или Oracle то в 99% случаев блокировать руками нечего не надо.
сервер сам за вас сделает эту работу. кончено можно оптимизировать это все...
и мудроствовать лукаво.. но лучше это оставить на потом.

[oleg lebedev]

Естественно, блокировать рукми совсем необязательно. Вы совершенно правы.
Любой DML ( ins,upd,del) - начало новой транзакции даже если она не начата с "start transaction".
ТС должен для себя чётко понять, что он хочет достигнуть, когда несколько сессий пытаются вставить записи с теми же самыми constraints. У меня впечатление, что он не понимает желаемый конечный результат работы своего кода и в этом главная проблема. Любую цель можно достигнуть в этом сценарии, просто нужно понять что.

[Don Cherry]

как пишется код – блокировать таблицу или нет?

опять же если мы говорим о SQL Server, SyBase или Oracle то в 99% случаев блокировать руками нечего не надо.
сервер сам за вас сделает эту работу. кончено можно оптимизировать это все...
и мудроствовать лукаво.. но лучше это оставить на потом.

Если я использую код
#1 DECLARE @NewItemID INT
#2 SET @NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable)
#3 INSERT INTO MyTable (ItemID, …) VALUES (@NewItemID, …)

то db server НЕ делает сам эту работу, поэтому я задаю вопрос в форуме, про transaction and locking.
И прошу ответ для "идиота", для меня.

Что нужно добавить в этот код,чтобы не было конфликта между users при создании PK?
Нужно блокировать table? Если да, то как?

BEGIN TRANSACTION

DECLARE @NewItemID INT
SET @NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable)
INSERT INTO MyTable (ItemID, …) VALUES (@NewItemID, …)

COMMIT TRANSACTION

[oleg lebedev]

Ход мысль правильный, но при таком раскладе вы всё равно можете ( а в реальности точно получите ) ошибку с constraint violation.
На базе происходят следующие вещи в условиях когда есть несколько сессий пытающихся вствить строчку с те же РК.
1. Сессия 1: вставляет id=1 ( без commit)
2. Сессия 2. вставляет id=1 и сlient ждёт результат Step 1.Если commit, то шаг 2 вылитит с ошибкой. Если rollback, то шаг 2 может совершть commit.

Если шаг 1 уже сделал commit, то шаг 2 гарантированно производит ошибку.

Вам здесь предложили разрешить конфликт несколькими способами. То ли с назначением PK для каждой строчки, то ли проигнорировать insert из шага 2 или сделать update.
Ваше решение - выбрать метод разрешения этого конфликта и всё.

[Don Cherry]

Естественно, блокировать рукми совсем необязательно. Вы совершенно правы.
Любой DML ( ins,upd,del) - начало новой транзакции даже если она не начата с "start transaction".
ТС должен для себя чётко понять, что он хочет достигнуть, когда несколько сессий пытаются вставить записи с теми же самыми constraints. У меня впечатление, что он не понимает желаемый конечный результат работы своего кода и в этом главная проблема. Любую цель можно достигнуть в этом сценарии, просто нужно понять что.

Компьютерный Мир делает, я отстаю и хочу понять как "желаемый конечный результат работы своего кода"
- создавать PK [Primary Key]
и
- вставлять записи
в multi-user environment без конфликта, i.e. когда несколько сессий пытаются вставить записи, с разными PK и разными constraints.

Ну пожалуйста напишите пример кода, и я перестану задавать елементарные/идиотские вопросы и тратить ваше время.

[Don Cherry]

Ход мысль правильный, но при таком раскладе вы всё равно можете ( а в реальности точно получите ) ошибку с constraint violation.
На базе происходят следующие вещи в условиях когда есть несколько сессий пытающихся вствить строчку с те же РК.
1. Сессия 1: вставляет id=1 ( без commit)
2. Сессия 2. вставляет id=1 и сlient ждёт результат Step 1.Если commit, то шаг 2 вылитит с ошибкой. Если rollback, то шаг 2 может совершть commit.

Если шаг 1 уже сделал commit, то шаг 2 гарантированно производит ошибку.

Вам здесь предложили разрешить конфликт несколькими способами. То ли с назначением PK для каждой строчки, то ли проигнорировать insert из шага 2 или сделать update.
Ваше решение - выбрать метод разрешения этого конфликта и всё.

Мне нужно разные PK для новых строк. В этом весь смысл. table structure with Primary Key.

[oleg lebedev]

Вам уже здесь всё написали.
Я вам дал работающий пример из Оракла как разрешается конфликт.
IE вам рассказал, что можно назначать PK из sequence и в этом случае конфликта не происходит.
Также можно вставлять все записи в промежуточную таблицу без PK, а затем пытаться разрешить конфликтующие строчки и вставить их в основную таблицу. Но для этого нужно написать свой код для разрешения конфликтов.

[oleg lebedev]

IE вам уже написал что можно использлвать auto increment тип ( IDENTITY )в Sybase или Sql Server. В Оракле этого нет, то там это легко решается путём создания sequence + простейшего триггера. Примеров на интернете - масса.

[Don Cherry]

IE вам уже написал что можно использлвать auto increment тип ( IDENTITY )в Sybase или Sql Server. В Оракле этого нет, то там это легко решается путём создания sequence + простейшего триггера. Примеров на интернете - масса.

В моём случае, мне нужно New PK = MAX PK + 1
Auto increment нельзя.
Ваш пример кода очень сложный.

[oleg lebedev]

Мой пример - элементарный. Он говорит, вставь строчку в таблицу, а если этот PK существует, то делай update.

Если вам нужно New PK = MAX PK + 1, то вам нужно использовать IDENTITY datatype :
https://stackoverflow.com/questions/677 ... r-database

В оракле это делается чуть сложнее:
https://stackoverflow.com/questions/112 ... -on-oracle

Вы должны понимать, что в общем случае New PK = MAX PK + 1 не будет соблюдаться строго. Иногда будут "дыры" между ID's, но это не приведет к PK violation.

[Don Cherry]

Мой пример - элементарный. Он говорит, вставь строчку в таблицу, а если этот PK существует, то делай update.

Если вам нужно New PK = MAX PK + 1, то вам нужно использовать IDENTITY datatype :
https://stackoverflow.com/questions/677 ... r-database

В оракле это делается чуть сложнее:
https://stackoverflow.com/questions/112 ... -on-oracle

Вы должны понимать, что в общем случае New PK = MAX PK + 1 не будет соблюдаться строго. Иногда будут "дыры" между ID's, но это не приведет к PK violation.

re: Мой пример - элементарный.
Я бы хотел генерировать PK изначально правильно, используя TRANSACTION and LOCK, и вызывать INSERT, и не проверять "если такой PK уже существует".
Ваш пример предлагает анализировать генерированный PK, поэтому для меня он "сложный", требует программирования.
Я упомянул "Mutex logic" в моём изначальном вопросе.

[oleg lebedev]

Я плохо понимаю, что означает "генерировать PK изначально правильно".
Если у вас 2 ли больше сессий и они могут назначить один PK, то как вам может помочь LOCK на таблицу?
Допустим, у вас 2 сессии и одна гарантировано генирирует PK с чётным число, а др. - с нечётным вы можете избежать конфликт. Если используете autoincrement datatype - тоже и ряд др. подходов, которые могут избежать конфликта до insert. Но если сессии могут создавать те же самые ключи, то вам надо писать код как разрешить конфликт еслт такой случится.

[oleg lebedev]

То, что вы предлагаете использовать без триггера - источник трудно отлавливаемых проблем в production. Допустм, кто-то вставил в ручную "insert into mytab (my_id, field1) values (10000, 'blah');"
Придёт время и попытка вставить срочку с sequence_id=10000 вызовет ошибку. Представьте, что это произошло в 3 часа ночи и от вас требуют срочно починить.
На performance этот триггер практически не влияет. Вряд ли вы заметете разницу на 100 - 1000 inserts в секунду.
Крайне не рекомендуется такой подход как вы предлагаете.

[oleg lebedev]

Не в 3 часа ночи пишут inserts. В 3 часа ночи у вас что-то завалилось от того, что кто-то когда-то вставил вручную когда-то дурацкую запись. Триггеры - зло, PK - зло, constraints - зло, всё влияет на производительность.

[oleg lebedev]

А если это был ваш босс? Тоже получит звездюлину?
Я не вижу смысла для дискуссий на этом subj. Если вы знаете, что делаете - то вам и расхлёбывать если такое случится. Я против таких советов как универсальное решение тем людям, кто не знает чем это чревато.

[ie]

BEGIN TRANSACTION

DECLARE @NewItemID INT
SET @NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable)
INSERT INTO MyTable (ItemID, …) VALUES (@NewItemID, …)

COMMIT TRANSACTION

вот этот код будет нормально работать на SQL Server.
при условии что MyTable -- не очень большая и не очень много юзеров вставляют/удаляют записи в ней.

select max(id) -- подозреваю будет блокировать всю таблицу
тоесть если таблица достаточно большая и/или на ней другие локи...
будет задержка, чем больше таблица и/или больше активность тем больше задержка.

в один прекрасный день (ночь) ваше приложение начнет умирать с криками timeout! timeout!

[oleg lebedev]

При этом коде 2 сессии могут получить одно и тоже значение для @NewItemID и соответственно конфликт. Начало транзакции и её окончание в явной форме абсолютно ничего не помогает.
Весь расчёт лишь на то, что время между select, insert - очень маленькое и др. транзакция не получит тоже самое значение для @NewItemID.

[oleg lebedev]

Мой босс в базу руками не лазит. Коллеги , перед тем, чтобы что то делать руками в продакшн, присылают скрипт на code review. Или разгребают то, что наворотили, сами. ИМХО нет лучшего способа научиться чем исправить свои собственные ошибки.

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

[oleg lebedev]

Я понимаю, что вы не согласитесь со мной никогда. Ваш пример с PL/SQL оставляет открытыми вопрос о том, откуда берутся значения для key_in значения. Что называется, "замнем для ясности". И вместо того, чтобы городить такой город с обработкой exceptions в вашей процедуре, гораздо проще использовать merge sql command. Будет быстрее, чем обработка нескольких exception. Conclusion : интервью бы вы у меня не прошли

key_IN берутся из внешней программы, например из Java с application server или др. PL/SQL модуля.
Merge будет существенно медленей для больших sets of data, т.к. быстро выполняется то что в RAM. Отдельно insert и update позволяет исполнять subsets of data и это будет быстрее. Проверено множество раз на настоящих, а не на игрушечных таблицах. К тому же debugging знаительно проще, если там вовлечено несколько таблиц.

Интервью у вас я никак не мог бы проходить, т.к. ваш уровень не позволяет это делать. Вы, судя по вашим суждениям, mid level developer, работающий на подхвате у индусов. Тот факт, что вы стали давать оценку моей квалификации на основе скудной информации из этого топика и ещё упоминули про интервью, свидетельствует, что у вас комплекс неполноценности. Вы, по всей видимости, страстно желаете его проводить, а вам не позволяют этого делать в связи с ничтожностью вашего положения в отделе. Вот и ваше неисполненное желание вылезло в такой уродливой и агрессивной форме.

Если б я искал человека в отдел на работу не требующую высокой квалификации, то я мог бы вполне вас взять. Всё ж вы что-то знаете, хоть и не глубоко. Про merge слыхали.