изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

• Название: Она позволяет решать многошаговые задачи одним выражением
• Дата: 09.02.2018
• Размер: 1.6 Mb.
• Имя файла: изучаем SQL.pdf
• Тип: Документы; #36127
• страница: 25 из 31

228
Глава 11. Условная логика
17 WHERE account.account_id =
18 (SELECT t.account_id
19 FROM transaction t
20 WHERE t.txn_id = 999);
Здесь всего три выражения case: два из них (строки 5 и 13) служат для изменения знака суммы транзакции для дебетовых транзакций,
а третье выражение case (строка 10) предназначено для проверки даты доступности фондов. Если эта дата еще не наступила, к доступному ос татку добавляется нуль; в противном случае добавляется сумма тран закции.
Обработка значений Null
Хотя значения null удобны для хранения в таблицах неизвестных зна чений столбцов, они не всегда подходят для отображения или исполь зования в выражениях. Например, в окне ввода данных вы, скорее всего, предпочтете отображать слово «unknown», а не оставлять пустое поле. При извлечении данных выражение case позволяет вместо значе ния null подставлять строку:
SELECT emp_id, fname, lname,
CASE
WHEN title IS NULL THEN 'Unknown'
ELSE title
END
FROM employee;
Значения null в вычислениях часто являются причиной результата null
, как показывает следующий пример:
mysql> SELECT (7 * 5) / ((3 + 14) * null);
+
+
| (7 * 5) / ((3 + 14) * null) |
+
+
| NULL |
+
+
1 row in set (0.08 sec)
Проводя вычисления, полезно преобразовать значения null в число
(обычно 0 или 1) с помощью выражения case, чтобы обеспечить резуль тат вычисления, отличный от null. Например, при вычислении с учас тием столбца account.avail_balance можно было бы подставить 0 (при сложении или вычитании) или 1 (при умножении или делении) для тех счетов, которые уже открыты, но средства на них еще не помещены:
SELECT +
CASE
WHEN avail_balance IS NULL THEN 0
ELSE avail_balance
END
+

Упражнения
229
Если числовой столбец может содержать значения null, хорошо бы ис пользовать условную логику при любых вычислениях с участием это го столбца, чтобы гарантировать значимые результаты.
Упражнения
Проверьте свою способность применять условную логику с помощью следующих примеров. Выполнив задание, сравните свои решения с от ветами, приведенными в приложении С.
11.1
Перепишите следующий запрос, использующий простое выражение case
, таким образом, чтобы получить аналогичные результаты с помо щью выражения case с перебором вариантов. Попытайтесь свести к минимуму количество блоков when.
SELECT emp_id,
CASE title
WHEN 'President' THEN 'Management'
WHEN 'Vice President' THEN 'Management'
WHEN 'Treasurer' THEN 'Management'
WHEN 'Loan Manager' THEN 'Management'
WHEN 'Operations Manager' THEN 'Operations'
WHEN 'Head Teller' THEN 'Operations'
WHEN 'Teller' THEN 'Operations'
ELSE 'Unknown'
END
FROM employee;
11.2
Перепишите следующий запрос так, чтобы результирующий набор со держал всего одну строку и четыре столбца (по одному для каждого от деления). Назовите столбцы branch_1, branch_2 и т. д.
mysql> SELECT open_branch_id, COUNT(*)
> FROM account
> GROUP BY open_branch_id;
+
+
+
| open_branch_id | COUNT(*) |
+
+
+
| 1 | 8 |
| 2 | 7 |
| 3 | 3 |
| 4 | 6 |
+
+
+
4 rows in set (0.00 sec)

Транзакции
До сих пор все примеры в данной книге были примерами одиночных
SQL выражений. В этой главе рассматриваются требования и среда, не обходимые для совместного выполнения нескольких SQL выражений.
Многопользовательские базы данных
Системы управления базами данных разрешают обращаться к данным и изменять их не только одному пользователю, но и нескольким одно временно. Если каждый пользователь выполняет запросы, как это происходит с хранилищем данных в течение обычных рабочих часов,
для сервера БД это не создает больших проблем. Однако если некото рые пользователи добавляют и/или изменяют данные, серверу прихо дится сохранять довольно много промежуточных результатов.
К примеру, создается отчет, представляющий доступный остаток всех текущих счетов, открытых в отделении. Однако одновременно с вы полнением отчета происходит следующее:
•
Служащий отделения обрабатывает вклад для одного из клиентов.
•
Клиент заканчивает снимать деньги на банкомате в операционном зале.
•
Банковское приложение, выполняющееся в конце каждого месяца,
начисляет процент по счетам.
Следовательно, пока создается отчет, несколько пользователей изме няют данные. Так, какие цифры должны появиться в отчете? Ответ от части зависит от того, как сервер реализовывает блокировку (locking) –
механизм управления одновременным использованием ресурсов дан ных. Большинство серверов БД применяют одну из двух стратегий блокировки:

