СУБД/лекция 4 семестр 2
Содержание |
Операторы управления транзакциями и блокировкой MySQL
START TRANSACTION COMMIT ROLLBACK
По умолчанию MySQL работает в режиме автоматического завершения транзакций (autocommit). Это означает, что как только выполняется оператор обновления данных, который модифицирует таблицу, MySQL тут же сохраняет изменения на диске.Если вы работаете с таблицами, безопасными в отношении транзакций (такими как InnoDB и BDB), то режим автоматического завершения транзакций можно отключить следующим оператором:
- SET AUTOCOMMIT=0;
START TRANSACTION; SELECT @A:=SUM(salary) FROM tablel WHERE TYPE=l; UPDATE table2 SET summary=@A WHERE TYPE=l; COMMIT;
Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):
UNLOCK TABLES также завершает транзакцию, если какие-либо таблицы были блокированы. До MySQL 4.0.13 CREATE TABLE завершал транзакцию, если была бы включена бинарная регистрация.
Транзакции не могут быть вложенными. Это следствие того, что неявный COMMIT выполняется для любой текущей транзакции, когда выполняется оператор start TRANSACTION или его синонимы.
Синтаксис 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, активна, вы не можете получить доступ ни к каким таблицам, которые не были блокированы этим оператором. Если ваш запрос обращается к таблице через псевдоним, вы должны блокировать таблицу, используя тот же псевдоним. Блокировка таблицы не будет работать, если не указан псевдоним:
LOCKTABLEtREAD; SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias1 was not locked with LOCK TABLES
И наоборот, если таблица блокирована по псевдониму, вы должны обращаться к ней, используя этот псевдоним:
ERROR 1100: Table Ч1 was not locked with LOCK TABLESLOCK TABLE t AS myalias READ; SELECT * ШЖ t;SELECT * FROM t AS myalias;
Блокировки по записи (WRITE) обычно имеют более высокий приоритет, чем блокировки по чтению (READ), чтобы гарантировать, что обновления данных пройдут как можно быстрее. Это означает, что если один поток получает блокировку по чтению, а затем другой поток запрашивает блокировку по записи, то последующие запросы на блокировку по чтению будут ожидать, пока не будет установлена и снята блокировка по записи. Вы можете использовать блокировки по записи с пониженным приоритетом (LOW_PRIORITY WRITE), чтобы позволить другим потокам устанавливать блокировки по чтению, пока текущий поток ожидает возможности поставить блокировку по записи. Вы должны устанавливать блокировки LOW_PRIORITY WRITE только тогда, когда уверены, что в процессе работы сервера будет время, когда ни один из потоков не будет удерживать блокировки по чтению. LOCK TABLES работает следующим образом: В определенном внутреннем порядке сортируются все таблицы, подлежащие блокировке. С точки зрения пользователя, этот порядок не определен. Если таблица блокируется и по чтению и по записи, устанавливается блокировка записи перед блокировкой чтения. Блокируется по одной таблице за раз до тех пор, пока поток не получит все блокировки.
Эта политика гарантирует, что при этом не случится взаимных блокировок (deadlocks). Существуют, однако, и другие обстоятельства в отношении этой политики, которые следует принимать во внимание. Если вы используете блокировку 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 для безопасного выполнения:
LOCKTABLEStransREAD,customerWRITE; SELECT SUM(VALUE) FROM trans WHERE customer_2.6.=идентификатор; UPDATE customer SET ЬоЬа1_уа1ъе=сумма_из_предццущ<2го_оператора WHERE сизЬотег_1<к=идентификатор; 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.