Какие команды автоматически завершают транзакцию в mysql

Вводная

Используя данные базы данных, подготовленной в предыдущей лабораторной работе, подготовить и реализовать серию запросов, связанных с выборкой информации и модификацией данных таблиц.

Теоретические сведения

Изменения БД часто требуют выполнения нескольких запросов, например при покупке в электронном магазине требуется добавить запись в таблицу заказов и уменьшить число товарных позиций на складе. В промышленных БД одно событие может затрагивать большее число таблиц и требовать многочисленных запросов.

Если на этапе выполнения одного из запросов происходит сбой, это может нарушить целостность БД (товар может быть продан, а число товарных позиций на складе не обновлено). Чтобы сохранить целостность БД, все изменения должны выполняться как единое целое. Либо все изменения успешно выполняются, либо, в случае сбоя, БД принимает состояние, которое было до начала изменений. Это обеспечивается средствами обработки транзакций.

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

Таблицы ISAM, MyISAM и HEAP не поддерживают транзакции. В настоящий момент их поддержка осуществляется только в таблицах BDB и InnoDB.

Транзакции позволяют объединять операторы в группу и гарантировать, что все операторы группы будут выполнены успешно. Если часть транзакции выполняется со сбоем, результаты выполнения всех операторов транзакции до места сбоя отменяются, приводя БД к виду, в котором она была до выполнения транзакции.

Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):

• ALTER TABLE
• DROP DATABASE
• LOAD MASTER DATA
• SET AUTO COMMIT=1
• BEGIN
• CREATE INDEX
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• DROP INDEX
• LOCK TABLES
• START TRANSACTION

UNLOCK TABLES также завершает транзакцию, если какие-либо таблицы были блокрованы. До MySQL 4.0.13 CREATE TABLE завершал транзакцию, если была бы включена бинарная регистрация. Транзакции не могут быть вложенными. Это следствие того, что неявный COMMIT выполняется для любой текущей транзакции, когда выполняется оператор start TRANSACTION или его синонимы.

По умолчанию MySQL работает в режиме автоматического завершения транзакций, т. е. как только выполняется оператор обновления данных, который модифицирует таблицу, изменения тут же сохраняются на диске. Чтобы объединить операторы в транзакцию, следует отключить этот режим: set AUTOCOMMIT=0;

После отключения режима для завершения транзакции необходимо ввести оператор COMMIT, для отката – ROLLBACK.

Отключить режим автоматического завершения транзакций для отдельной последовательности операторов можно оператором START TRANSACTION.

Для таблиц InnoDB есть операторы savepoint и rollback to savepoint, которые позволяют работать с именованными точками начала транзакции. Пример:

mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO catalogs UALUES(NULL,'Периферия'); Query OK, 1 row affected (0.00 sec) mysql> SAVEPOINT point1; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO catalogs VALUES(NULL,'Разное'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM CATALOGS; cat_ID cat_name 1 Программирование 2 Интернет 3 азы данных 4 Сети 5 Мультимедиа 12 Периферия 13 Разное 7 rows in set (0.00 sec) mysql> ROLLBACK TO SAVEPOINT point1; Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM catalogs; cat_ID cat_name 1 Программирование 2 Интернет 3 Базы данных 4 Сети 5 Мультимедиа 12 Периферия 6 rows in set (0.00 sec)

В данном примере оператор savepoint устанавливает именованную точку начала транзакции с именем point1. Оператор rollback to save point point1откатывает транзакцию к состоянию, в котором находилась БД на момент установки именованной точки. Все точки сохранения транзакций удаляются, если выполняются операторы commit или rollback без указания имени точки сохранения.

Задание

• Используя базу, полученную в лабораторной 2, создать транзакцию, произвести ее откат и фиксацию. Показать, что данные существовали до отката, удалились после отката, снова были добавлены, и затем были успешно зафиксированы.

Пример выполнения работы

Для выполнения задания объединим несколько операций по добавлению в таблицу catalogs новых каталогов, а затем произведем откат транзакции, т. е. отмену произведенных действий. Отключаем режим автоматического завершения, добавляем новые записи и проверяем, добавились записи или нет.

mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO catalogs VALUES(NULL,'Аппаратура' ); Query OK, 1 row affected INSERT INTO catalogs VALUES(NULL,'Безопасность') Query OK, 1 row affected SELECT * FROM catalogs; cat_ID cat_name 1 Программирование 2 Интернет 3 Базы данных 4 Сети 5 Мультимедиа 6 Аппаратура 7 Безопасность 7 rows in set (0.00 sec)

Откатываем транзакцию оператором ROLLBACK(изменения не сохранились).

mysql> ROLLBACK; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM catalogs; cat_ID cat_nane 1 Программирование 2 Интернет 3 Базы данных 4 Сети 5 Мультимедиа 5 rows in set (0.00 sec)

Воспроизведем транзакцию и сохраним действия оператором COMMIT.

mysql> INSERT INTO catalogs VALUES(NULL,'Аппаратура' ); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO catalogs VALUES(NULL,'Безопасность') Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM catalogs; cat_ID cat_nane 1 Программирование 2 Интернет 3 Базы данных 4 Сети 5 Мультимедиа 8 Аппаратура 9 Безопасность 7 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 row affected (0.00 sec) mysql> SELECT * FROM catalogs; cat_ID cat_nane 1 Программирование 2 Интернет 3 Базы данных 4 Сети 5 Мультимедиа 8 Аппаратура 9 Безопасность 7 rows in set (0.00 sec)

Вопросы

1. Что такое транзакция?
2. Какие запросы допустимы внутри тразакции?
3. К чему приведёт использование DDL запроса внутри транзакции?

Просто заметки

6.7 Команды управления транзакциями и блокировками в MySQL

6.7.1 Синтаксис команд BEGIN/COMMIT/ROLLBACK

По умолчанию MySQL работает в режиме autocommit . Это означает, что при выполнении обновления данных MySQL будет сразу записывать обновленные данные на диск.

При использовании таблиц, поддерживающих транзакции (таких как InnoDB , BDB ), в MySQL можно отключить режим autocommit при помощи следующей команды:

SET AUTOCOMMIT=0

После этого необходимо применить команду COMMIT для записи изменений на диск или команду ROLLBACK , которая позволяет игнорировать изменения, произведенные с начала данной транзакции.

Если необходимо переключиться из режима AUTOCOMMIT только для выполнения одной последовательности команд, то для этого можно использовать команду START TRANSACTION или BEGIN или BEGIN WORK :

START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT;

START TRANSACTION была добавлена в MySQL 4.0.11. Это — рекомендованный способ открыть транзакцию, в соответствии с синтаксисом ANSI SQL.

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

При выполнении команды ROLLBACK после обновления таблицы, не поддерживающей транзакции, пользователь получит ошибку ( ER_WARNING_NOT_COMPLETE_ROLLBACK ) в виде предупреждения. Все таблицы, поддерживающие транзакции, будут перезаписаны, но ни одна таблица, не поддерживающая транзакции, не будет изменена.

При выполнении команд START TRANSACTION или SET AUTOCOMMIT=0 необходимо использовать двоичный журнал MySQL для резервных копий вместо более старого журнала записи изменений. Транзакции сохраняются в двоичном системном журнале как одна порция данных (перед операцией COMMIT ), чтобы гарантировать, что транзакции, по которым происходит откат, не записываются. See section 4.9.4 Бинарный журнал обновлений.

Следующие команды автоматически завершают транзакцию (как если бы перед выполнением данной команды была сделана операция COMMIT ):

Команда	Команда	Команда
ALTER TABLE	BEGIN	CREATE INDEX
DROP DATABASE	DROP TABLE	RENAME TABLE
TRUNCATE

Уровень изоляции для транзакций можно изменить с помощью команды SET TRANSACTION ISOLATION LEVEL . . See section 6.7.3 Синтаксис команды SET TRANSACTION .

6.7.2 Синтаксис команд LOCK TABLES/UNLOCK TABLES

LOCK TABLES tbl_name [AS alias] [, tbl_name [AS alias] . ] . UNLOCK TABLES

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

Чтобы использовать команду LOCK TABLES в MySQL 4.0.2, необходимо иметь глобальные привилегии LOCK TABLES и SELECT для заданных таблиц. В MySQL 3.23 для этого необходимы привилегии SELECT , INSERT , DELETE и UPDATE для рассматриваемых таблиц.

Основные преимущества использования команды LOCK TABLES состоят в том, что она позволяет осуществлять эмуляцию транзакций или получить более высокую скорость при обновлении таблиц. Ниже это разъясняется более подробно.

Если в потоке возникает блокировка операции READ для некоторой таблицы, то только этот поток (и все другие потоки) могут читать из данной таблицы. Если для некоторой таблицы в потоке существует блокировка WRITE , тогда только поток, содержащий блокировку, может осуществлять операции чтения ( READ ) и записи ( WRITE ) на данной таблице. Остальные потоки блокируются.

Различие между READ LOCAL и READ состоит в том, что READ LOCAL позволяет выполнять неконфликтующие команды INSERT во время существования блокировки. Однако эту команду нельзя использовать для работы с файлами базы данных вне сервера MySQL во время данной блокировки.

При использовании команды LOCK TABLES необходимо блокировать все таблицы, которые предполагается использовать в последующих запросах, употребляя при этом те же самые псевдонимы, которые будут в запросах! Если таблица упоминается в запросе несколько раз (с псевдонимами), необходимо заблокировать каждый псевдоним!

Блокировка WRITE обычно имеет более высокий приоритет, чем блокировка READ , чтобы гарантировать, что изменения обрабатываются так быстро, как возможно. Это означает, что если один поток получает блокировку READ и затем иной поток запрашивает блокировку WRITE , последующие запросы на блокировку READ будут ожидать, пока поток WRITE не получит блокировку и не снимет ее. Можно использовать блокировки LOW_PRIORITY WRITE , позволяющие другим потокам получать блокировки READ в то время, как основной поток находится в состоянии ожидания блокировки WRITE . Блокировки LOW_PRIORITY WRITE могут быть использованы только если есть уверенность, что в конечном итоге будет период времени, когда ни один из потоков не будет иметь блокировки READ .

Команда LOCK TABLES работает следующим образом:

1. Сортирует все блокируемые таблицы в порядке, который задан внутренним образом, т.е. «зашит» (с точки зрения пользователя этот порядок не задан).
2. Блокировка WRITE ставится перед блокировкой READ , если таблицы блокируются с блокировками READ и WRITE .
3. Блокирует одну таблицу единовременно, пока поток не получит все блокировки.

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

Использование для таблицы блокировки LOW_PRIORITY WRITE всего лишь означает, что MySQL будет выполнять данную конкретную блокировку, пока не появится поток, запрашивающий блокировку READ . Если поток получил блокировку WRITE и находится в ожидании блокировки следующей таблицы из списка блокируемых таблиц, то все остальные потоки будут ожидать, пока блокировка WRITE не будет снята. Если это представляет серьезную проблему для вашего приложения, то следует подумать о преобразовании имеющихся таблиц в таблицы иного вида, поддерживающие транзакции.

Поток, ожидающий блокировку таблицы, можно безопасно уничтожить с помощью команды KILL . See section 4.5.5 Синтаксис команды KILL .

Учтите, что нельзя блокировать любые таблицы, используемые совместно с оператором INSERT DELAYED , поскольку в этом случае команда INSERT выполняется как отдельный поток.

Обычно нет необходимости блокировать таблицы, поскольку все единичные команды UPDATE являются неделимыми; никакой другой поток не может взаимодействовать с какой-либо SQL-командой, выполняемой в данное время. Однако в некоторых случаях предпочтительно тем или иным образом осуществлять блокировку таблиц:

• Если предполагается выполнить большое количество операций на группе взаимосвязанных таблиц, то быстрее всего это сделать, блокировав таблицы, которые вы собираетесь использовать. Конечно, это имеет свою обратную сторону, поскольку никакой другой поток управления не может обновить таблицу с блокировкой READ или прочитать таблицу с блокировкой WRITE . При блокировке LOCK TABLES операции выполняются быстрее потому, что в этом случае MySQL не производит запись на диск содержимого кэша ключей для заблокированных таблиц, пока не будет вызвана команда UNLOCK TABLES (обычно кэш ключей записывается на диск после каждой SQL-команды). Применение LOCK TABLES увеличивает скорость записи/обновления/удаления в таблицах типа MyISAM .
• Если вы используете таблицы, не поддерживающие транзакций, то при использовании программы обработки таблиц необходимо применять команду LOCK TABLES для гарантии, что никакой другой поток не вклинился между операциями SELECT и UPDATE . Ниже показан пример, требующий использования LOCK TABLES для успешного выполнения операций:

mysql> LOCK TABLES trans READ, customer WRITE; mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id; mysql> UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id; mysql> UNLOCK TABLES;

Используя пошаговые обновления ( UPDATE customer SET value=value+new_value ) или функцию LAST_INSERT_ID() , применения команды LOCK TABLES во многих случаях можно избежать.

Некоторые проблемы можно также решить путем применения блокирующих функций на уровне пользователя GET_LOCK() и RELEASE_LOCK() . Эти блоки хранятся в хэш-таблице на сервере и, чтобы обеспечить высокую скорость, реализованы в виде pthread_mutex_lock() и pthread_mutex_unlock() . See section 6.3.6.2 Разные функции.

Чтобы получить дополнительную информацию о механизме блокировки, обращайтесь к разделу section 5.3.1 Как MySQL блокирует таблицы.

Можно блокировать все таблицы во всех базах данных блокировкой READ с помощью команды FLUSH TABLES WITH READ LOCK . See section 4.5.3 Синтаксис команды FLUSH . Это очень удобно для получения резервной копии файловой системы, подобной Veritas, при работе в которой могут потребоваться заблаговременные копии памяти.

Примечание: Команда LOCK TABLES не сохраняет транзакции и автоматически фиксирует все активные транзакции перед попыткой блокировать таблицы.

6.7.3 Синтаксис команды SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

Устанавливает уровень изоляции транзакций.

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

Установить глобальный уровень изоляции по умолчанию для утилиты mysqld можно с помощью опции —transaction-isolation=. . See section 4.1.1 Параметры командной строки mysqld .

Какие команды автоматически завершают транзакцию в mysql

MySQL имеет очень сложный, но интуитивно понятный интерфейс SQL. Эта глава описывает различные команды, типы и функции, которые Вы должны знать, чтобы использовать MySQL эффективно. Эта глава также может служить справочником по всем функциональным возможностям, включенным в MySQL.

USE db_name

Команда USE db_name сообщает, чтобы MySQL использовал базу данных db_name как заданную по умолчанию для последующих запросов. База данных остается текущей до конца сеанса, или пока не будет выдана другая инструкция USE :

mysql> USE db1; mysql> SELECT count(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT count(*) FROM mytable; # selects from db2.mytable

Создание специфической базы данных посредством инструкции USE не препятствует Вам обращаться к таблицам в других базах данных. Пример ниже обращается к таблице author из базы данных db1 и таблице editor из базы данных db2 :

mysql> USE db1; mysql> SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id;

Инструкция USE предусмотрена для совместимости с Sybase.

tbl_name

DESCRIBE представляет собой сокращение для вызова SHOW COLUMNS FROM . Подробности в разделе «4.10 Получение информации о базах данных, таблицах, столбцах и индексах».

DESCRIBE обеспечивает информацию относительно столбцов таблицы. col_name может быть именем столбца или строкой, содержащей групповые символы SQL `%’ и `_’ .

Если типы столбцов не те, которые Вы задавали в инструкции CREATE TABLE , обратите внимание, что MySQL иногда изменяет типы столбцов. Подробности в разделе «7.3.1 Тихие изменения спецификации столбца».

Эта инструкция предусмотрена для совместимости с Oracle.

Инструкция SHOW обеспечивает подобную информацию. Подробности в разделе «4.10 Синтаксис SHOW «.

По умолчанию, MySQL выполняется в режиме autocommit . Это означает, что, как только Вы сделаете модификацию, MySQL сохранит ее на диск.

Если Вы используете транзакционно-безопасные таблицы (подобно BDB , InnoDB , Вы можете перевести MySQL в режим не- autocommit следующей командой:

SET AUTOCOMMIT=0

После того, как это сделано, Вы должны использовать COMMIT , чтобы сохранить Ваши изменения на диске, или ROLLBACK , если Вы хотите игнорировать изменения, которые сделали с начала Вашей транзакции.

Если Вы хотите переключать режим AUTOCOMMIT для одного набора инструкций, Вы можете использовать команды обрамления BEGIN или BEGIN WORK так:

BEGIN; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT;

Обратите внимание, что, если Вы используете не транзакционно-безопасные таблицы, изменения будут сохранены сразу, независимо от состояния режима autocommit .

Если Вы делаете ROLLBACK , когда Вы модифицировали не транзакционно-безопасные таблицы, Вы получите ошибку ( ER_WARNING_NOT_COMPLETE_ROLLBACK ) как предупреждение. Все транзакционно-безопасные таблицы будут восстановлены, но любая транзакционно-небезопасная таблица не будет изменяться.

Если Вы используете BEGIN или SET AUTOCOMMIT=0 , Вы должны использовать двоичный файл регистрации MySQL для резервирования вместо старого файла регистрации модификаций. Транзакции сохранены в двоичном протоколе, запись для COMMIT может гарантировать, что транзакции, которые прокручены обратно, не сохранены.

Следующие команды автоматически заканчивают транзакцию (как будто Вы сделали COMMIT перед выполнением команды):

ALTER TABLE	BEGIN	CREATE INDEX
DROP DATABASE	DROP TABLE	RENAME TABLE
TRUNCATE

Вы можете изменять уровень изоляции для транзакций командой SET TRANSACTION ISOLATION LEVEL . . Подробности в разделе «9.2.3 Синтаксис SET TRANSACTION «.

LOCK TABLES tbl_name [AS alias] [, tbl_name . ] . UNLOCK TABLES

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

Основные причины использовать LOCK TABLES : эмуляция транзакций или получение большего быстродействия при модифицировании таблиц. Это объясняется более подробно позже.

Если поток получает блокировку READ на таблице, он (и все остальные) могут только читать из таблицы. Если поток получает блокировку WRITE на таблице, то только он может читать или писать таблицу. Другие потоки блокированы.

Различие между READ LOCAL и READ в том, что READ LOCAL позволяет непротиворечивым инструкциям INSERT выполняться в то время, как установлена блокировка. Это не может использоваться, если Вы собираетесь управлять файлами базы данных снаружи MySQL в то время, как Вы поставили блокировку.

Когда Вы используете LOCK TABLES , Вы должны блокировать все таблицы, которые Вы собираетесь использовать, и использовать тот же самый псевдоним, который собираетесь применить в Ваших запросах! Если Вы используете таблицу в запросе несколько раз (с псевдонимами), Вы должны получить блокировку для каждого псевдонима!

Блокировки WRITE обычно имеют более высокий приоритет, чем READ , чтобы гарантировать, что модификации будут обработаны как можно скорее. Это означает, что, если один поток получает блокировку READ , и затем другой поток запрашивает блокировку WRITE , последующие запросы блокировки READ будут ждать, пока поток WRITE не получит блокировку и не снимет ее. Вы можете использовать блокировку LOW_PRIORITY WRITE , чтобы позволить другим потокам получать блокировки READ , в то время как поток ждет блокировку WRITE . Вы должны использовать блокировку LOW_PRIORITY WRITE только в случае, если Вы уверены, что будет в конечном счете такой момент, когда никакие потоки не будут иметь запрос на блокировку READ .

1. Сортирует все таблицы, которые будут блокированы, во внутреннем определенном порядке (с точки зрения пользователя, порядок неопределен).
2. Если таблица блокирована с помощью блокировок read и write, write всегда размещается перед read.
3. Блокируется одна таблица за раз, пока поток не получает все блокировки.

Эта стратегия гарантирует, что блокировка таблицы свободна от тупиков. Имеются, однако, другие вещи, о которых надо знать:

Если Вы используете блокировку LOW_PRIORITY_WRITE для таблицы, это означает, что MySQL будет ждать эту блокировку до тех пор, пока не останется потока, который просит блокировку READ . Когда поток имеет блокировку WRITE и ждет, чтобы получить блокировку для следующей таблицы в списке таблиц блокировки, все другие потоки будут ждать освобождения блокировки WRITE . Если это становится серьезной проблемой для Вашей прикладной программы, Вы должны рассмотреть преобразование некоторых из Ваших таблиц в транзакционно-безопасные.

Вы можете безопасно уничтожать поток, который ждет блокировку таблицы, с помощью команды KILL . Подробности в разделе «4.9 Синтаксис KILL «.

Обратите внимание, что Вы НЕ должны блокировать таблицы, которые Вы используете с INSERT DELAYED . Это потому, что в этом случае INSERT выполняется отдельным потоком.

Обычно Вы не должны блокировать таблицы, поскольку все одиночные инструкции UPDATE атомные: никакой поток не может сталкиваться с любым другим, в настоящее время выполняющим инструкции SQL. Имеется несколько случаев, когда стоит блокировать таблицы:

• Если Вы собираетесь выполнять много операций на связке таблиц, намного быстрее блокировать таблицы, которые Вы собираетесь использовать. Конечно, никакой другой поток не может модифицировать блокированную на READ таблицу, и никакой поток не сможет читать блокированную на WRITE таблицу. Причина того, что некоторые вещи выполняются быстрее под LOCK TABLES в том, что MySQL не будет сбрасывать на диск кэш ключей для блокированных таблиц до вызова UNLOCK TABLES (обычно кэш ключей сбрасывается на диск после каждой инструкции SQL). Это ускоряет вставки, удаления и обновления на таблицах MyISAM .
• Если Вы используете драйвер таблицы в MySQL, который не поддерживает транзакции, Вы должны использовать LOCK TABLES , если Вы хотите гарантировать, что никакой другой поток не обработается между SELECT и UPDATE . Пример, показанный ниже, требует LOCK TABLES , чтобы выполниться безопасно:

mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id= some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES;

Используя инкрементные модификации ( UPDATE customer SET value=value+new_value ) или функцию LAST_INSERT_ID() , Вы во многих случаях можете избежать использования LOCK TABLES .

Вы можете также решать некоторые проблемы, используя функции GET_LOCK() и RELEASE_LOCK() . Эти блокировки сохранены в таблице hash на сервере и выполнены через вызовы pthread_mutex_lock() и pthread_mutex_unlock() для ускорения работы. Подробности в разделе «6.5.2 Дополнительные функции «.

Вы можете блокировать все таблицы во всех базах данных с блокировками чтения командой FLUSH TABLES WITH READ LOCK . Подробности в разделе «4.8 Синтаксис FLUSH «. Это очень удобный способ получать резервные копии, если Вы имеете файловую систему, подобную Veritas, которая может делать кадры состояния.

ОБРАТИТЕ ВНИМАНИЕ : LOCK TABLES не транзакционно-безопасна и автоматически завершает любые активные транзакции перед попыткой блокировать таблицы.

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Устанавливает уровень изоляции транзакции глобально, для целого сеанса или следующей транзакции.

Заданное по умолчанию поведение должно установить уровень изоляции для следующей (не начатой) транзакции.

Если Вы устанавливаете привилегию GLOBAL , это будет воздействовать на все новые созданные потоки. Вы будете нуждаться в привилегии PROCESS , чтобы сделать это.

Установка привилегии SESSION будет воздействовать на следующую и на все будущие транзакции.

HANDLER table OPEN [AS alias] HANDLER table READ index <=|>=| <=|<>(value1, value2, . ) [WHERE . ] [LIMIT . ] HANDLER table READ index [WHERE . ] [LIMIT . ] HANDLER table READ [WHERE . ] [LIMIT . ] HANDLER table CLOSE

Команда HANDLER обеспечивает прямой доступ к интерфейсу таблиц MySQL, совершая обход SQL-оптимизатора. Таким образом, это работает быстрее, чем SELECT.

Первая форма инструкции HANDLER открывает таблицу, делая ее доступной через следующий вызов HANDLER . READ .

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

Третья форма выбирает одну (или определенное предложением LIMIT число) строку в индексном порядке, соответствуя условиям определения WHERE запроса.

Четвертая форма (без индексной спецификации) выбирает одну (или определенное предложением LIMIT число) строку из таблицы в естественном порядке строк (как они сохранены в файле данных), соответствуя условиям определения WHERE запроса. Это быстрее, чем HANDLER table READ index , когда нужен полный просмотр таблицы.

Последняя форма закрывает таблицу, открытую с помощью вызова HANDLER . OPEN .

HANDLER это инструкция низкого уровня, например, она не обеспечивает непротиворечивость. Вызов HANDLER . OPEN НЕ блокирует таблицу. Так что другие потоки могут работать с таблицей и менять данные.

Начиная с Version 3.23.23, MySQL имеет поддержку для полнотекстовой индексации и поиска. Полнотекстовые индексы в MySQL представляют собой индекс типа FULLTEXT . Индекс FULLTEXT может быть создан из столбцов VARCHAR и TEXT в вызове CREATE TABLE или добавлен позже через инструкции ALTER TABLE или CREATE INDEX . Для больших наборов данных, добавление индекса FULLTEXT через ALTER TABLE (или CREATE INDEX ) намного быстрее, чем вставка строк в пустую таблицу с индексом.

Поиск выполняется с помощью функции MATCH .

mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO articles VALUES -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management . '), -> (0,'How To Use MySQL Efficiently', 'After you went through a . '), -> (0,'Optimizing MySQL','In this tutorial we will show how to . '), -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalize . '), -> (0,'MySQL vs. YourSQL', 'In the following database comparison we . '), -> (0,'MySQL Security', 'When configured properly, MySQL could be . '); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); +----+-------------------+---------------------------------------------+ | id | title | body | +----+-------------------+---------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison we . | | 1 | MySQL Tutorial | DBMS stands for DataBase Management . | +----+-------------------+---------------------------------------------+ 2 rows in set (0.00 sec)

Функция MATCH соответствует запросу естественного языка для текстовой совокупности AGAINST , которая является просто набором столбцов, покрытых индексом FULLTEXT ). Для каждой строки в таблице это возвращает релевантность: меру подобия между текстом в этой строке (в столбцах, которые являются частью совокупности) и запросом. Когда это используется в предложении WHERE (см. пример выше) возвращенные строки автоматически сортируются с уменьшением релевантности. Релевантность представлена неотрицательным числом с плавающей запятой. Нулевая релевантность означает, что нет никакого подобия.

