Поиск

Операторы управления транзакциями и блокировкой MySQL

Синтаксис START TRANSACTION, COMMITи ROLLBACK
По умолчанию MySQL работает в режиме автоматического завершения транзакций (autocommit). Это означает, что как только выполняется оператор обновления данных, который модифицирует таблицу, MySQL тут же сохраняет изменения на диске.
Если вы работаете с таблицами, безопасными в отношении транзакций (такими как InnoDB и BDB), то режим автоматического завершения транзакций можно отключить сле­дующим оператором:
SET AUTOCOMMIT=0;
После отключения режима автоматического завершения транзакций вы должны ис­пользовать оператор COMMIT, чтобы сохранять изменения на диске, либо ROLLBACK, чтобы отменять изменения, выполненные с момента начала транзакции.
Если вы хотите отключить режим автоматического завершения транзакций только для отдельной последовательности операторов, можете воспользоваться оператором START TRANSACTION:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM tablel WHERE type=l;
UPDATE table2 SET summary=@A WHERE type=l;
COMMIT;
После START TRANSACTION режим автоматического завершения транзакций остается выключенным до явного завершения транзакции с помощью COMMIT или отката посред­ством rollback. Затем режим автоматического завершения возвращается в свое преды­дущее состояние.
Вместо START TRANSACTION для обозначения начала транзакции могут использоваться BEGIN и BEGIN WORK. Оператор START TRANSACTION появился в версии MySQL 4.0.11. Это стандартный синтаксис SQL и рекомендуемый способ начинать транзакции. BEGIN и BEGIN WORK доступны, начиная с MySQL 3.23.17 и MySQL 3.23.19, соответственно.
Следует отметить, что если вы не используете таблицы, безопасные к транзакциям, все изменения сохраняются немедленно, независимо от режима автоматического завер­шения транзакций.
Если вы даете ROLLBACK после обновления таблицы, безопасной к транзакциям, в пределах транзакции, выдается предупреждение ER_WARNING_NOT_COMPLETE_ROLLBACK. Изменения в таблицах, безопасных к транзакциям, будут отменены, а в небезопасных -останутся.
Если вы используете START TRANSACTION или SET AUTOCOMMIT=0, то должны для ре­зервных копий использовать бинарный журнал MySQL, вместо старого журнала обнов­лений. Транзакции сохраняются в бинарном журнале одним куском, до COMMIT. Транзак­ции, отмененные оператором ROLLBACK, в журнал не заносятся. (Существует одно ис­ключение: модификации нетранзакционных таблиц не могут быть отменены. Если отмененная транзакция включала в себя модификацию данных нетранзакционных таб­лиц, такая транзакция целиком записывается в бинарный журнал с оператором ROLLBACK в конце, чтобы гарантировать, что модификации этих таблиц будут реплицированы. Это верно, начиная с версии MySQL 4.0.15.)
Вы можете изменить уровень изоляции транзакций оператором SET TRANSACTION ISOLATION LEVEL. См. разделСинтаксис SET TRANSACTION
Операторы,которыенельзяоткатить
Для некоторых операторов нельзя выполнить откат с помощью ROLLBACK. В их число входят операторы языка определения данных (Data Definition Language - DDL), которые создают и уничтожают базы данных, а также создают, удаляют и изменяют таблицы.
Вы должны проектировать свои транзакции таким образом, чтобы они не включали в себя эти операторы. Если ввести такой оператор в начале транзакции, которая не может быть откатана, и затем следующий оператор позже завершится аварийно, полный эф­фект транзакции не может быть отменен оператором ROLLBACK.
Операторы,вызывающиенеявныйCOMMIT
Следующие операторы неявно завершают транзакцию (как если бы перед их выпол­нением был выдан COMMIT):
UNLOCK TABLES также завершает транзакцию, если какие-либо таблицы были блоки­рованы. До MySQL 4.0.13 CREATE TABLE завершал транзакцию, если была бы включена бинарная регистрация.
Транзакции не могут быть вложенными. Это следствие того, что неявный COMMIT выполняется для любой текущей транзакции, когда выполняется оператор start TRANSACTION или его синонимы.
6.4.4.Синтаксис SAVEPOINTи ROLLBACK TO SAVEPOINT
SAVEPOINT идентификатор
ROLLBACK TO SAVEPOINT идентификатор
Начиная с версий MySQL 4.0.14 и 4.1.1, innoDB поддерживает SQL-операторы SAVEPOINT и ROLLBACK TO SAVEPOINT.
Оператор SAVEPOINT устанавливает именованную точку начала транзакции с именем идентификатор. Если текущая транзакция уже имеет точку сохранения с таким же име­нем, старая точка удаляется и устанавливается новая.
Оператор ROLLBACK TO SAVEPOINT откатывает транзакцию к именованной точке со­хранения. Модификации строк, которые выполнялись текущей транзакцией после этой точки, отменяются откатом, однако InnoDB не снимает блокировок строк, которые были установлены в памяти после точки сохранения. (Отметим, что для вновь вставленных строк информация о блокировке опирается на идентификатор транзакции, сохраненный в строке, блокировка не хранится в памяти отдельно. В этом случае блокировка строки снимается при отмене.) Точки сохранения, установленные в более поздние моменты, чем именованная точка, удаляются.