Многопользовательские базы данных
231
•
Пользователи, осуществляющие запись в БД, должны запрашивать и получать от сервера блокировку записи (write lock) для изменения данных. А пользователи, считывающие данные из БД, должны за прашивать и получать от сервера блокировку чтения (read lock)
для осуществления запросов к данным. В то время как чтение мо жет осуществляться одновременно несколькими пользователями,
для каждой таблицы (или ее части) одновременно выдается только одна блокировка записи, и запросы на чтение блокируются до тех пор, пока не будет снята блокировка записи.
•
Пользователи, осуществляющие запись в БД, для изменения дан ных должны запрашивать и получать от сервера блокировку запи си, но пользователи, считывающие данные, для запроса данных не нуждаются ни в каком типе блокировки. Вместо этого сервер гаран тирует, что читатель видит непротиворечивое представление дан ных (данные представляются неизменными, даже несмотря на то,
что другие пользователи могут их модифицировать), начиная с мо мента начала запроса до его завершения. Этот подход известен как
контроль версий
(versioning).
У обеих стратегий есть свои достоинства и недостатки. При первом под ходе время ожидания может оказаться длительным, если одновремен но поступило много запросов на чтение и запись. Второй подход может создать проблемы в случае длительных запросов, поскольку происхо дит изменение данных. В данной книге обсуждаются три сервера: Mi crosoft SQL Server использует первый подход, Oracle Database – вто рой, а MySQL – оба подхода (в зависимости от выбранного пользовате лем механизма хранения (storage engine), который обсуждается не много позже).
Также есть ряд различных стратегий блокировки ресурса. Блокирова ние может выполняться на одном из трех разных уровней, или с одной из трех детализаций (granularities):
Блокирование таблицы
Предотвращает одновременное изменение несколькими пользова телями данных одной таблицы.
Блокирование страницы
Предотвращает одновременное изменение несколькими пользова телями данных одной страницы таблицы (страница – сегмент памя ти, обычно от 2 до 16 Кбайт).
Блокирование строки
Предотвращает одновременное изменение несколькими пользова телями одной строки таблицы.
У этих подходов тоже есть свои плюсы и минусы. При блокировке всей таблицы возникает очень мало промежуточных результатов, но по мере роста числа пользователей такая блокировка очень быстро приводит

232
Глава 12. Транзакции к недопустимым временам ожидания. С другой стороны, в случае бло кировки строки сохраняется намного больше промежуточных резуль татов, но такая блокировка позволяет многим пользователям вносить изменения в одну таблицу, если это касается разных строк. Из трех серверов, обсуждаемых в этой книге, Microsoft SQL Server использует блокировки страницы и строки, Oracle Database – блокировку строки,
а MySQL может блокировать таблицу, страницу или строку (опять же в зависимости от выбранного механизма хранения).
Возвращаясь к отчету: данные, появляющиеся на его страницах, будут отражать состояние БД или на момент начала создания отчета (если сервер использует контроль версий), или на момент осуществления сер вером блокировки чтения (если сервер использует блокировки и чте ния, и записи).
Что такое транзакция?
Если бы серверы БД работали безостановочно, если бы пользователи все гда позволяли программам завершать выполнение и если бы приложе ния всегда завершались без неустранимых ошибок, прерывающих вы полнение, то незачем было бы обсуждать параллельный доступ к базам данных. Однако ни на одну из перечисленных ситуаций рассчитывать нельзя. Следовательно, чтобы несколько пользователей могли осущест влять доступ к одним и тем же данным, необходим еще один элемент.
Этой дополнительной деталью пазла параллелизма является транзак
ция
(transaction) – механизм группировки нескольких SQL выраже ний, позволяющий успешно выполниться всем или ни одному из них.
Если клиент пытается перевести 500 долларов со сберегательного счета на текущий, он немного расстроится, если деньги будут успешно сняты с первого счета, но не внесены на второй. Какой бы ни была причина сбоя (сервер был выключен для проведения работ по техническому об служиванию, истекло время ожидания запроса на блокировку страни цы таблицы account и др.), клиент захочет вернуть свои 500 долларов.
Чтобы защититься от ошибок такого рода, программа, обрабатываю щая запрос на перевод, сначала начинает транзакцию, затем выполня ет SQL выражения, необходимые для перемещения денег со сберега тельного счета на текущий, и, если все проходит успешно, завершает транзакцию, формируя команду commit (фиксировать). Однако если происходит что то непредвиденное, программа выдает команду rollback
(откат), которая указывает серверу отменить все изменения, внесенные с момента начала транзакции. Весь процесс может выглядеть так:
START TRANSACTION;
/* Снять деньги с первого счета, обеспечив достаточный остаток */
UPDATE account SET avail_balance = avail_balance 500
WHERE account_id = 9988
AND avail_balance > 500;

