Структуры и управления потоками в хранимых процедурах.
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
delimiter |
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
IF variable1 = 0 THEN
SELECT variable1;
END IF;
IF param1 = 0 THEN
SELECT 'Parameter value = 0';
ELSE
SELECT 'Parameter value <> 0';
END IF;
END;
|
delimiter ;
Конструкция case, с ней мы знакомы в выражении SELECT, но она допустима и в хранимых процедурах:
| DELIMITER // | |||||||||||||||||
| CREATE PROCEDURE `proc_CASE` (IN param1 INT) | |||||||||||||||||
| BEGIN | |||||||||||||||||
| DECLARE variable1 INT; | |||||||||||||||||
| SET variable1 = param1 + 1; | |||||||||||||||||
| CASE | |||||||||||||||||
| WHEN variable1 = 0 THEN | |||||||||||||||||
| INSERT INTO table1 VALUES (param1); | |||||||||||||||||
| WHEN variable1 = 1 THEN | |||||||||||||||||
| INSERT INTO table1 VALUES (variable1); | |||||||||||||||||
| ELSE | |||||||||||||||||
| INSERT INTO table1 VALUES (99); | |||||||||||||||||
| END CASE; | |||||||||||||||||
| END // | |||||||||||||||||
Конструкция WHILE
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вот пример цикла:
| DELIMITER // | |||||||||||||
| CREATE PROCEDURE `proc_WHILE` (IN param1 INT) | |||||||||||||
| BEGIN | |||||||||||||
| DECLARE variable1, variable2 INT; | |||||||||||||
| SET variable1 = 0; | |||||||||||||
| WHILE variable1 < param1 DO | |||||||||||||
| INSERT INTO table1 VALUES (param1); | |||||||||||||
| SELECT COUNT(*) INTO variable2 FROM table1; | |||||||||||||
| SET variable1 = variable1 + 1; | |||||||||||||
| END WHILE; | |||||||||||||
| END // | |||||||||||||
LEAVE –прервать цикл
ITERATE – Продолжить следующую итерацию
Хранимые функции – идея та же самая, логика на стороне сервера. Хранимая функция возвращает какое-то одно значение.
CREATE FUNCTION ИмяПроцедуры RETURNS ТипДанных
BEGIN
Запросы на языке SQL
END;
Функции можно использовать в SQL-операторах. Именно для этого они и используются.
Пример. Пусть дано отношение R1(room, lesson_date). Требуется вывести на экран всю эту таблицу отстортировав ее по полю room. Предположим в поле room хранятся следующие значения: БК-1 БК-2 БК-3 БК-4 БК-11 БК-22 БК-3
Выполним простой запрос
SELECT room, lesson_date
FROM R1
ORDER BY room;
В результате мы получим:
Здесь удобно использовать функцию:
DELIMITER |
CREATE FUNCTION sp_room_number(room VARCHAR(5)) RETURNS INT
BEGIN
RETURN SUBSTR(room,4)*1; //аналог функции ПРАВ
END;
DELIMITER ;
Теперь используем эту функцию:
SELECT room, lesson_date
FROM R1
ORDER BY sp_room_number (room);
Транзакции и типы хранилищ БД.
При одновременной работе нескольких пользователей с одной и той же информационной базой периодически возникают ситуации, когда два или более пользователей пытаются не только одновременно прочитать одни те же данные, но и внести в них какие-то изменения. Очевидно, что если в таких случаях не предусмотреть возможности подключения специальных механизмов системы, то могут возникнуть проблемы, связанные с целостностью и достоверностью данных, хранимых в информационной базе.
Что такое блокировка?
Блокировка- это информация о том, что данный ресурс захвачен «кем-то», для выполнения какого-то действия.
Рассмотрим простой пример. Продавец продает яблоки. Так получилось, что продавец очень рассеян и ему приходится абсолютно все записывать. По этой же причине все яблоки у него пронумерованны.
Пришел покупатель Иванов и ему понравилось яблоко №4. Он хочет его купить. Иванов достает кошелек и отсчитывает деньги.
Тем временем, продавец делает запись в своей книге: «Яблоко №4 - продано Иванову». Эта запись и есть блокировка.
Обратите внимание, что на самом деле яблоко все еще находится у продавца, Иванов его не купил. Может быть и не сможет купить (например окажется, что не хватает денег). Но у продавца уже записано, что это яблоко нельзя предлагать другим покупателям до тех пор, пока Иванов не завершит процесс покупки. Этот процесс, состоящий из нескольких взаимосвязанных действий (выбор яблока, отсчитывание денег, передача денег продавцу, передача яблока покупателю) называется транзакцией. Блокировка должна быть установлена в момент выбора Ивановым яблока №4 и снята после завершения транзакции покупки.
Тем временем подходит Петров и тоже хочет купить яблоко. Он сможет купить любое яблоко, кроме яблока №4
Таким образом, смысл блокировки в том, чтобы запретить некоторые действия над общим ресурсом на некоторое ограниченное время. В данном случае Петрову запрещено выбирать яблоко №4 до тех пор, пока Иванов не завершил свою транзакцию покупки. То есть, Петров находится в состоянии ожидания на блокировке.
Из приведенного примера понятно, что блокировки - это необходимый механизм при конкурентном доступе к общим ресурсам.
В самом деле, что бы было, если бы продавец не записал в своей книге, что яблоко из четвертой ячейки нельзя предлагать другим покупателям? Скорее всего, произошел бы конфликт между Ивановым и Петровым, возможно при этом «досталось» бы и продавцу. В любом случае, если запись в книге продавца отсутствует, исход данной ситуации становится непредсказуемым. Неизвестно, кому достанется это яблоко, неизвестно у кого окажутся деньги, которые Иванов за него отдал и так далее.
Если же блокировка на яблоко №4 установлена, то это гарантирует однозначный исход: Иванов гарантированно сможет купить яблоко, если у него хватит денег. Если же он откажется от покупки, то только в этом случае яблоко из 4 ячейки сможет купить Петров.
Следует понимать, что в силу различных причин блокировки могут быть как «хорошими» (необходимыми), так и «плохими» (избыточными).
Рассмотрим еще один вариант развития событий, который поясняет откуда берутся «плохие» блокировки.
Покупатель Иванов хочет купить одно яблоко. Он перебирает все яблоки из ящика по одному, выбирая, какое лучше. При этом продавец записывает в своей книге все яблоки, которые понравились Иванову.
В это время подходит Петров и не может купить ни одного яблока, потому что они все заблокированы Ивановым. Петров ждет некоторое время, обижается и уходит. Это событие соответствует ошибке "Превышение времени ожидания блокировки". А Иванов, в результате, выбирает одно единственное яблоко (самое лучшее) и покупает только его. Таким образом, все остальные яблоки были заблокированы зря. Если бы этих блокировок не было, то Петров, возможно, тоже купил бы яблоко.
В данном случае (в отличие от первого примера) Петров как раз столкнулся с «плохими» (избыточными) блокировками.
Подводя итог сказанному важно отметить, что «хорошие» блокировки обязательно должны присутствовать в прикладном решении. Именно благодаря им обеспечивается предсказуемость действий пользователей, целостность и непротиворечивость данных.
С «плохими» блокировками нужно бороться и в идеале их не должно существовать в прикладном решении.
Теперь ближе к серверу. Существует, в общем и целом, два вида блокировок:
блокировка на чтение и блокировка на запись.
Блокировка на чтение означает буквально следующее, вы говорите серверу, мне очень важны эти данные, мне нужно, чтобы никто их не модифицировал, пока я с ними работаю. Блокировка на чтение означает, что никто не сможет модифицировать данные, пока они заблокированы, но эти данные доступны другим пользователям на чтение. Пока эту блокировку не снимите, никто другой такую же блокировку не поставит.
Как это делается:
LOCK TABLES ИМЯ_ТАБЛИЦЫ READ //блокировка на чтение
UNLOCK TABLES; //отмена блокировки
Пример:
LOCK TABLES R1 READ
SELECT COURSE FROM R1;
Другой пользователь может читать, но не может вставить данные, что произойдет, если в этот момент другой пользователь захочет внести данные в эту таблицу?
Запрос др. пользователя:
INSERT INTO R1 (ID, ROOM, COURSE) VALUES (10, ‘BK-61’, ‘химия’)
Рано или поздно первый пользователь таблицу разблокирует и тогда второй пользователь сможет вставить свои данные.
Блокировка на запись более жесткая. Она выполняется точно также:
LOCK TABLES ИМЯ_ТАБЛИЦЫ WRITE //блокировка на запись
UNLOCK TABLES; //отмена блокировки
Что она означает. Вы как бы говорите серверу, я сам буду эту таблицу модифицировать, сейчас данные в ней еще не готовы, т.е. другим пользователям я запрещаю даже читать данные, ну раз они внесены не полностью.
Пример:
LOCK TABLES R1 WRITE
Другой пользователь не сможет даже читать данные из этой таблицы, он просто повиснет.
Запрос др. пользователя:
SELECT * FROM R1;
Блокировки позволяют избежать конфликтов между пользователями, но замедляют работу пользователей. Поэтому многие сервера позволяют сделать отложенную вставку данных. Т.е., например, к заблокированной таблице поступил запрос с предложением INSERT, он зафиксировался, но не выполнился, пользователь может работать дальше. А его вставка данных произойдет только тогда, когда блокировка с таблицы будет снята. В таком случае в запрос вставки данных надо добавить соответствующее указание.
INSERT DELAYED INTO R1 (ID, ROOM, COURSE) VALUES (10, ‘BK-61’, ‘химия’)
Большинство клиентов, будь то сайт или прикладная программа, не дадут, конечно, пользователю зависнуть надолго, они контролируют этот процесс, с помощью таймаута. Т.е. через какое-то время, если сервер не выполняет запрос, то клиент этот запрос отменяет и выводит сообщение об ошибке.
Многие сервера предлагают и другие виды блокировок.
Блокировка записей – не вся таблица блокируется, а всего одна запись, т.е. к другим записям таблицы другие пользователи доступ имеют, а конкретно к этой – нет.
Для этого в MYSQL есть возможности, но они плохо реализованы.
Такого рода блокировки считаются устаревшими и теперь уже используют транзакции.
Транзакция – это процесс, группа операций, которая может быть выполнена либо полностью успешно, соблюдая целостность данных и независимо от параллельно идущих транзакций, либо не выполнена вообще.
Как определить, что часть команд надо выделить в транзакцию? Для этого разработан тест ACID. Четыре свойства должны быть у транзакции и если они требуются, то да, надо выделить часть команд в транзакцию. К этим свойствам относятся:
1. Атомарность. Команды – неделимы, либо все до конца, либо ничего.
2. Согласованность – данные должны оставаться в согласованном состоянии. Целостность БД нарушена не должна быть. Никакие правила внутренней системы она нарушать не должна.
3. Изоляция – Конкурирующие транзакции не должны пересекаться.
4. Долговечность – Любые сбои не должны влиять на транзакцию.
Как пользоваться транзакциями.
Всего три команды:
START TRANSACTION //начинаю транзакцию
COMMIT //успешное завершение
ROLLBACK // откат транзакции
Посмотрим на примере.
| Пользователь 1 | Пользователь 2 | ||
| Начинает транзакцию | START TRANSACTION | Понятия не имеет ни о каких транзакциях 1 пользователя | |
| Получает выборку | SELECT count(*) FROM R1; | Выполняет свой запрос. | SELECT count(*) FROM R1; |
| В результате: | В результате: | ||
| Вставляет данные: Или удаляет | INSERT INTO R1 (ID, ROOM, COURSE) VALUES (11, ‘BK-63’, ‘биология’) DELETE FROMr1 WHERE ID =1; | ||
| Получает выборку | SELECT count(*) FROM R1; | Выполняет свой запрос. | SELECT count(*) FROM R1; |
| В результате: | В результате, пользователь понятия не имеет о том, что вставка данных произошла: | ||
| Решил отменить транзакцию | ROLLBACK; | ||
| Получает выборку | SELECT count(*) FROM R1; | Выполняет свой запрос. | SELECT count(*) FROM R1; |
| В результате: | В результате, пользователь понятия не имеет о том, что вставка данных произошла: |
Или же наоборот пользователь не отменял транзакцию, а зафиксировал ее:
| Пользователь 1 | Пользователь 2 | ||
| Решил подтвердить транзакцию | COMMIT; | ||
| Получает выборку | SELECT count(*) FROM R1; | Выполняет свой запрос. | SELECT count(*) FROM R1; |
| В результате: | В результате, пользователь получит обновленные данные, т.к. транзакция зафиксирована. |
Оба пользователя друг другу не противоречат. Дело в том, что сервер вставку, изменение или удаление данных автоматически делает в транзакции. Это происходит благодаря движку InnoDB. Таким образом, благодаря этому движку, мы имеем так называемые автоматические блокировки. Но что будет происходить, если нам надо вставить или удалить 100 записей? В таком случае каждый раз будет создаваться транзакция, потом изменение БД, затем фиксирование транзакции. Это неудобно, поэтому, есть еще и возможность подключить управляемые блокировки. Но тут надо быть осторожными. Как это делается?
SET AUTOCOMMIT = 1; // переменная, которая по умолчанию имеет значение 1 и означает, что используются автоматические блокировки. Если значение этой переменной перевести в ноль, то будут использоваться автоматические блокировки.
SET AUTOCOMMIT = 0;
START TRANSACTION
INSERT INTO R1 (ID, ROOM, COURSE) VALUES (11, ‘BK-63’, ‘биология’)
………………………………………
Commit;
SET AUTOCOMMIT = 1;
Уровни изоляции транзакций:
(пример оплаты по картам)
Не всегда изоляция транзакций – это хорошо, на базах данных с огромным количеством пользователей, приходится устанавливать уровни изоляции.
1. READ UNCOMMITTED – чтение незафиксированных изменений своей транзакции и конкурирующих транзакций.
2. READ COMMITTED – чтение всех изменений своей транзакции и зафиксирвоанных изменений конкурирующих транзакций.
3. REPEATTABLE READ – чтение всех изменений своей транзакции, любые изменения, внесенные конкурирующими транзакциями после начала своей недоступны. Полный уровень изоляции.
4. SERIALIZABLE – запрещено чтение всех данных измененных с начала транзакции, в том числе и своей.
| Пользователь 1 | Пользователь 2 | ||
| Устанавливает возможность видеть изменения других транзакций до завершения | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | Устанавливает возможность видеть изменения других транзакций до завершения | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| Начинает транзакцию | START TRANSACTION | Начинает транзакцию | START TRANSACTION |
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку | SELECT count(*) FROM R1; |
| В результате: | В результате: | ||
| Удаляет данные | DELETE FROMr1 WHERE ID =1; | ||
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку | SELECT count(*) FROM R1; |
| В результате: | В результате, пользователь увидел, что произошло удаление данных | ||
| Решил отменить транзакцию | ROLLBACK; | ||
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку. | SELECT count(*) FROM R1; |
| В результате: | В результате: |
Стандартным уровнем изоляции является уровень REPEATTABLE READ
Посмотрим на примере
| Пользователь 1 | Пользователь 2 | ||
| Устанавливает 2 уровень изоляции | SET SESSION TRANSACTION ISOLATION LEVEL REPEATTABLE READ; | Устанавливает 2 уровень изоляции | SET SESSION TRANSACTION ISOLATION LEVEL REPEATTABLE READ; |
| Начинает транзакцию | START TRANSACTION | Начинает транзакцию | START TRANSACTION |
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку | SELECT count(*) FROM R1; |
| В результате: | В результате: | ||
| Удаляет данные | DELETE FROMr1 WHERE ID =1; | ||
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку | SELECT count(*) FROM R1; |
| В результате: | В результате, пользователь не видит, что произошло удаление данных | ||
| Подтвердил транзакцию | Commit; | ||
| Получает выборку | SELECT count(*) FROM R1; | Получает выборку. | SELECT count(*) FROM R1; |
| В результате: | Даже после того, как пользователь 1 подтвердил свою транзакцию, пользователь 2 не видит внесенных изменений, до тех пор, пока не завершит свою транзакцию | ||
| Подтвердил транзакцию | Commit; | ||
| Получает выборку. | SELECT count(*) FROM R1; | ||
| В результате: |
Типы хранилищ MYSQL.
База данных MySQL работает с несколькими видами хранилищ данных. Хранилища отличаются способом хранения данных, набором возможностей.
Для того чтобы посмотреть какие типы поддерживает ваша инсталляция MySQL необходимо выполнить команду SHOW ENGINES;
В результате вы получаете таблицу содержащую информацию о том, какие типы (движки) таблиц установлены в вашей системе и краткое описание их возможностей. Нас в основном будет интересовать столбец support, который содержит информацию о поддержке типа таблицы и может принимать значения: NO - не поддерживается, YES - поддерживается, DEFAULT -используется по-умолчанию. Для просмотра информации о том, где хранится база данных можно использовать команду: select @@datadir;
В версии MySQL 5.5 поддерживается 9 различных типов таблиц.
· InnoDB - движок с поддержкой транзакций, откатов и защитой от потери данных. В данном типе таблиц используются блокировки на уровне записи и не блокирующее чтение, что позволило улучшить производительность при многопользовательском режиме работы. InnoDB сохраняет пользовательские данные в кластерных индексах, что позволяет компенсировать в/в для простых запросов основанных на первичных ключах.
· MyISAM - движок таблиц MySQL используемый в основном в Web-приложениях, хранилищах данных и других программных средах. Данный тип таблиц поддерживается всеми инсталляциями MySQL. Отсутствует поддержка транзакций.
· Memory - хранит данные в оперативной памяти для очень быстрого доступа. Также известен как HEAP (куча).
· Merge - используется для логического объединения одинаковых MyISAM таблиц и обращение к ним, как к единому объекту. Хорошо подойдет для очень больших хранилищ данных.
· Archive - идеальное решение для хранения больших объёмов информации, к которой не требуется частый доступ.
· Federated - предоставляет возможность объединять различные MySQL сервера для создания одной логической базы данных из нескольких физических машин. Идеально подойдет для архитектур, которые поддерживают распределенное хранение данных.
· CSV - хранит пользовательские данные в текстовых файлах разделяя значения запятыми. Используется если необходим простой обмен с приложениями, которые умеют экспортировать/импортировать данные из CSV формата.
· Blackhole - принимает, но не возвращает никаких данных. Результатами любых запросов из таких хранилищ будут пустые выборки.
· Example - тестовый движок, не выполняет никаких функций, будет полезен только разработчикам, которые собираются писать свой движок, в качестве примера.