Если оператор возвращает следующую ошибку, это означает, что названная точка сохранения не существует:
ERROR 1181: Got error 153 during ROLLBACK
Все точки сохранения транзакций удаляются, если выполняется оператор COMMIT или ROLLBACK без указания имени точки сохранения.

Синтаксис LOCK TABLESи UNLOCK TABLES
LOCK TABLES
имя_таблицы[AS псевдоним] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, имя_таблицы[AS псевдоним] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES
LOCK TABLES блокирует таблицы для текущего потока сервера. UNLOCK TABLES снима­ет любые блокировки, удерживаемые текущим потоком. Все таблицы, заблокированные в текущем потоке, неявно разблокируются, когда поток выполняет другой оператор LOCK TABLES либо когда закрывается соединение с сервером.
На заметку!
LOCK TABLES не является оператором, безопасным в отношении транзакций, и неявно заверша­ет транзакцию перед попыткой блокировать таблицы.
Начиная с версии MySQL 4.0.2, для того, чтобы выполнять LOCK TABLES, необходимо иметь привилегию LOCK TABLES и привилегию SELECT для соответствующих таблиц. В MySQL 3.23 необходимо иметь привилегии SELECT, INSERT, DELETE и UPDATE для этих таблиц.
Основная причина применения LOCK TABLES - эмуляция транзакций или повышение скорости обновления таблиц. Ниже это объясняется более подробно.
Если поток устанавливает блокировку по чтению (READ) на таблице, то этот поток (и все остальные) может только читать данные из таблицы. Если поток устанавливает бло­кировку записи (WRITE) таблицы, то лишь этот поток может читать и писать в таблицу. Доступ остальных нитей к таблице блокируется.
Разница между READ LOCAL и READ состоит в том, что READ LOCAL позволяет некон­фликтующим операторам INSERT (параллельным вставкам) выполняться, пока блокиров­ка удерживается. Однако это не может быть выполнено, если вы пытаетесь манипулиро­вать файлами базы данных извне MySQL в то время, пока удерживается блокировка.
В случае применения LOCK TABLES необходимо блокировать все таблицы, которые используются в запросах. Если одна и та же таблица используется несколько раз в за­просе (через псевдонимы), вы должны получить блокировку на каждый псевдоним. Пока блокировка, полученная от LOCK TABLES, активна, вы не можете получить доступ ни к каким таблицам, которые не были блокированы этим оператором.
Если ваш запрос обращается к таблице через псевдоним, вы должны блокировать таблицу, используя тот же псевдоним. Блокировка таблицы не будет работать, если не указан псевдоним:
mysql> LOCKTABLEtREAD;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias1 was not locked with LOCK TABLES

И наоборот, если таблица блокирована по псевдониму, вы должны обращаться к ней, используя этот псевдоним:
mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * ШЖ t;
ERROR 1100: Table Ч1 was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;
Блокировки по записи (WRITE) обычно имеют более высокий приоритет, чем блоки­ровки по чтению (READ), чтобы гарантировать, что обновления данных пройдут как мож­но быстрее. Это означает, что если один поток получает блокировку по чтению, а затем другой поток запрашивает блокировку по записи, то последующие запросы на блоки­ровку по чтению будут ожидать, пока не будет установлена и снята блокировка по запи­си. Вы можете использовать блокировки по записи с пониженным приоритетом (LOW_PRIORITY WRITE), чтобы позволить другим потокам устанавливать блокировки по чтению, пока текущий поток ожидает возможности поставить блокировку по записи. Вы должны устанавливать блокировки LOW_PRIORITY WRITE только тогда, когда уверены, что в процессе работы сервера будет время, когда ни один из потоков не будет удержи­вать блокировки по чтению.
LOCK TABLES работает следующим образом:

  1. В определенном внутреннем порядке сортируются все таблицы, подлежащие блокировке. С точки зрения пользователя, этот порядок не определен.
  2. Если таблица блокируется и по чтению и по записи, устанавливается блокировка записи перед блокировкой чтения.
  3. Блокируется по одной таблице за раз до тех пор, пока поток не получит все блокировки.

