MySQL Transaction

Введение в сделку

Транзакция - это логическая единица работы, которая содержит один или несколько операторов SQL. Транзакции являются атомарными единицами работы, которые могут быть зафиксированы или отменены. Когда транзакция вносит множественные изменения в базу данных, либо все изменения завершаются успешно, когда транзакция фиксируется, либо все изменения отменяются при откате транзакции.

Транзакция начинается с первого исполняемого оператора SQL. Транзакция заканчивается, когда она фиксируется или откатывается, явно с помощью оператора COMMIT или ROLLBACK или неявно, когда DDL (язык определения данных (DDL)) используется для управления таблицей и структурой индекса и операторами CREATE, ALTER, RENAME, DROP и TRUNCATE назвать несколько элементов определения данных) выдается заявление.

Понять концепцию транзакции

Чтобы понять концепцию транзакции, рассмотрим банковскую базу данных. Предположим, что клиент банка переводит деньги со своего сберегательного счета (SB a / c) на свой текущий счет (CA a / c), выписка будет разделена на четыре блока:

• Дебет СБ а / с.
• Кредит CA / C.
• Запись в журнале транзакций
• Завершение транзакции

Оператор SQL для дебетования SB a / c выглядит следующим образом:

Оператор SQL для кредитования OD A / C выглядит следующим образом:

Оператор SQL для записи в журнале транзакций выглядит следующим образом:

Оператор SQL для завершения транзакции выглядит следующим образом:

MySQL и модель ACID

ACID (атомарность, согласованность, изоляция, долговечность) - это набор свойств, гарантирующих надежную обработку транзакций базы данных. В MySQL механизм хранения InnoDB поддерживает ACID-совместимые функции. В следующих разделах обсуждается, как функции MySQL, в частности механизм хранения InnoDB, взаимодействуют с категориями модели ACID:

Атомарность. Атомарность аспекта модели ACID в основном включает транзакции InnoDB. Связанные функции MySQL включают в себя:

• Автокоммит настройки.
• COMMIT заявление.
• ROLLBACK заявление.
• Оперативные данные из таблиц INFORMATION_SCHEMA.

Согласованность: аспект согласованности модели ACID в основном включает внутреннюю обработку InnoDB для защиты данных от сбоев. Связанные функции MySQL включают в себя:

• InnoDB буфер двойной записи.
• InnoDB восстановление после сбоя.

Изоляция: аспект изоляции модели ACID в основном включает транзакции InnoDB, в частности, уровень изоляции, который применяется к каждой транзакции. Связанные функции MySQL включают в себя:

• Автокоммит настройки.
• УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ.
• Низкоуровневые детали блокировки InnoDB. Во время настройки производительности эти подробности отображаются в таблицах INFORMATION_SCHEMA.

Долговечность. Аспект долговечности модели ACID включает функции программного обеспечения MySQL, взаимодействующие с вашей конкретной аппаратной конфигурацией. Из-за множества возможностей, зависящих от возможностей вашего ЦП, сети и запоминающих устройств, этот аспект является наиболее сложным для предоставления конкретных рекомендаций. Связанные функции MySQL включают в себя:

• InnoDB буфер двойной записи включается и выключается с помощью параметра конфигурации innodb_doublewrite.
• Опция конфигурации innodb_flush_log_at_trx_commit.
• Опция конфигурации sync_binlog.
• Опция конфигурации innodb_file_per_table.
• Буфер записи в устройстве хранения, таком как дисковод, SSD или массив RAID.
• Кэш-память с резервным питанием в устройстве хранения.
• Операционная система, используемая для запуска MySQL, в частности, ее поддержка системного вызова fsync ().
• Источник бесперебойного питания (ИБП), защищающий электроэнергию для всех компьютерных серверов и устройств хранения данных, на которых работают серверы MySQL и хранятся данные MySQL.
• Ваша стратегия резервного копирования, такая как частота и типы резервных копий, а также сроки хранения резервных копий.
• Для распределенных или размещенных приложений данных: конкретные характеристики центров обработки данных, в которых находится оборудование для серверов MySQL, и сетевые соединения между центрами обработки данных.

MySQL Transaction

