Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > СУБД, общие вопросы > Безопасное изменение ID у записи

Автор: WolfAlone 6.7.2011, 04:35

Доброго времени суток!

Стоит задача: перенести какую-то запись "в конец" таблицы. Для этого, на мой взгляд рациональнее всего, просто сменить ID этой записи на последний + 1. Выглядеть всё это будет примерно так:

Код


SELECT @max_id := (MAX(id)+1) FROM table;
UPDATE table SET id=@max_id WHERE id = 2;

Но тут сразу же возникает два вопроса:
1. Не случится ли однажды такое, что в один прекрасный момент, сможет затесаться другой конкурентный INSERT-запрос, между SELECT и UPDATE который добавит в таблицу какую-то запись, после чего @max_id уже перестанет значение последнего существующего ID в реальном времени?
2. После того, как я меняю ID какой-то записи на последний + 1, значение счётчика "AUTO INCREMENT" у таблицы не увеличивается! В виду чего, при следующем INSERT'e возникает ошибка о том, что запись с таким номером уже существует! (*при вставке записи, её ID я не указываю, т.к. именно для этого существует автоинкрементное поле [ID])

Первое, что приходит в голову - это изменить ID у всех записей в таблице. Но по моему, это как то не правильно... Если записей будет миллион и нужно будет переместить первую запись в конец таблицы, то предётся изменить ID у миллиона записей!? Возможно, есть какое-то более "красивое" решение?

Второе, что пришло в голову - это взять все данные (собрать в массив), удалить ту запись, которую нужно переместить в конец, а затем вставить её снова. Таким образом, обе проблемы отпадают, но... по моему это тоже как-то не совсем правильно! Зачем удалять данные и снова вставлять их же, если они уже есть в таблице?

Автор: WolfAlone 6.7.2011, 05:17

*в качестве примера, можно считать, что в роли Сервера БД - выступает MySQL 5.1/5.5

Автор: Zloxa 6.7.2011, 09:03

Цитата(WolfAlone @ 6.7.2011, 04:35

)

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

Случится.
Против конкурентного инсерта нет простого приема. MySQL, афайк не поддерживает аналог ораклиных сиквенсов и ФБшных генераторов. Можно похожий механизм реализовать самостоятельно, но тогда, по факту конкурентных инсертов происходить не будет,они будут строго упорядочены.

Если два запроса объединить в один и использовать режим изоляции read commited, то конкурентные апдейты сериализуются и не приведут к рассогласованию данных

Код


UPDATE table SET id=(SELECT coalesce((MAX(id)+1),1) FROM table) WHERE id = 2;

В этом же случае конкурентый инсерт производить средствами insert into table select coalesce(max(id)+1,1), :fld1, :fld2,:fld3 from table. Так же в режиме изоляции read commited. Но производительность такой вставки будет оставлять желать лучшего и инсерты будут таки не конкурентны а последовательны.

нууу о том что дизайн говен, думаю говорить не стоит, вы и сами это видите.

Автор: Akina 6.7.2011, 12:33

Цитата(WolfAlone @ 6.7.2011, 05:35

)

Для этого, на мой взгляд рациональнее всего, просто сменить ID этой записи на последний + 1.

А на мой взгляд, разумнее дополнительное поле, задающее порядок.

Автор: WolfAlone 6.7.2011, 15:06

Zloxa, скажите пожалуйста, если "проблему" попробовать решить вот таким вот образом:

Код


SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
UPDATE table SET id=(SELECT coalesce((MAX(id)+1),1) FROM table) WHERE id = 2;
COMMIT;

Это даст гарантию того, что никакой конкурентный запрос не "вклинится" и не нарушит целостность данных?

Автор: Zloxa 6.7.2011, 15:19

Точно не уверен, я не являюсь экспертом по mysql.
Мне кажется этот запрос таки заблокирует простой инсерт, но я не могу за то поручиться.
Практика - критерий истины. ))) Попробойте в одной сессии начать транзакцию, выполнить аптдейт и не коммититься.
Во второй сессии, просто выполните простой инсерт. Желателльно в диртириде. Если он встанет на блокировке - радуйтесь ))