Что такое транзакция?
233
IF <Предыдущим выражением была изменена ровно одна строка> THEN
/* Внести деньги на следующий счет */
UPDATE account SET avail_balance = avail_balance + 500
WHERE account_id = 9989;
IF <Предыдущим выражением была изменена ровно одна строка> THEN
/* Все получилось, сделать изменения постоянными */
COMMIT;
ELSE
/* Что то не так, отменить все изменения, сделанные в данной транзакции */
ROLLBACK;
END IF;
ELSE
/* Недостаток средств на счете или при обновлении возникла ошибка */
ROLLBACK;
END IF;
Хотя предыдущий фрагмент кода может показаться похожим на один из процедурных языков программирования, предостав ляемых основными компаниями производителями БД, такими как PL/SQL от Oracle или Transact SQL от Microsoft, он написан на псевдокоде и не пытается имитировать ни один конкретный язык.
Приведенный выше фрагмент кода начинается с запуска транзакции.
После этого делается попытка удалить 500 долларов с текущего счета и затем добавить 500 долларов на сберегательный счет. Если все про ходит хорошо, транзакция фиксируется; однако если что то не так,
происходит откат транзакции, т. е. все внесенные с начала транзакции изменения отменяются.
С помощью транзакции программа гарантирует, что пятьсот долларов или останутся на сберегательном счету, или перейдут на текущий счет без какой либо возможности краха. Независимо от того, была ли тран закция зафиксирована или произошел откат, все ресурсы, занятые
(например, блокировка записи) во время выполнения транзакции, по завершении транзакции высвобождаются.
Конечно, если программе удается завершить оба выражения update, но сервер выключается до того, как смогут выполниться commit или roll back
, откат транзакции произойдет, когда сервер вернется в рабочий режим. (Одна из задач, которую должен выполнить сервер перед воз вращением в нормальный режим работы, – найти все незавершенные транзакции, запущенные на момент выключения сервера, и выпол нить их откат.)
Запуск транзакции
Серверы БД обрабатывают создание транзакций одним из двух воз можных способов:
•
Активная транзакция всегда присутствует для каждого сеанса ра боты с БД, поэтому нет ни необходимости, ни способа для явного

234
Глава 12. Транзакции начала транзакции. По завершении транзакции сервер автоматиче ски начинает новую транзакцию для сеанса пользователя.
•
Если транзакция не начата явно, отдельные SQL выражения фик сируются автоматически независимо друг от друга. Чтобы начать транзакцию, сначала нужно запустить на выполнение команду.
Из трех рассматриваемых серверов первый подход использует Oracle
Database, а Microsoft SQL Server и MySQL – второй. Одно из преиму ществ подхода Oracle к обработке транзакций в том, что даже в случае одиночной SQL команды есть возможность сделать откат, если пользо вателя не удовлетворяет результат или он изменил свое мнение. Таким образом, если вы забудете вставить блок where в выражение delete, ос танется возможность отменить неверные действия (разумеется, только если вы осознаете, допив свой утренний кофе, что не хотели удалять все 125 000 строк своей таблицы)). Однако при работе с MySQL и SQL
Server, как только нажата клавиша
Enter, изменения, осуществленные
SQL выражением, становятся постоянными (и тогда только админист ратор БД сможет восстановить исходные данные из резервной копии или какими либо иными средствами).
Стандарт SQL:2003 включает команду start transaction (запустить транзакцию), предназначенную для явного начала транзакции. MySQL
соответствует этому стандарту, а пользователи SQL Server должны вы зывать команду begin transaction (начать транзакцию). Для обоих сер веров, пока транзакция не начата явно, все операции выполняются в режиме автоматической фиксации (autocommit mode), т. е. сервер автоматически фиксирует отдельные выражения. Следовательно, мож но принять решение об использовании транзакций и выполнить ко манду запустить/начать транзакцию или просто позволить серверу фиксировать отдельные выражения.
Оба сервера, MySQL и SQL Server, позволяют отключать режим авто матической фиксации для отдельных сеансов. В этом случае серверы будут вести себя в отношении транзакций точно так же, как Oracle Da tabase. В SQL Server для отключения режима автоматической фикса ции служит следующая команда:
SET IMPLICIT_TRANSACTIONS ON
MySQL позволяет отключить режим автоматической фиксации так:
SET AUTOCOMMIT=0
Если режим автоматической фиксации выключен, все SQL команды выполняются в рамках транзакции, и их фиксацию или откат следует выполнять явно.
Рекомендация: при каждом входе в систему следует отключать режим автоматической фиксации. Выполнение всех SQL выра жений в рамках транзакции должно войти в привычку. По край