MySQL (здесь мы поддерживаем версию 5.6) поддерживает локальные транзакции (в рамках данного сеанса клиента) с помощью таких операторов, как SET autocommit, START TRANSACTION, COMMIT и ROLLBACK. Вот синтаксис START TRANSACTION, COMMIT и ROLLBACK:

 НАЧАТЬ СДЕЛКУ
   транзакция_характеристика [, транзакция_характеристика] ...] 
transaction_characteristic:     
    С ПОСЛЕДОВАТЕЛЬНЫМ ОСНОВАНИЕМ    
    | ЧИТАЙ ПИШИ    
    | ЧИТАЙТЕ ТОЛЬКО    
НАЧАЛО [РАБОТАЕТ]  
COMMIT [РАБОТА] [И [НЕТ] ЦЕПЬ] [[НЕТ] РЕЛИЗ]  
ROLLBACK [РАБОТА] [И [НЕТ] ЦЕПЬ] [[НЕТ] РЕЛИЗ]  
SET autocommit = {0 | 1}

Эти заявления обеспечивают контроль над использованием транзакций:

• Оператор START TRANSACTION или BEGIN начинает новую транзакцию.
• COMMIT фиксирует текущую транзакцию, делая ее изменения постоянными.
• ROLLBACK откатывает текущую транзакцию, отменяя ее изменения.
• Инструкция SETautocommit отключает или включает режим автоматической фиксации по умолчанию для текущего сеанса.

По умолчанию MySQL работает с включенным режимом автоматической фиксации. Это означает, что как только вы выполняете инструкцию, которая обновляет (модифицирует) таблицу, MySQL сохраняет обновление на диске, чтобы сделать его постоянным. Изменение не может быть отменено.

В настоящее время (по умолчанию) MySQL работает с включенным режимом автоматической фиксации.

 mysql> select * from student_mast;
+ ------------ + ------------------ + ---------- +
| STUDENT_ID | ИМЯ | ST_CLASS |
+ ------------ + ------------------ + ---------- +
| 2 | Нина Кочхар | 9 |
| 3 | Лекс Де Хаан | 9 |
| 4 | Александр Хунольд | 11 |
+ ------------ + ------------------ + ---------- +
3 ряда в наборе (0,09 сек)

Давайте выполним команду обновления:

 mysql> mysql> UPDATE STUDENT_MAST SET ST_CLASS = 8 WHERE STUDENT_ID = 2;
Запрос в порядке, затрагивается 1 строка (0,08 сек)
Строк совпало: 1 Изменено: 1 Предупреждений: 0

 mysql> mysql> select * from student_mast;
+ ------------ + ------------------ + ---------- +
| STUDENT_ID | ИМЯ | ST_CLASS |
+ ------------ + ------------------ + ---------- +
| 2 | Нина Кочхар | 8 |
| 3 | Лекс Де Хаан | 9 |
| 4 | Александр Хунольд | 11 |
+ ------------ + ------------------ + ---------- +
3 ряда в наборе (0,00 сек)

Теперь выполните команду ROLLBACK, чтобы вернуться на предыдущем этапе:

 mysql> mysql> ROLLBACK;
Запрос в порядке, затронуто 0 строк (0,03 сек)

 mysql> mysql> select * from student_mast;
+ ------------ + ------------------ + ---------- +
| STUDENT_ID | ИМЯ | ST_CLASS |
+ ------------ + ------------------ + ---------- +
| 2 | Нина Кочхар | 8 |
| 3 | Лекс Де Хаан | 9 |
| 4 | Александр Хунольд | 11 |
+ ------------ + ------------------ + ---------- +
3 ряда в наборе (0,00 сек)
 

Откат не выполняется, поскольку MySQL работает с включенным режимом автоматической фиксации.

Чтобы отключить режим автоматической фиксации, используйте оператор START TRANSACTION. Смотрите следующий пример:

 mysql> mysql> START TRANSACTION;
