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

[alex_127]

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.

Спасибо.

Благородный дон может использовать sp_getapplock как mutex. Но правильный ответ is this one: вам надо переделать задачу так чтобы у вас можно было использовать autoincrement.

[ALV00]

Я бы хотел генерировать PK изначально правильно, используя TRANSACTION and LOCK, и вызывать INSERT, и не проверять "если такой PK уже существует".

ТС, используйте sequence или identity column или оставьте все как есть. Можно локировать таблицу командой lock table in exclusive mode, это и будет ваш мьютекс. Снимается он коммитом или роллбэком. Но тогда будет вероятность что зависшая транзакция всех подвесит. То есть так делать некошерно. Скажите вашему начальству, что надо либо делать правильно, либо пусть от...тся.

Кстати, проанализируйте, что будет происходить при primary key violation. Прерывание как-то обрабатывается в аппликейшн? Повтор операции решает проблему? Я просто работал с большой продакшн системой, и там многие таблицы заполнялись точно так же, id=max(id)+1. Вроде ничего ужасного не происходило, ну может пользователь получал сообщение об ошибке раз в квартал. Nobody cared.

[Albert_al]

Мои два цента :
Лично я отказался от использования в Оракле sequence где это возможно. Если надо вставлять записи очень много и очень часто, то в качестве первичного ключа использую колонку с типом данных raw(16) и default value sys_guid() :

create table my_tab
(
my_id raw(16) default sys_guid(),
field1 varchar2(100)
);
Работает быстрее, но многие девелоперы не понимают тип данных raw и поэтому просят number в качестве PK.

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

[Uzito]

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

Спрашиваю для друга: а что, в MS SQL совсем плохо с sequence? В оракле таким гимором заниматься не нужно.

[ie]

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

Спрашиваю для друга: а что, в MS SQL совсем плохо с sequence? В оракле таким гимором заниматься не нужно.

в sql server есть auto-increment fields о чем
уже неоднократно тут говорили. works like a charm.
но по неопнятным причинам TC выбрал сложный путь..

[Don Cherry]

Все предложения сводятся к "Используй auto increment".
MySQL - auto increment
SQL Server - identity
Oracle - sequence

А что если в app [в Java коде] нужен этот новый PK для business logic, например
- сделать его частью associated photo or document filename, или
- использовать этот новый PK как параметер в функции, которая что-то делает с этой записью в одной или многих таблицах, или
- нужно делать INSERT этого нового PK в другие таблицы для каких-то linking целей, или
- куча других нужд
В MySQL и SQL Server его не достать, он внутри.
В Oracle можно выкрутиться, например так
CREATE SEQUENCE seq_my
...
declare @ItemID INT
SET @ItemID = seq_my.nextval
INSERT INTO MyTable (ItemID,...) VALUES (@ItemID,...)
SELECT @ItemID

А как же вы пишите ваши apps не имея нового созданного PK в Java или C++ коде?
Это же типичная, требуемая, везде нужная business logic.
Чего-то вы тут не договариваете...

Мой изначальный пример/логика NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable) даёт доступ к PK.
Вопрос не в том чтобы переделать существующую db, сделать PK auto increment в таблицах,
а в том что новый PK нужен для business logic in Java or C++ коде.

[Вячеслав Викторович]

Все предложения сводятся к "Используй auto increment".
MySQL - auto increment
SQL Server - identity
Oracle - sequence

А что если в app [в Java коде] нужен этот новый PK для business logic, например
- сделать его частью associated photo or document filename, или
- использовать этот новый PK как параметер в функции, которая что-то делает с этой записью в одной или многих таблицах, или
- нужно делать INSERT этого нового PK в другие таблицы для каких-то linking целей, или
- куча других нужд
В MySQL и SQL Server его не достать, он внутри.
В Oracle можно выкрутиться, например так
CREATE SEQUENCE seq_my
...
declare @ItemID INT
SET @ItemID = seq_my.nextval
INSERT INTO MyTable (ItemID,...) VALUES (@ItemID,...)
SELECT @ItemID

А как же вы пишите ваши apps не имея нового созданного PK в Java или C++ коде?
Я не понимаю. Это же типичная, требуемая, везде нужная business logic.
Чего-то вы заумно не договариваете...

Мой изначальный пример/логика NewItemID = (SELECT MAX(ItemID)+1 FROM MyTable) даёт доступ к PK.
Вопрос не в том чтобы переделать существующую db, сделать PK auto increment в таблицах,
а в том что новый PK нужен для business logic in Java or C++ коде.