Эта политика гарантирует, что при этом не случится взаимных блокировок (dead­locks). Существуют, однако, и другие обстоятельства в отношении этой политики, кото­рые следует принимать во внимание.
Если вы используете блокировку LOW_PRIORITY WRITE для таблицы, это означает только, что MySQL будет ожидать момента, когда не будет ни одного потока, который желает установить блокировку чтения. Когда потоку удается установить блокировку записи одной таблицы, и он ожидает возможности заблокировать следующую таблицу в списке, все остальные потоки будут приостановлены до тех пор, пока блокировка записи не будет снята. Если это представляет серьезную проблему для ваших приложений, вы должны рассмотреть возможность преобразования некоторых ваших таблиц в нетран-закционную форму.
Можно безопасно использовать KILL для прерывания потока, который ожидает бло­кировки таблицы. См. раздел 6.5.4.3.
Заметьте, что вы не должны блокировать никаких таблиц из тех, в которых выпол­няете INSERT DELAYED, потому что в этом случае INSERT выполняется отдельным пото­ком сервера.
Обычно вам не нужно блокировать таблицы, поскольку все отдельные операторы INSERT атомарны - то есть никакой другой поток не может вмешаться в исполняемый в данный момент SQL-оператор. Однако существуют случаи, когда блокировать таблицы все же необходимо:

  • Если вы собираетесь выполнять множество операций над набором таблиц MyISAM, то гораздо быстрее получится, если их предварительно заблокировать. Блокировка таблиц MyISAM ускоряет вставку, обновление или удаление в них. Отрицатель­ная сторона этого состоит в том, что ни один поток не может обновлять заблоки­рованную по чтению таблицу (включая тот, что установил блокировку), и ни один поток не может получить никакого доступа к таблице, заблокированной по запи­си, кроме потока, установившего блокировку.
    Причина того, что некоторые операции MylSAM работают быстрее на блокирован­ных таблицах, связана с тем, что MySQL не сбрасывает на диск индексный кэш для этих таблиц до тех пор, пока не будет вызван UNLOCK TABLES. Обычно индекс­ные кэши сбрасываются после каждого SQL-оператора.
  • Если вы используете механизм хранения MySQL, которые не поддерживает тран­закций, вы должны выдавать LOCK TABLES, если хотите гарантировать, что между SELECT и UPDATE не будут выданы другие операторы. Приведенный ниже пример требует LOCK TABLES для безопасного выполнения:
    mysql> LOCKTABLEStransREAD,customerWRITE;
    mysql> SELECT SUM(value) FROM trans WHERE customer_2.6.=идентификатор;
    mysql> UPDATE customer
    -> SET ЬоЬа1_уа1ъе=сумма_из_предццущ<2го_оператора
    -> WHERE сизЬотег_1<к=идентификатор; mysql> UNLOCK TABLES; ~
    Без LOCK TABLES существует возможность того, что другой поток может вставить новую строку в таблицу trans между выполнением ваших операторов SELECT и UPDATE.
    Вы можете избежать применения LOCK TABLES во многих случаях, применяя относи­тельные обновления (UPDATE customer SET value=value+new_value), либо функцию LAST_INSERT_ID(). См. раздел Транзакции и атомарные операции
    Избежать блокировки таблиц можно также в некоторых случаях, используя функции пользовательского уровня GET_LOCK () и RELEASE_LOCK (). Эти блокировки сохраняются в хэш-таблице на сервере и для скорости реализуются вызовами pthread_mutex_lock() и pthread_mutex_unlock(). См. раздел Различные функции
    Вы можете заблокировать по чтению все таблицы во всех базах данных оператором flush tables with read LOCK. См. разделСинтаксис LOCK TABLES и UNLOCK TABLES Это очень удобный способ получить резервные копии, если вы работаете с файловой системой вроде Veritas, которая может делать снимки во времени.
    %Назаметку! Если вы используете ALTER TABLE на блокированной таблице, она может разблокироваться. См. раздел Проблемы с ALTER TABLE
    Синтаксис SET TRANSACTION
    SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
  • Этот оператор устанавливает уровень изоляции следующей транзакции, глобально либо только для текущего сеанса.
Поведение SET TRANSACTION по умолчанию заключается в том, что он устанавливает уровень изоляции для следующей (еще не стартовавшей) транзакции. Если вы транзакций по умолчанию для всех новых соединений, которые будут установлены с этого момента. Существующие соединения не затрагиваются. Для выполнения этого оператора нужно иметь привилегию SUPER. Применение ключевого слова SESSION уста­навливает уровень изоляции по умолчанию всех будущих транзакций только для теку­щего сеанса.

Описание уровней изоляции транзакций InnoDB приведено в книге MySQL. Руково­дство администратора (М. : Издательский дом "Вильяме", 2005, ISBN 5-8459-0805-1). InnoDB поддерживает эти уровни, начиная с версии MySQL 4.O.5. Уровень изоляции по умолчанию - REPEATABLE READ.

Вы можете также установить начальный глобальный уровень изоляции для сервера mysqld, запустив его с опцией —transaction-isolation