Запрос в порядке, затронуто 0 строк (0,00 с)
mysql> ОБНОВЛЕНИЕ STUDENT_MAST SET ST_CLASS = 10 ГДЕ STUDENT_ID = 2;
Запрос в порядке, затрагивается 1 строка (0,00 с)
Строк совпало: 1 Изменено: 1 Предупреждений: 0
mysql> select * from student_mast;
+ ------------ + ------------------ + ---------- +
| STUDENT_ID | ИМЯ | ST_CLASS |
+ ------------ + ------------------ + ---------- +
| 2 | Нина Кочхар | 10 |
| 3 | Лекс Де Хаан | 9 |
| 4 | Александр Хунольд | 11 |
+ ------------ + ------------------ + ---------- +
3 ряда в наборе (0,00 сек)

 mysql> ROLLBACK;
Запрос в порядке, затронуто 0 строк (0,07 сек)
mysql> select * from student_mast;
+ ------------ + ------------------ + ---------- +
| STUDENT_ID | ИМЯ | ST_CLASS |
+ ------------ + ------------------ + ---------- +
| 2 | Нина Кочхар | 8 |
| 3 | Лекс Де Хаан | 9 |
| 4 | Александр Хунольд | 11 |
+ ------------ + ------------------ + ---------- +
3 ряда в наборе (0,00 сек)

Операторы MySQL, которые нельзя откатить, и операторы, которые вызывают неявную фиксацию

В MySQL некоторые операторы не могут быть отменены. Операторы DDL, такие как базы данных CREATE или DROP, таблицы CREATE, ALTER или DROP или хранимые подпрограммы. Вы должны разработать транзакцию без этих заявлений.

Операторы, перечисленные в этом разделе (и любые синонимы для них) неявно завершают любую транзакцию, активную в текущем сеансе, как если бы вы выполнили COMMIT перед выполнением оператора.

• Операторы языка определения данных (DDL), которые определяют или изменяют объекты базы данных. ALTER DATABASE ... ОБНОВЛЕНИЕ ИМЕНИ КАТАЛОГА ДАННЫХ ОБНОВЛЕНИЯ, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, СОЗДАНИЕ БАЗЫ ДАННЫХ, СОЗДАНИЕ СОБЫТИЙ, СОЗДАНИЕ ИНДЕКСА, СОЗДАНИЕ ПРОЦЕДУРЫ, СОЗДАНИЕ СЕРВЕРА, СОЗДАНИЕ ТАБЛИЦЫ СОЗДАНИЯ, СОЗДАНИЕ ТРИГГЕРА БАЗА ДАННЫХ, СОБЫТИЕ КАПЛИ, ИНДЕКС КАПЛИ, ПРОЦЕДУРА КАПЛИ, СЕРВЕР КАПЛИ, ТАБЛИЦА КАПЛИ, ТРИГГЕР КАПЛИ, ПРОСМОТР КАПЛИ, ТАБЛИЦА ПЕРЕИМЕНОВАНИЯ, ТАБЛИЦА ПЕРЕКЛЮЧЕНИЯ.
• ALTER FUNCTION, CREATE FUNCTION и DROP FUNCTION также вызывают неявную фиксацию при использовании с сохраненными функциями, но не с UDF. (ALTER FUNCTION может использоваться только с сохраненными функциями.)
• ALTER TABLE, CREATE TABLE и DROP TABLE не фиксируют транзакцию, если используется ключевое слово TEMPORARY.
• Операторы, которые неявно используют или изменяют таблицы в базе данных MySQL. СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ, УДАЛИТЬ ПОЛЬЗОВАТЕЛЯ, GRANT, ПЕРЕИМЕНОВАТЬ ПОЛЬЗОВАТЕЛЯ, ОТМЕНИТЬ, УСТАНОВИТЬ ПАРОЛЬ.
• Оператор-контроль и операторы блокировки. НАЧАТЬ, ЗАБЛОКИРОВАТЬ ТАБЛИЦЫ, УСТАНОВИТЬ autocommit = 1 (если значение еще не равно 1), НАЧАТЬ СДЕЛКУ, ОТКРЫТЬ ТАБЛИЦЫ.
• Заявления о загрузке данных. НАГРУЗКА ДАННЫХ В ИНФАЛЕ. LOAD DATA INFILE вызывает неявную фиксацию только для таблиц, использующих механизм хранения NDB.
• Административные заявления. ТАБЛИЦА АНАЛИЗА, ИНДЕКС КЭША, ТАБЛИЦА ПРОВЕРКИ, ИНДЕКС ЗАГРУЗКИ В КЭШ, ТАБЛИЦА ОПТИМИЗАЦИИ, ТАБЛИЦА РЕМОНТА.
• Репликация контрольных операторов. Начиная с MySQL 5.6.7: НАЧАТЬ РАБ, ОСТАНОВИТЬ РАБ, СБРОСИТЬ РАБ, ИЗМЕНИТЬ МАСТЕРА НА.
  