после инсерта сразу читай @@identity or SCOPE_IDENTITY() и все дела. Hibernate в entity сам после инсерта id заполняет.
в sybase аналогично. в oracle аналогично. не надо изобретать велосипед.
https://msdn.microsoft.com/en-us/librar ... .100).aspx

[Don Cherry]

после инсерта сразу читай @@identity or SCOPE_IDENTITY() и все дела. Hibernate в entity сам после инсерта id заполняет.
в sybase аналогично. в oracle аналогично. не надо изобретать велосипед.
https://msdn.microsoft.com/en-us/librar ... .100).aspx

Если подытожить, для multi-user systems, в самом общем приземлённом варианте,
- PK должен быть auto increment [правильный подход]
- Если PK не auto increment, то дизайн не правильный, надо исправить

Так?

И наверно на этом можно закрыть topic. Да и код можно было не обсуждать, в принципе - делай auto increment, по другому не правильно и трата времени.
Спасибо.

[alex_127]

после инсерта сразу читай @@identity or SCOPE_IDENTITY() и все дела. Hibernate в entity сам после инсерта id заполняет.
в sybase аналогично. в oracle аналогично. не надо изобретать велосипед.
https://msdn.microsoft.com/en-us/librar ... .100).aspx

Если подытожить, для multi-user systems, в самом общем приземлённом варианте,
- PK должен быть auto increment [правильный подход]
- Если PK не auto increment, то дизайн не правильный, надо исправить

Так?

И наверно на этом можно закрыть topic. Да и код можно было не обсуждать, в принципе - делай auto increment, по другому не правильно и трата времени.
Спасибо.

+1
Or PK is guid, or composite of other keys or driven by external logic (say VIN supplied externally).
But not max()+ 1 from table.

[Dmitry67]

Инкрементация IDENTITY в MS SQL происходит в Autonomous transaction
А вот SELECT MAX(ItemID)+1 FROM MyTable это гарантия, с одной стороны, превращения системы в однопоточную (на первой же долгой транзакции ВСЕ застрянет), а в случае разрыва транзакций бизнеслогикой - race conditions c получением неверного ID.

[Don Cherry]

Инкрементация IDENTITY в MS SQL происходит в Autonomous transaction
А вот SELECT MAX(ItemID)+1 FROM MyTable это гарантия, с одной стороны, превращения системы в однопоточную (на первой же долгой транзакции ВСЕ застрянет), а в случае разрыва транзакций бизнеслогикой - race conditions c получением неверного ID.

Я думал, что ответ еже получил. А при чём тут autonomous transaction?
В SQL Server создаю table structure
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
и в моих INSERT НЕ использую "ID", он генерируется автоматически.
Чего-то я опять не понял.

[Dmitry67]

А при чём тут autonomous transaction?
В SQL Server создаю table structure
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
и в моих INSERT НЕ использую "ID", он генерируется автоматически.
Чего-то я опять не понял.

Попробуйте сделающее.
insert into persons (lastname, firstname, age) select 'marge','Simpson',35
BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Hole','creator',0
ROLLBACK TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40

Вы увидите что Hole creator но создан (естественно), но между Marge & Homer дырка в нумерации.
Сделано это специально

Иначе следующий код вешал бы все инсерты в таблицу:

BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40
waitfor delay '01:00:00' -- ждем час
ROLLBACK TRAN

А вот код с max()+1 как раз на этом бы и вис.

[Don Cherry]

А при чём тут autonomous transaction?
В SQL Server создаю table structure
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
и в моих INSERT НЕ использую "ID", он генерируется автоматически.
Чего-то я опять не понял.

Попробуйте сделающее.
insert into persons (lastname, firstname, age) select 'marge','Simpson',35
BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Hole','creator',0
ROLLBACK TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40

Вы увидите что Hole creator но создан (естественно), но между Marge & Homer дырка в нумерации.
Сделано это специально

Иначе следующий код вешал бы все инсерты в таблицу:

BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40
waitfor delay '01:00:00' -- ждем час
ROLLBACK TRAN

А вот код с max()+1 как раз на этом бы и вис.

Я не достаточно умён, пожалйуста скажите сначала в принципе в чём проблема
- использование IDENTITY в таблице [i.e academical knowledge]
или
- max() + 1 [который я не буду больше использовать]
а потом уже детали и код.
Если второе, зачем об этом разговаривать?

[Вячеслав Викторович]

