Можно ли откатить операторы CREATE TABLE и ALTER TABLE в основных базах данных SQL?

Я работаю над программой, которая выдает DDL. Я хотел бы знать, можно ли откатить CREATE TABLE и аналогичный DDL в

  • Postgres
  • MySQL
  • SQLite
  • et al

Опишите, как каждая база данных обрабатывает транзакции с помощью DDL.


sql transactions create-table ddl
person joeforker    schedule 14.01.2011    source источник
comment
Чтобы дополнить эту тему, H2 также не поддерживает операторы транзакционного DDL для большинства Команды SQL в соответствии с этим.   -  person Gabriel Paim    schedule 09.04.2019

Ответы (5)


arrow_upward
159
arrow_downward

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis этот вопрос с точки зрения PostgreSQL.

Является ли DDL транзакционным согласно этому документу?

  • PostgreSQL - да
  • MySQL - нет; DDL вызывает неявную фиксацию
  • Oracle Database 11g Release 2 и выше - по умолчанию нет, но существует альтернатива, называемая переопределением на основе выпуска
  • Старые версии Oracle - нет; DDL вызывает неявную фиксацию
  • SQL Server - да
  • Sybase Adaptive Server - да
  • DB2 - да
  • Informix - да
  • Firebird (Interbase) - да

В SQLite также есть транзакционный DDL. Мне удалось ROLLBACK выражение CREATE TABLE в SQLite. В ее CREATE TABLE документации не упоминаются какие-либо особые транзакционные "подводные камни".

person joeforker    schedule 19.01.2011
comment
Однако драйвер Python по умолчанию для sqlite предотвращает транзакционный SQL. bugs.python.org/issue10740 - person joeforker; 17.09.2013
comment
Итак, ответ - да, их можно откатить, если вы не используете MySQL или более старые версии Oracle. - person rjmunro; 24.04.2015
comment
Нет, кроме перечисленных, существуют и другие базы данных SQL. - person joeforker; 30.04.2015
comment
Других основных баз данных SQL нет :-) - person rjmunro; 01.05.2015
comment
В MariaDB есть открытая проблема для добавления поддержки транзакционного DDL: jira.mariadb.org/browse/MDEV -4259. Пожалуйста, проголосуйте за это. - person Gili; 13.04.2017
comment
Несколько ограниченный оператор ALTER TABLE SQLite также можно откатить. Это не упоминается явно в документации. Там упоминается, как выполнять расширенные изменения внутри транзакции. - person Thomas; 07.07.2017
comment
А как насчет Microsoft SQL? - person Thorkil Værge; 27.07.2018
comment
SQL Server - это то же самое, что Microsoft SQL? - person joeforker; 31.07.2018
comment
H2: нет (большинство операторов языка определения данных (DDL), таких как создание таблицы, фиксируют текущую транзакцию) - person Slava Semushin; 17.10.2018

arrow_upward
32
arrow_downward

PostgreSQL имеет транзакционный DDL для большинства объектов баз данных (конечно, таблиц, индексов и т. Д., Но не баз данных, пользователей). Однако практически любой DDL получит ACCESS EXCLUSIVE блокировку на целевой объект, что сделает его полностью недоступным до завершения транзакции DDL. Кроме того, не все ситуации полностью обрабатываются - например, если вы попытаетесь выбрать из таблицы foo, в то время как другая транзакция отбрасывает ее и создает таблицу замены foo, тогда заблокированная транзакция, наконец, получит ошибку, а не найдет новую foo таблицу. (Изменить: это было исправлено в PostgreSQL 9.3 или ранее)

CREATE INDEX ... CONCURRENTLY является исключительным, он использует три транзакции для добавления индекса в таблицу, позволяя одновременные обновления, поэтому сам по себе он не может быть выполнен в транзакции.

Также в транзакции нельзя использовать команду обслуживания базы данных VACUUM.

person araqnid    schedule 14.01.2011
comment
Я бы сказал, что если я попытаюсь выбрать из таблицы foo, когда другая транзакция отбрасывает и воссоздает ее, то я принимаю старую версию или ошибку. Меня не устраивает новая версия, потому что она еще не зафиксирована, поэтому я не должен ее видеть. Я нормально отношусь к ошибке, потому что при параллельном транзакционном доступе нужно быть готовым к перезапуску транзакций в любом случае. Если ошибки случаются чаще, чем необходимо, это может снизить производительность, но это все равно верно. - person Jan Hudec; 04.06.2014
comment
@JanHudec: вы не увидите незафиксированную версию новой таблицы, только результат всей транзакции, которая сбросила / воссоздала ее. то есть транзакция, которая удаляет, воссоздает и повторно заполняет таблицу, является фактически атомарной по отношению к другим процессам, выбирающим из этой таблицы. (но все будет заблокировано, как только они попытаются прочитать схему таблицы) - person araqnid; 04.06.2014

arrow_upward
7
arrow_downward

Невозможно сделать с MySQL, кажется, очень глупо, но это правда ... (согласно принятому ответу)

«Оператор CREATE TABLE в InnoDB обрабатывается как одна транзакция. Это означает, что ROLLBACK от пользователя не отменяет операторы CREATE TABLE, сделанные пользователем во время этой транзакции».

https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Пробовал несколько разных способов и просто не откатывается ..

Чтобы решить эту проблему, просто установите флаг ошибки и выполните команду «drop table tblname», если один из запросов завершился неудачно.

person Robert Sinclair    schedule 14.11.2017
comment
Проклятие. Я пытался понять, почему ранее созданные таблицы не исчезают, когда конкретная (создать) таблица выходит из строя в течение последнего часа. Я использую MariaDB (XAMPP переключился с MySQL на MariaDB), но случай такой же. Это глупо: | - person akinuri; 03.06.2020
comment
К сожалению, начиная с версии 10.5, MariaDB по-прежнему не поддерживает транзакционный DDL, см. это для подробностей. - person Han; 11.06.2021

arrow_upward
4
arrow_downward

Хотя это, строго говоря, не «откат», в Oracle команду FLASHBACK можно использовать для отмены этих типов изменений, если база данных была настроена для ее поддержки.

person Dave Costa    schedule 14.01.2011

arrow_upward
4
arrow_downward

Похоже, другие ответы устарели.

As of 2019:

  • Postgres поддерживает транзакционный DDL для многих выпусков.
  • SQLite поддерживает транзакционный DDL для многих выпусков.
  • MySQL поддерживает атомарный DDL начиная с версии 8.0 (который был выпущен в 2018 г.).
person PaulMest    schedule 24.06.2019
comment
Следует отметить, что атомарный DDL в MySQL 8 относится только к атомарным операторам DDL, но не к операторам транзакций. Оператор DDL, атомарный или нет, в большинстве случаев по-прежнему вызывает неявную фиксацию и, следовательно, не может быть выполнен в другой транзакции (например, START TRANSACTION ... COMMIT;. Таким образом, вы по-прежнему не можете откатить операторы DDL в транзакции, если последний в той же транзакции терпит неудачу. (См. Примечание в разделе dev.mysql.com/doc/refman /8.0/en/) - person Lacek; 27.12.2019