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

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


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

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

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

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

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

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

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

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

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

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



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

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


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

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

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

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



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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

Это сообщение отредактировал(а) Zloxa - 7.7.2011, 09:51

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

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

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