СУБД/лекция 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, активна, вы не можете получить доступ ни к каким таблицам, которые не были блокированы этим оператором.
- Если ваш запрос обращается к таблице через псевдоним, вы должны блокировать таблицу, используя тот же псевдоним. Блокировка таблицы не будет работать, если не указан псевдоним:
LOCK TABLE READ; SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias1 was not locked with LOCK TABLES
- И наоборот, если таблица блокирована по псевдониму, вы должны обращаться к ней, используя этот псевдоним:
LOCK TABLE t AS myalias READ; SELECT * t;
ERROR 1100: Table t was not locked with LOCK TABLES
SELECT * FROM t AS myalias;
Блокировки по записи (WRITE) обычно имеют более высокий приоритет, чем блокировки по чтению (READ), чтобы гарантировать, что обновления данных пройдут как можно быстрее. Это означает, что если один поток получает блокировку по чтению, а затем другой поток запрашивает блокировку по записи, то последующие запросы на блокировку по чтению будут ожидать, пока не будет установлена и снята блокировка по записи.
Вы можете использовать блокировки по записи с пониженным приоритетом (LOW_PRIORITY WRITE), чтобы позволить другим потокам устанавливать блокировки по чтению, пока текущий поток ожидает возможности поставить блокировку по записи. Вы должны устанавливать блокировки LOW_PRIORITY WRITE только тогда, когда уверены, что в процессе работы сервера будет время, когда ни один из потоков не будет удерживать блокировки по чтению. LOCK TABLES работает следующим образом:
- В определенном внутреннем порядке сортируются все таблицы, подлежащие блокировке.
- С точки зрения пользователя, этот порядок не определен.
- Если таблица блокируется и по чтению и по записи, устанавливается блокировка записи перед блокировкой чтения.
- Блокируется по одной таблице за раз до тех пор, пока поток не получит все блокировки.
Эта политика гарантирует, что при этом не случится взаимных блокировок (deadlocks). Существуют, однако, и другие обстоятельства в отношении этой политики, которые следует принимать во внимание.
- Если вы используете блокировку LOW_PRIORITY WRITE для таблицы, это означает только, что MySQL будет ожидать момента, когда не будет ни одного потока, который желает установить блокировку чтения.
- Когда потоку удается установить блокировку записи одной таблицы, и он ожидает возможности заблокировать следующую таблицу в списке, все остальные потоки будут приостановлены до тех пор, пока блокировка записи не будет снята.
- Если это представляет серьезную проблему для ваших приложений, вы должны рассмотреть возможность преобразования некоторых ваших таблиц в не транзакционную форму.
- Можно безопасно использовать KILL для прерывания потока, который ожидает блокировки таблицы.
- Заметьте, что вы не должны блокировать никаких таблиц из тех, в которых выполняете INSERT DELAYED, потому что в этом случае INSERT выполняется отдельным потоком сервера.
Обычно вам не нужно блокировать таблицы, поскольку все отдельные операторы INSERT атомарны - то есть никакой другой поток не может вмешаться в исполняемый в данный момент SQL-оператор. Однако существуют случаи, когда блокировать таблицы все же необходимо:
- Если вы собираетесь выполнять множество операций над набором таблиц MyISAM, то гораздо быстрее получится, если их предварительно заблокировать.
-
Блокировка таблиц MyISAM ускоряет вставку, обновление или удаление в них.
- Отрицательная сторона этого состоит в том, что ни один поток не может обновлять заблокированную по чтению таблицу (включая тот, что установил блокировку), и ни один поток не может получить никакого доступа к таблице, заблокированной по записи, кроме потока, установившего блокировку.
- Причина того, что некоторые операции MylSAM работают быстрее на блокированных таблицах, связана с тем, что MySQL не сбрасывает на диск индексный кэш для этих таблиц до тех пор, пока не будет вызван UNLOCK TABLES. ( Обычно индексные кэши сбрасываются после каждого SQL-оператора.)
-
LOCKTABLEStransREAD,customerWRITE; SELECT SUM(VALUE) FROM trans WHERE customer_2.6.=идентификатор; UPDATE customer SET ЬоЬа1_уа1ъе=сумма_из_предццущ<2го_оператора WHERE сизЬотег_1<к=идентификатор; UNLOCK 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.
Это очень удобный способ получить резервные копии, если вы работаете с файловой системой вроде Veritas, которая может делать снимки во времени.
Если вы используете 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
Кто-бы ни купил MySQL, она еще долго будет будоражить ресурсы Наших с Вами серверов — и это хорошо.
Пример
- Есть таблица
CREATE TABLE test (id INT, VALUE VARCHAR(255)) ENGINE=InnoDB;
Что по Вашему покажет этот запрос?
START TRANSACTION; INSERT INTO test(id, VALUE) VALUES (1, 'test'), (2, 'test 2'); SELECT * FROM test; COMMIT; SELECT * FROM test;
А что покажет простейший SELECT во время выполнения текущей транзакции? Не ясно. Вот и придумали такие правила.
- Первый READ UNCOMMITTED
Рассмотрим транзакцию выше. После INSERT данные сразу-же станут доступны для чтения. Тоесть еще до вызова COMMIT вне транзакции можно получить только что добавленные данные. В английской литературе это называется dirty read («грязное чтение»). Этот уровень редко используется на практике, да вообще редко кто меняет эти самые уровни.
- Второй READ COMMTITED
В данном случае прочитать данные возможно только после вызова COMMIT. При чем внутри транзакции данные тоже будут еще не доступны. Если рассмотреть транзакцию выше, то первый SELECT ничего не вернет, т.к. таблица у нас еще пустая и транзакция не подтверждена.
- Третий REPEATABLE READ
Этот уровень используется по умолчанию в MySQL. Отличается от второго тем, что вновь добавленные данные уже будут доступны внутри транзакции, но не будут доступны до подтверждения извне. Здесь может возникнуть теоретическая проблема «фантомного чтения». Когда внутри одной транзакции происходит чтение данных, другая транзакция в этот момент вставляет новые данные, а первая транзакция снова читает те-же самые данные.
- И последний SERIALIZABLE
На данном уровне MySQL блокирует каждую строку над которой происходит какое либо действие, это исключает появление проблемы «фантомов». На самом деле смысла использовать этот уровень нет, т.к. InnoDB и менее популярный Falcon решают эту проблему.
- Увидеть текущий уровень изоляции
SHOW VARIABLES LIKE '%tx_isolation%';
- Установить
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
P.S. Это всего лишь попытка вольного перевода самой обычной документации.
CREATE TABLE one(id INT, VALUE VARCHAR(12)) ENGINE = MyISAM; CREATE TABLE two(id INT, VALUE VARCHAR(12)) ENGINE = InnoDB; START TRANSACTION; INSERT INTO one (id, VALUE) VALUES (1, 'test'); INSERT INTO two (id, VALUE) VALUES (1, 'test'); ROLLBACK; SELECT * FROM one; SELECT * FROM two;
Что будет в первой таблице, а что во второй?