SAVEPOINT, ROLLBACK TO SAVEPOINT и RELEASE SAVEPOINT

InnoDB поддерживает операторы SQL SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT и необязательное ключевое слово WORK для ROLLBACK.

Оператор SAVEPOINT устанавливает именованную точку сохранения транзакции с именем идентификатора. Если текущая транзакция имеет точку сохранения с тем же именем, старая точка сохранения удаляется, а новая устанавливается.

Оператор ROLLBACK TO SAVEPOINT откатывает транзакцию до указанной точки сохранения без прерывания транзакции. Изменения, которые выполняются в текущей транзакции для строк после установки точки сохранения, отменяются при откате, но InnoDB не освобождает блокировки строк, которые были сохранены в памяти после точки сохранения.

Вот синтаксис:

 SAVEPOINT идентификатор  
ROLLBACK [РАБОТА] ДО [SAVEPOINT] идентификатор  
RELEASE SAVEPOINT идентификатор

БЛОКИРОВАТЬ и РАЗБЛОКИРОВАТЬ Столы

MySQL позволяет клиентским сеансам явно получать блокировки таблиц с целью взаимодействия с другими сеансами для доступа к таблицам или для предотвращения изменения таблиц другими сеансами в периоды, когда сеанс требует монопольного доступа к ним. Сессия может получить или снять блокировки только для себя. Один сеанс не может получить блокировки для другого сеанса или снять блокировки, удерживаемые другим сеансом.

LOCK TABLES явно получает блокировки таблицы для текущего сеанса клиента. Блокировки таблиц могут быть получены для базовых таблиц или представлений. У вас должны быть привилегия LOCK TABLES и привилегия SELECT для каждого объекта, который нужно заблокировать.

UNLOCK TABLES явно освобождает любые блокировки таблиц, удерживаемые текущим сеансом. LOCK TABLES неявно освобождает любые блокировки таблиц, удерживаемые текущим сеансом, до получения новых блокировок.

Вот синтаксис:

 ЗАМКИ      
  tbl_name [[AS] псевдоним ] lock_type      
  [, tbl_name [[AS] псевдоним ] lock_type ] ... 
lock_type :      
  ЧИТАТЬ [МЕСТНОЕ]    
 | [LOW_PRIORITY] НАПИСАТЬ    
РАЗБЛОКИРОВАТЬ СТОЛЫ 

Синтаксис SET TRANSACTION

 SET [GLOBAL | СЕССИЯ] СДЕЛКА
    транзакция_характеристика [, транзакция_характеристика] ...
	
transaction_characteristic:      
    УРОВЕНЬ ИЗОЛЯЦИИ    
  | ЧИТАЙ ПИШИ    
  | ЧИТАЙТЕ ТОЛЬКО    
уровень:       
  ПОВТОРЯЕМЫЙ ЧИТАТЬ     
  | ЧИТАТЬ ОБЯЗАТЕЛЬНО     
  | ЧИТАТЬ БЕЗ КОММИТЕТЫ     
  | SERIALIZABLE

• С ключевым словом GLOBAL, заявление применяется глобально для всех последующих сеансов. Существующие сеансы не затрагиваются.
• С ключевым словом SESSION оператор применяется ко всем последующим транзакциям, выполненным в текущем сеансе.
• Без каких-либо ключевых слов SESSION или GLOBAL оператор применяется к следующей (не запущенной) транзакции, выполненной в текущем сеансе.

Ссылка: руководство по MySQL 5.6

Предыдущая: Триггеры MySQL
Далее: MySQL Views