DML или команды манипулирования данными должны обрабатываться в SQL с особой осторожностью по той простой причине, что они каким-то образом модифицируют целевые данные. Обычно это делается с помощью операторов UPDATE или DELETE. В этом сообщении блога мы узнаем, как лучше защитить данные (и самих себя) при выполнении этих типов команд, используя SAVEPOINT в TRANSACTION.
Примечание: все данные, имена или наименования, найденные в базе данных, представленной в этом посте, строго используются для практики, обучения, обучения и тестирования. Он ни в коем случае не отображает фактические данные, принадлежащие или используемые какой-либо стороной или организацией.
ОС и БД используются:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 8.0.13
По умолчанию MySQL автоматически фиксирует все операторы при их запуске. Любые изменения с помощью команд DML записываются на диск и, следовательно, являются постоянными.
Однако вы можете (временно) изменить это «высечено на камне», введя команду START TRANSACTION. После включения, чтобы вернуть MySQL в режим автоматической фиксации, вы должны выполнить либо команду ROLLBACK, либо COMMIT, заканчивая или завершая TRANSACTION.
Чтобы сделать вещи еще лучше, в TRANSACTION также есть что-то, называемое SAVEPOINT. Вам просто нужно использовать его, потому что по умолчанию его нет.
Создавая SAVEPOINT с пользовательским именем, любой активный TRANSACTION можно откатить с помощью синтаксиса ROLLBACK TO save_point_name до этого конкретного места в TRANSACTION. Мне нравится представлять это как единичный «момент времени» внутри TRANSACTION в целом.
Любые модификации данных, которые были выполнены в течение TRANSACTION после установленного SAVEPOINT, возвращаются в исходное состояние (до состояния SAVEPOINT).
Чтобы лучше понять, давайте рассмотрим простой пример и закрепим концепцию. У меня есть эта таблица и данные:
mysql> SELECT * FROM names; + — — — + — — — — — — + | id | first_name | + — — — + — — — — — — + | 4 | Harry | | 33 | Tom | | 8 | Shelly | | 10 | Ricky | + — — — + — — — — — — + 4 rows in set (0.00 sec)
Затем я инициализирую TRANSACTION:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
Создайте SAVEPOINT файл с именем «initial_save»:
mysql> SAVEPOINT initial_save; Query OK, 0 rows affected (0.00 sec)
Затем выполните UPDATE в строке таблицы:
mysql> UPDATE names
-> SET id = 414
-> WHERE first_name = ‘Harry’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
И посмотрите отраженные изменения:
mysql> SELECT *
-> FROM names
-> WHERE first_name = ‘Harry’;
+ — — — + — — — — — — +
| id | first_name |
+ — — — + — — — — — — +
| 414 | Harry |
+ — — — + — — — — — — +
1 row in set (0.00 sec)
Давайте отменим это изменение, выполнив ROLLBACK для созданного SAVEPOINT:
mysql> ROLLBACK TO initial_save; Query OK, 0 rows affected (0.04 sec)
И просмотрите отмененные (исходные) данные для этой строки:
mysql> SELECT *
-> FROM names
-> WHERE first_name = ‘Harry’;
+ — — — + — — — — — — +
| id | first_name |
+ — — — + — — — — — — +
| 4 | Harry |
+ — — — + — — — — — — +
1 row in set (0.00 sec)
Ниже приводится очень важный отрывок из документации SAVEPOINT, цитируемый непосредственно из этого источника:
«Оператор ROLLBACK TO SAVEPOINT откатывает транзакцию до указанной точки сохранения без завершения транзакции. Изменения, внесенные текущей транзакцией в строки после установки точки сохранения, отменяются при откате, но InnoDB не снимает блокировки строк, которые были сохранены в памяти после точки сохранения. (Для новой вставленной строки информация о блокировке передается идентификатором транзакции, хранящимся в строке; блокировка отдельно не сохраняется в памяти. В этом случае блокировка строки снимается при отмене.) «
Наконец, чтобы сделать эти изменения постоянными, мы должны выпустить COMMIT:
mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
Вы можете видеть, что с тех пор, как COMMIT был выпущен, мы больше не находимся внутри TRANSACTION и SAVEPOINT "initial_save" не существует:
mysql> ROLLBACK TO initial_save; ERROR 1305 (42000): SAVEPOINT initial_save does not exist
Теперь, когда мы закончили или вышли из TRANSACTION, данные остаются в исходном состоянии:
mysql> SELECT *
-> FROM names
-> WHERE first_name = ‘Harry’;
+ — — — + — — — — — — +
| id | first_name |
+ — — — + — — — — — — +
| 4 | Harry |
+ — — — + — — — — — — +
1 row in set (0.00 sec)
Так это полное доказательство? Не совсем так.
Хотя у вас может быть несколько отдельных SAVEPOINTS в одном TRANSACTION, существуют некоторые ограничения на то, какие типы команд «разрешены» для ROLLBACK. Поскольку их слишком много, чтобы перечислить их все, можно использовать несколько наиболее распространенных команд: ALTER TABLE, CREATE VIEW, DROP FUNCTION и т. Д.
Чтобы увидеть полный список, посетите раздел 13.3.3 Утверждения, вызывающие неявную фиксацию в официальной документации.
TRANSACTION 'и SAVEPOINT' - отличное дополнение для тех, кто имеет дело с командами DML и их данными, так что обязательно проверьте их!
Нравится то, что вы прочитали? Видите что-нибудь неправильное? Прокомментируйте, пожалуйста, ниже и спасибо за чтение !!!
Изучите официальное Онлайн-руководство по MySQL 8.0 для получения дополнительной информации.
Призыв к действию!
Спасибо, что нашли время прочитать этот пост. Я искренне надеюсь, что вы открыли для себя что-то интересное и поучительное. Пожалуйста, поделитесь своими выводами здесь с кем-нибудь, кого вы знаете, кто тоже получит от этого такую же ценность.
Посетите страницу Портфолио-проекты, чтобы увидеть сообщения в блоге / технические статьи, которые я написал для клиентов.
Я уже упоминал, как я люблю чашку кофе?!?!
Чтобы получать уведомления по электронной почте (Никогда не спамить) от этого блога (Проза Цифровой Совы) о последних публикациях в блогах по мере их публикации, пожалуйста, подпишитесь (по собственному желанию), нажав кнопку Нажмите, чтобы подписаться! На странице боковая панель на главной странице! (Не стесняйтесь в любое время просмотреть страницу Политики конфиденциальности Digital Owl's Prose по любым вопросам, которые могут у вас возникнуть: обновления по электронной почте, подписка, отказ, контактные формы и т. Д.)
Обязательно посетите страницу Best Of, где собраны мои лучшие сообщения в блоге.
Джош Отвелл хочет учиться и расти как разработчик SQL и блогер. Другие любимые занятия находят его, уткнувшись носом в хорошую книгу, статью или командную строку Linux. Среди них он разделяет любовь к настольным ролевым играм, чтению фэнтезийных романов и проведению времени с женой и двумя дочерьми.
Отказ от ответственности: примеры, представленные в этом посте, представляют собой гипотетические идеи о том, как достичь аналогичных результатов. Это не самое лучшее решение. Большая часть, если не все, из представленных примеров выполняется на рабочей станции-среде личного развития / обучения и не должна считаться производственной или готовой к использованию. Ваши конкретные цели и потребности могут отличаться. Используйте те методы, которые лучше всего подходят для ваших нужд и целей. Мнения мои собственные.
Первоначально опубликовано на joshuaotwell.com 16 января 2019 г.