Вышеупомянутое представляет собой базисный пример использования функции MATCH . Строки будут возвращены с уменьшением релевантности.

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles; +----+-----------------------------------------+ | id | MATCH (title,body) AGAINST ('Tutorial') | +----+-----------------------------------------+ | 1 | 0.64840710366884 | | 2 | 0 | | 3 | 0.66266459031789 | | 4 | 0 | | 5 | 0 | | 6 | 0 | +----+-----------------------------------------+ 5 rows in set (0.00 sec)

Этот пример показывает, как найти релевантность. Поскольку предложения WHERE или ORDER BY не присутствуют в запросе, возвращенные строки не упорядочиваются.

mysql> SELECT id, body, MATCH (title,body) AGAINST ( -> 'Security implications of running MySQL as root') AS score -> FROM articles WHERE MATCH (title,body) AGAINST -> ('Security implications of running MySQL as root'); +----+-----------------------------------------------+-----------------+ | id | body | score | +----+-----------------------------------------------+-----------------+ | 4 | 1. Never run mysqld as root. 2. Normalize . | 1.5055546709332 | | 6 | When configured properly, MySQL could be . | 1.31140957288 | +----+-----------------------------------------------+-----------------+ 2 rows in set (0.00 sec)

Это более сложный пример: запрос возвращает релевантность и дополнительно сортирует строки с ее уменьшением. Чтобы достичь этого, нужно определить MATCH дважды. Обратите внимание, что это не вызовет никакой перегрузки, так как оптимизатор MySQL обратит внимание, что эти два обращения MATCH идентичны, и вызовут код поиска только однажды.