Что такое транзакция?
235
ней мере, это поможет вам избежать унизительной необходимо сти просить администратора БД восстановить уничтоженные по неосторожности данные.
Завершение транзакции
Если транзакция запущена – явно посредством команды start transac tion или неявно сервером БД, – пользователь должен явно завершить ее, чтобы внесенные им изменения стали постоянными. Это делается с помощью команды commit, которая указывает серверу пометить изме нения как постоянные и высвободить все ресурсы (т. е. снять блоки ровку страниц или строк), используемые во время транзакции.
Если решено отменить все изменения, сделанные с момента начала транзакции, необходимо выполнить команду rollback, которая указы вает серверу вернуть данные в то состояние, в каком они находились до начала транзакции. После завершения выполнения rollback все ре сурсы, используемые сеансом, высвобождаются.
Кроме выполнения команды commit или rollback, возможны еще не сколько сценариев завершения транзакции – или как косвенный ре зультат действий пользователя, или как результат чего то, находяще гося вне власти пользователя:
•
Выключение сервера; в этом случае откат транзакции будет выпол нен автоматически при возобновлении работы сервера.
•
Выполнение SQL выражения управления схемой, например alter table
, что приведет к фиксации текущей транзакции и запуску но вой.
•
Выполнение еще одной команды start transaction, в результате чего происходит фиксация предыдущей транзакции.
•
Преждевременное завершение транзакции сервером, который вы явил взаимоблокировку (deadlock) и решил, что виновна в этом дан ная транзакция. В этом случае будет выполнен откат транзакции и пользователь получит сообщение об ошибке.
Из этих четырех сценариев первый и третий довольно просты, а вот два других заслуживают некоторого внимания. Если говорить о втором сценарии, изменения базы данных, независимо от того, было ли это до бавление новой таблицы, индексация или удаление столбца из табли цы, не могут быть отменены. Таким образом, команды, изменяющие схему, должны выполняться вне транзакции. Поэтому если транзак ция уже запущена, сервер зафиксирует ее, выполнит команду( ы)
SQL выражений управления схемой и затем автоматически запустит новую транзакцию для данного сеанса. Сервер не будет информиро вать пользователя о происходящем, поэтому следует действовать ак куратно, чтобы выражения, составляющие единицу работы, невзна чай не были разбросаны сервером по нескольким транзакциям.

236
Глава 12. Транзакции
Четвертый сценарий занимается выявлением взаимоблокировок. Взаи моблокировка происходит, когда две разные транзакции ожидают ре сурсов, удерживаемых другой транзакцией. Например, транзакция А
только что обновила таблицу account и ожидает блокировки записи для таблицы transaction. В это время транзакция В вставила строку в таб лицу transaction и ожидает блокировки записи для таблицы account.
Если случится, что обе транзакции изменяют одну и ту же страницу или строку (в зависимости от детализации блокировок, используемой сервером БД), каждая из них будет бесконечно ожидать, когда другая транзакция завершит выполнение и высвободит необходимый ресурс.
Серверы БД всегда должны быть настороже и выявлять такие ситуа ции, чтобы не остановиться полностью; при выявлении взаимоблоки ровки выбирается одна из транзакций (произвольно или по какому то критерию) и осуществляется ее откат, чтобы дать возможность другой транзакции продолжить выполнение.
В отличие от обсуждавшегося ранее второго сценария, сервер БД сфор мирует ошибку и сообщит пользователю о том, что был сделан откат его транзакции из за выявления взаимоблокировки. Например, при работе с MySQL будет получена ошибка #1213, сопровождаемая сле дующим сообщением:
Message: Deadlock found when trying to get lock; try restarting transaction
(Сообщение: Обнаружена взаимоблокировка при попытке блокировки; попытайтесь перезапустить транзакцию)
Как предлагает сообщение об ошибке, разумным будет повторно за пустить транзакцию, для которой был сделан откат из за выявления взаимоблокировки. Однако если взаимоблокировки становятся до вольно частым явлением, вероятно, необходимо скорректировать при ложения, осуществляющие доступ к БД, чтобы снизить вероятность взаимоблокировок (одна общепринятая стратегия – обеспечить, чтобы доступ к ресурсам всегда осуществлялся в одном и том же порядке, на пример изменение данных счета выполнялось бы перед вставкой дан ных транзакции).