Цитата(Akina @ 6.7.2011, 12:33

)

А на мой взгляд, разумнее дополнительное поле, задающее порядок.

Тут согласен. ПК для этих целей использовть не гоже. Лучше для этих целей использовать суррогатное поле. Однако же проблема конкурентной модификации ранга все равно останется.

Автор: WolfAlone 6.7.2011, 18:49

Что-то у меня никак не получается создать 2 одновременных конкурентных запроса, описанных выше. Подскажите пожалуйста ПО для реализации такого эксперемента! *Желательно под винду.

Автор: LSD 6.7.2011, 19:08

Цитата(WolfAlone @ 6.7.2011, 19:49

)

Любой SQL клиент у которого можно:
- открыть 2 сессии к базе
- отключить автокомммит

Автор: WolfAlone 6.7.2011, 19:18

LSD, пробовал SQL Maestro for MySQL и HeidiSQL (то, с чем работаю повседневно). Две сессии открываю в виде двух экзепмляров программы, отключить "автокоммит" и запустить тразакцию пытаюсь вот так:

Код


SET AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

Результат что-то пока нулевой...

В ответ получаю:

Код


/* 0 rows affected, 0 rows found. Duration for 3 queries: 0,093 sec. */

Никакой блокировки не происходит. Подозреваю, что что-то я делаю не так.

В настройках софта поставил "Keep connection alive", что бы не от отключался/подключался после каждого запроса.

Автор: WolfAlone 7.7.2011, 01:31

Помогите пожалуйста с экспериментом, что-то у меня не получается совсем!

Автор: Zloxa 7.7.2011, 09:10

совершенно не понятно чему там можно не получаться.
У меня нет ни одной инсталляции MySQL, но у меня есть подключение к MS SQL. MS SQL, как и МySQL является блокировочником и, очень вероятно, что они будут работать одинаково.

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

Код


use northwind
insert into Region values (777,'JustTestIt');

northwind - демонстрационная база данных, которая разворачивается автоматически, при установке sql сервера.
Я создал запись, над которой буду производить эксперименты.
Далее, в том же окне

Код


set transaction isolation level serializable
begin transaction
update region set RegionId = (select max(RegionID)+1 from region) where RegionId = 777

Открываю новое окно, это конкурируюая сессиия. Выполняю

Код


use northwind
set transaction isolation level read uncommitted
begin transaction
insert into region values (7777,'Hello')

С удовольствием наблюдаю, что insert завис - встал на блокировке. Значит моя гепотеза была верна.
Иду в первое окно
выполняю

Код


rollback

Радостно наблюдаю, что тот инсерт отвис и выполнился
повторно выполняю в этом же окне

Код


set transaction isolation level serializable
begin transaction
update region set RegionId = (select max(RegionID)+1 from region) where RegionId = 777

Радостно наблюдаю что теперь этот запрос завис(встал на блокировке).
Перехожу во второе окно, деаю там rollback, это запрос в первом окне завершается, делаю роллбэк и там

Резюме - можно пользовать, достаточность serializable доказана.

Теперь повторяю этот же эксперимент, но вместо serializable использую read committed. Первый же кейс не приводит к зависанию инсерта. Он происходит успешно, а значит может привести к потере согласованности данных. На момент фиксации транзакции, новоприсвоенный айди в базе уже будет не наибольшим.

Резюме - необходимость serializable доказана.

Однако все равно не понятно к чему весь этот пляс, если что делать с счетчиком автоинкремента - все равно не понятно.

Автор: Akella 7.7.2011, 12:58

Не играйся с ID smile

, может лучше будет просто сделать вставку, а старую запись удалить?

Код

Insert into table1 select from table1 where id = 2

Автор: WolfAlone 7.7.2011, 14:13

Akella, во истину мудрое решение! Спасибо!

P.S. Вопрос закрыт.