MySQL использует очень простой синтаксический анализатор, чтобы расчленить текст на слова. Слово является любой последовательностью символов, чисел, знаков ‘ и _ . Любое слово, которое присутствует в списке stopword или слишком короткое (3 символа или меньше), игнорируется.

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

Такая методика работает лучше всего с большими совокупностями (фактически, это было тщательно настроено на этот путь). Для очень маленьких таблиц распределение слов не отражает адекватно их семантическое значение, и эта модель может производить причудливые результаты.

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL'); Empty set (0.00 sec)

Поиск слова MySQL не производит никаких результатов в вышеупомянутом примере. Слово MySQL присутствует больше, чем в половине строк, и обрабатывается как stopword (то есть с семантическим значением, равным нулю).

Слово, которое соответствует половине строк в таблице, менее вероятно определяет релевантные документы. Фактически, наиболее вероятно, что поиск по нему найдет множество несоответствующих документов. Все мы знаем, что это случается очень часто, когда мы пробуем что-то поискать в Internet. Таким строкам были назначены низкие семантические значения в этом специфическом наборе данных .

• Все параметры для функции MATCH должны быть столбцами из той же самой таблицы, которая является частью того же самого индекса.
• Параметром AGAINST должна быть строка-константа.

Обратите внимание, что поиск был тщательно настроен для самой лучшей эффективности. Изменение заданного по умолчанию поведения будет, в большинстве случаев, делать результаты поиска хуже. Не изменяйте исходники MySQL, если Вы не знаете точно, что Вы делаете!