А при чём тут autonomous transaction?
В SQL Server создаю table structure
CREATE TABLE Persons (
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
и в моих INSERT НЕ использую "ID", он генерируется автоматически.
Чего-то я опять не понял.

Попробуйте сделающее.
insert into persons (lastname, firstname, age) select 'marge','Simpson',35
BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Hole','creator',0
ROLLBACK TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40

Вы увидите что Hole creator но создан (естественно), но между Marge & Homer дырка в нумерации.
Сделано это специально

Иначе следующий код вешал бы все инсерты в таблицу:

BEGIN TRAN
insert into persons (lastname, firstname, age) select 'Homer','Simpson',40
waitfor delay '01:00:00' -- ждем час
ROLLBACK TRAN

А вот код с max()+1 как раз на этом бы и вис.

Я не достаточно умён, пожалйуста скажите сначала в принципе в чём проблема
- использование IDENTITY в таблице [i.e academical knowledge]
или
- max() + 1 [который я не буду больше использовать]
а потом уже детали и код.
Если второе, зачем об этом разговаривать?

ну охота человеку ещё по теме поговорить. ну пускай ещё что-нибудь скажет

[oleg lebedev]

Это не проблема, а просто особенность, что рано или поздно появятся дырки в последовательностях. Это не вызовет проблем с генерацией нового ключа, а вместо, скажем, 10,11,12,13,14 будет 10,11, 17,18, 21 и это надо знать заранее. Дима об этом и сказал.

[oleg lebedev]

Это не лечится никак ни в Оракле, ни в Sybase, ни в MSQL Server. Даже в postgres где можно откатить всё остальное, включая truncate и DDL. Вам уже объснили механизм. Sequence никогда нельзя откатить назад. Cache parameter исключительно для performance и образование дырок он не вылечивает. Попробуйте вставить что-то из sequence и затем откатить транзакцию.

[Dmitry67]

Это не лечится никак ни в Оракле, ни в Sybase, ни в MSQL Server. Даже в postgres где можно откатить всё остальное, включая truncate и DDL. Вам уже объснили механизм. Sequence никогда нельзя откатить назад. Cache parameter исключительно для performance и образование дырок он не вылечивает. Попробуйте вставить что-то из sequence и затем откатить транзакцию.

Для OP: Именно, это фича

P.S.
Из занудства отмечу что в MS SQL тоже truncate и DLL откатывается.

[oleg lebedev]

Я попробовал сегодня на работе на MS SQL дропнуть таблицу и откатиь и действительно, откатил.
Судя по тому что MS SQL была калька с Sybase, я могу предположить, что Sybase тоже откатывает, правильно?

[alex_127]

Я попробовал сегодня на работе на MS SQL дропнуть таблицу и откатиь и действительно, откатил.
Судя по тому что MS SQL была калька с Sybase, я могу предположить, что Sybase тоже откатывает, правильно?

Sybase and MS SQL can be more different then you and Adam...

http://dcx.sap.com/1001/en/dbrfen10/rf- ... ement.html

Side effects
Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. DROP TABLE, DROP VIEW, DROP MATERIALIZED VIEW, and DROP INDEX close all cursors for the current connection.

[ALV00]

Sequence никогда нельзя откатить назад.

Но можно инициализировать любым числом.

[oleg lebedev]

Sequence никогда нельзя откатить назад.

Но можно инициализировать любым числом.

И как это может помочь в том чтобы решить проблему дырок в последовательностях? Надеюсь, вы не предлагаете инициализировать последовательность на каждой дырке, которая может иметь место в production?

[ALV00]

И как это может помочь в том чтобы решить проблему дырок в последовательностях? Надеюсь, вы не предлагаете инициализировать последовательность на каждой дырке, которая может иметь место в production?

Ну да, дырки штопать только ручками.

ps: а для кого то проблематичны дырки? Я с таким сталкивался только в российском бухучете.

[oleg lebedev]

И как это может помочь в том чтобы решить проблему дырок в последовательностях? Надеюсь, вы не предлагаете инициализировать последовательность на каждой дырке, которая может иметь место в production?

Ну да, дырки штопать только ручками.

ps: а для кого то проблематичны дырки? Я с таким сталкивался только в российском бухучете.

Как правило в этом нет проблем, но есть люди, которые делают свой дизайн на предположении что связь между текущей строчой и следующей всегда будет как NEXT=CURRENT + 1.
Для них открытие дырок в последовательностях может быть очень неприятным.

[Dmitry67]

Таких людей надо гнать

[oleg lebedev]

Таких людей надо гнать

Их гонят в дверь, а они через окно.