Минимальная длина слова, которое будет индексировано определена в файле myisam/ftdefs.h строкой

#define MIN_WORD_LEN 4

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_FREQ

• REPAIR TABLE и ALTER TABLE работают с индексами FULLTEXT , а OPTIMIZE TABLE с индексами FULLTEXT теперь работает в 100 раз быстрее.
• MATCH . AGAINST поддерживает следующие boolean operators :
  • + слово означает, что слово должно присутствовать в каждой возвращенной строке.
  • — слово означает, что слово не должно присутствовать в каждой возвращенной строке.
  • < и >могут использоваться, чтобы уменьшить и увеличить вес слова в запросе.
  • ~ может использоваться, чтобы назначить отрицательный вес слову.
  • * является оператором усечения.

  
  

  • Ускорить все операции с индексами FULLTEXT .
  • Поддержка скобок () в булевом поиске.
  • Поиск фраз, операторы близости.
  • Булев поиск может работать без индекса FULLTEXT (но очень медленно).
  • Поддержка для «always-index words». Это такие строки, которые пользователь определяет как слова, например, «C++», «AS/400», «TCP/IP» и т.д.
  • Поддержка для поиска в таблицах типа MERGE .
  • Поддержка для многобайтных наборов символов.
  • Сделать список stopword зависимым от языка данных в таблице.
  • Происхождение (зависимое от языка данных, конечно).
  • Универсальный обработчик пользовательских UDF (?).
  • Сделать модель более гибкой (добавляя некоторые корректируемые параметры для FULLTEXT в вызов CREATE/ALTER TABLE ).

  Все о MySQL. Управление поведением транзакции

  MySQL имеет в своем арсенале две переменные, позволяющие управлять транзак­циями, — AUTOCOMMIT и TRANSACTION ISOLATION LEVEL. Мы обсудим их подробнее в следующих разделах.
  Автоматическое выполнение транзакции
  По умолчанию MySQL сразу же выполняет каждый SQL-запрос. Этот режим работы называется режимом автовыполнения и является причиной того, что каждый сеанс MySQL не требуется начинать операторами START TRANSACTION или завершать операторами COMMIT или ROLLBACK. Или другими словами, MySQL рассматривает каждый запрос как транзакцию, состоящую из одного оператора.
  Это стандартное поведение можно изменить, манипулируя переменной AUTOCOMMIT, которая управляет режимом автовыполнения MySQL. Следующий фрагмент демонст­рирует отключение стандартного поведения MySQL, заключающегося в выдаче команды COMMIT после каждого SQL-оператора.
  Листинг 12.10.
  mysql> SET AUTOCOMMIT = 0;
  Query OK, 0 rows affected (0.02 sec)
  После этого любое изменение в таблицах не будет сохранено в базе данных до тех пор, пока не поступит команда завершения транзакции COMMIT. В действительности, если за­вершить сеанс MySQL без команды COMMIT, база данных автоматически запустит ко­манду ROLLBACK для того, чтобы отменить все изменения, сделанные во время этого се­анса, отменяя тем самым всю работу, проделанную во время сеанса. Это можно проил­люстрировать на следующем примере.
  Листинг 12.11.
  mysql> SET AUTOCOMMIT = 0;
  Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM gallery; Empty set (0.00 sec)
  mysql> INSERT INTO gallery (id, filename, dsc) VALUES (52, ‘dog.gif’, ‘Me and my pooch’);
  Query OK, 1 row affected (0.00 sec)
  mysql> exit
  Bye

  

  Больше скорости
  Плохая производительность MySQL зачастую бывает вызвана большим количеством небольших обновлений совместно с активизированным режимом автовыполнения.
  А теперь запустим новый сеанс.
  Листинг 12.12.
  [user@host] $ mysql
  Welcome to the MySQL monitor. Commands end with ; or g.
  Your MySQL connection id is 14 to server version: 4.0.12-max-debug
  mysql> SELECT * FROM gallery;
  Empty set (0.01 sec)
  Режим автовыполнения всегда можно включить вновь, вернув переменную AUTOCOMMIT в ее исходное состояние.
  Листинг 12.13.
  mysql> SET AUTOCOMMIT = 1;
  Query OK, 0 rows affected (0.00 sec)
  При включении режима автовыполнения, MySQL автоматически выдаст команду COMMIT и сохранит все открытые транзакции.
  Получить текущее значение переменной AUTOCOMMIT можно в любой момент с по­мощью оператора SELECT.

  Переменная AUTOCOMMIT является сеансовой переменной и по умолчанию при за­пуске сеанса нового клиента имеет значение 1. На момент написания этой книги еще не была предусмотрена возможность задавать по умолчанию значение этой переменной, равное 0. Более подробную информацию об изменении и просмотре переменных сервера можно получить в главе 13, «Администрирование и настройка».
  Ошибочный тип
  Переменная AUTOCOMMIT воздействует только на таблицы, поддерживающие работу с транзакциями. При работе с такими таблицами, например MyISAM, переменная AUTOCOMMIT не оказывает никакого влияния и изменения в таких таблицах всегда сохраняются сразу же.

  Похожие публикации:

  1. Как включить запись разговора на самсунг
  2. Как посмотреть как выглядела страница вконтакте раньше
  3. Как сделать ios 14 на андроид хонор
  4. Почему iphone 6 не обновляется до ios 13