БД в многопользовательской среде

1. БД в многопользовательской среде

Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

2. БД в многопользовательской среде

Архитектура клиент-сервер
Сервер БД (SQL-сервер) – СУБД, размещаемая на сервере и выполняющая запросы,
поступающие от приложений, находящихся на клиентских
компьютерах.
Клиент формирует интерфейс пользователя и запросы к серверу на чтение и изменение данных,
хранящихся на сервере.
Сервер обеспечивает целостное хранение данных и выдает клиенту результаты запросов.
Инициатором диалога с сервером, как правило, является клиент.
Запросы к серверу формируются на языке SQL.
Примеры серверов БД: Microsoft SQL Server, Oracle, Informix.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

3. БД в многопользовательской среде

Механизм блокировок
В многопользовательской среде с одними и теми же данными могут одновременно работать
несколько пользователей, что может привести к противоречиям.
Стратегии блокировок:
1. Оптимистическая блокировка – запись блокируется только на время ее сохранения,
– в процессе редактирования запись остается
незаблокированной,
– при одновременном внесении изменений разными
пользователями могут возникать конфликты.
2. Пессимистическая блокировка – как только пользователь начинает редактировать
запись, она блокируется до тех пор, пока
изменение не будет сохранено.
3. Блокировка всех записей – блокируются все записи таблицы на весь период их
открытия,
– другие пользователи не могут изменить или
заблокировать записи.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

4. БД в многопользовательской среде

Обработка транзакций
Транзакция – неделимый набор операций над хранящимися данными.
• Транзакция успешно завершается, если выполнены все ее операции.
• В противном случае происходит откат в состояние, предшествующее выполнению транзакции.
Обработка транзакций выполняется в любом приложении, где два или более действия
должны выполняться согласованно.
Приложение должно указать рамки транзакции:
BeginTrans – начало последовательности операций, объединяемых в одну транзакцию.
CommitTrans – запись результатов всех операций транзакции.
RollBack – откат транзакции, т.е. отмена всех изменений, выполненных в транзакции.
Конкретная форма команд обработки транзакций различается в разных СУБД.
В многопользовательской среде обработка транзакций приобретает особое значение.
Параллельные транзакции – две или более транзакций в БД, производящие обработку
в одно и то же время.
• Шаги транзакций выполняются попеременно (процессор переключается между операциями
транзакций, за определенный промежуток времени выполняется некоторая часть каждой из
транзакций).
• Переключение происходит столь быстро, что складывается впечатление, что транзакции
выполняются одновременно.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

5. БД в многопользовательской среде

Проблемы параллельной обработки транзакций
1. Проблема потерянного обновления.
Пример:
Пользователь 1: Считать элемент X
Пользователь 2: Считать элемент X
Уменьшить X на 5
Уменьшить X на 3
…………………………..
…………………………..
Записать элемент X
Записать элемент X
• Данные обоих пользователей были верными на момент считывания.
• Пользователь 2 записал неверное значение элемента X (без учета изменений, произведенных
пользователем 1).
2. Проблема несогласованного чтения.
Пример:
• Пользователь 2 читает данные, которые были обработаны некоторым фрагментом
транзакции пользователя 1 (не до конца обработанные данные).
• Как следствие, пользователь 2 имеет ошибочные данные.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

6. БД в многопользовательской среде

Решение проблем несогласованностей, вызванных параллельной обработкой транзакций:
Блокировка ресурсов – запрет совместного использования ресурсов путем блокировки
данных, которые считываются для обновления.
Пример:
Пользователь 1: Блокировать элемент X
Пользователь 2: Блокировать элемент X
Считать элемент X
Считать элемент X
Уменьшить X на 5
Уменьшить X на 3
…………………………..
…………………………..
Записать элемент X
Записать элемент X
Разблокировать элемент X
Разблокировать элемент X
• Из-за блокировки транзакция пользователя 2 должна ждать, пока пользователь 1 не закончит работу с
элементом X.
• Пользователь 2 сможет прочесть элемент X только после того, как пользователь 1 завершит
модификацию и разблокирует элемент X.
Сериализуемые транзакции – параллельные транзакции, результат выполнения
которых является таким же, как если бы эти транзакции
выполнялись последовательно.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

7. БД в многопользовательской среде

3. Проблема взаимных блокировок или «смертельные объятия».
Каждая из транзакций ожидает освобождения ресурса, заблокированного другой транзакцией.
Пример:
Пользователь 1: Блокировать элемент X
Пользователь 2: Блокировать элемент Y
Считать элемент X
Считать элемент Y
Блокировать элемент Y
Блокировать элемент X
…………………………..
…………………………..
Способы решения проблемы взаимных блокировок:
А). Предотвращение возникновения взаимных блокировок.
Приложения должны выполнять следующие условия:
• блокировка всех требуемых ресурсов сразу.
или
• блокировка ресурсов в одном и том же порядке.
Б). Обнаружение и распутывание взаимных блокировок.
• СУБД имеют алгоритмы обнаружения взаимных блокировок.
• При возникновении взаимной блокировки обычно отменяется одна из транзакций с
удалением из БД произведенных ею изменений.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

8. БД в многопользовательской среде

Распределение функций в архитектуре клиент-сервер
При разработке приложений в архитектуре клиент-сервер важным вопросом является
распределение функциональности приложения между клиентской и серверной частью.
Основная задача клиентского ПО – обеспечение интерфейса пользователя (ввод данных и
представление результатов в удобном для пользователя виде).
Основная задача серверного ПО – обеспечение целостности и защищенности данных в БД,
быстрая обработка SQL-запросов клиентов.
Логика приложения – правила обработки данных, обусловленные правилами, действующими в
предметной области (бизнес-правилами).
1. Двухзвенные архитектуры.
а). Архитектура с «толстым клиентом».
• Вся логика приложения выполняется на стороне клиента и, соответственно, дублируется для каждого клиента.
• Если логика приложения изменяется, то ее надо менять для каждого клиента.
• Клиент «толстый», т.к. должен обладать достаточным набором ресурсов, чтобы производить всю необходимую
обработку данных, помимо обеспечения пользовательского интерфейса.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

9. БД в многопользовательской среде

б). Архитектура с «тонким клиентом».
• Логика приложения реализуется на стороне сервера.
• Все изменения в логике приложения вносятся только в одном месте – на сервере, соответственно, такие
приложения легче поддерживать.
• Клиент «тонкий», т.к. для обеспечения пользовательского интерфейса мощность рабочей станции может быть
невелика.
в). Стандартная двухзвенная архитектура.
• Логика приложения перераспределяется между клиентом и сервером, находя компромисс между «тонким» и
«толстым» клиентом.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

10. БД в многопользовательской среде

2. Трехзвенные архитектуры.
а). Архитектура с выделенным компонентом логики приложения.
• Логика приложения рассматривается как отдельный компонент (отдельная программа) вне СУБД.
• Этот компонент расположен на сервере БД (там же, где и СУБД).
б). Архитектура с сервером приложений.
• Логика приложения рассматривается как отдельный компонент (отдельная программа) вне СУБД.
• Этот компонент расположен на выделенном компьютере, который называется сервер приложений.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

11. БД в многопользовательской среде

Программирование на стороне сервера
1. Хранимые процедуры
Хранимые процедуры – программы:
• хранящиеся в БД на сервере,
• реализующие сложные запросы и транзакции,
• запускаемые на выполнение одной командой.
Хранимые процедуры:
• могут содержать не только SQL-запросы, но и переменные, операторы, реализующие логику
ветвления, вызовы других хранимых процедур,
• могут принимать входные параметры, чтобы одна процедура могла быть использована разными
приложениями с разной входной информацией,
• вызываются приложением или другой хранимой процедурой путем указания имени вызываемой
процедуры и передачи ей входных параметров.
Достоинства хранимых процедур:
• При вызове хранимой процедуры по сети посылается всего одна команда.
• Все приложения, вызывающие хранимую процедуру, используют одну и ту же логику обработки
данных, заложенную в этой процедуре.
• Изменение логики хранимой процедуры производится на сервере. Эти изменения видны всем
приложениям, работающим с процедурой.
• Хранимая процедура может быть использована как механизм секретности, дав возможность
приложениям вызывать процедуру и запретив прямой доступ к данным в таблицах.
Расширения языка SQL для создания хранимых процедур:
• Transact-SQL (T-SQL) в Microsoft SQL Server.
• PL/SQL в Oracle.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

12. БД в многопользовательской среде

Основы Transact-SQL
Допускается использование любых SQL-запросов:
• запросы-выборки (SELECT),
• запросы-изменения (DELETE, UPDATE, INSERT),
• запросы DDL (CREATE TABLE, DROP TABLE, …).
Пример: Увеличить на 10% все цены на билеты для вылетов, плановая дата которых позже 18.10.19.
CREATE PROC updatePrice
AS
UPDATE Passenger SET Price = Price * 1.1
WHERE PlanDate > ‘10/18/19’
Вызов хранимой процедуры: EXEC updatePrice
Пример: Получить информацию о конкретном рейсе.
CREATE PROC getFlight @flightID char(5)
AS
SELECT * FROM Flight
WHERE id_flight = @flightID
Вызов хранимой процедуры: EXEC getFlight ‘12345’
EXEC getFlight @flightID = ‘12345’
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

13. БД в многопользовательской среде

Возможно использование операторов для управления выполнением хранимой процедурой:
• BEGIN … END – создание блоков операторов.
• WHILE – организация циклов.
• IF … ELSE – ветвление.
• …..
• BEGIN TRAN, COMMIT, ROLLBACK – управление транзакцией.
Пример: Проверка наличия рейса в определенный город.
CREATE PROC existFlight @city char(20)
AS
IF (SELECT Count(*) FROM Flight WHERE Town = @city) >= 1
RETURN 0
ELSE
RETURN 1
Вызов хранимой процедуры: EXEC existFlight ‘Москва’
EXEC existFlight @city = ‘Москва’
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

14. БД в многопользовательской среде

2. Триггеры
Триггеры – особый вид хранимой процедуры, которая связана с определенными
данными и автоматически запускается сервером при попытке изменения
этих данных (вставке, обновлении, удалении).
Триггеры – развитая форма правил (ограничений на значения), которые тоже автоматически активизируются при
изменении данных, которые они защищают.
В отличие от триггера хранимая процедура не имеет жесткой привязки к данным и ее необходимо явно вызывать
для выполнения.
CREATE TRIGGER имя_триггера ON имя_таблицы
FOR { INSERT| UPDATE| DELETE| INSERT, UPDATE| INSERT, DELETE| UPDATE, DELETE| INSERT, UPDATE, DELETE}
AS
……………………………
Пример: Триггер, запрещающий вставку новой строки.
CREATE TRIGGER noUpdate ON Table1
FOR INSERT
AS
ROLLBACK
• Триггер всегда выполняется внутри неявной транзакции. BEGIN TRAN выполняется автоматически в начале триггера,
а COMMIT – в конце триггера.
• Ни один пользователь и ни одна другая процедура не увидят изменений до успешного завершения работы триггера.
• ROLLBACK – единственный управляющий оператор транзакции, который не выполняется автоматически.
• Отменить удаление, обновление, добавление данных можно с помощью явного указания оператора ROLLBACK.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»

15. БД в многопользовательской среде

Transact-SQL поддерживает 2 специальные таблицы: inserted и deleted, в которые
помещаются копии вставляемых, изменяемых и удаляемых строк.
В ходе выполнения команд обновления, удаления и вставки над таблицей триггера и таблицами inserted и deleted
выполняются операции:
• Перед вызовом INSERT-триггера новые строки добавляются в таблицу триггера и таблицу inserted.
• Перед вызовом DELETE-триггера строки удаляются из таблицы триггера и помещаются в таблицу deleted.
• Перед вызовом UPDATE-триггера старые строки удаляются из таблицы триггера и помещаются в таблицу
deleted, затем обновленные строки добавляются в таблицу триггера и в таблицу inserted.
Операция
INSERT
Таблица inserted
Новые строки
DELETE
UPDATE
Таблица deleted
Удаляемые строки
Обновленные строки
Старые строки
Пример: Триггер, обеспечивающий невозможность ввода отрицательного значения цены.
CREATE TRIGGER priceUpdate ON Passenger
FOR INSERT, UPDATE
AS
IF (SELECT Count(*) FROM inserted WHERE Price <= 0) > 0
ROLLBACK
• В теле триггера таблицы inserted и deleted можно использовать.
Управление данными
Кафедра АПУ СПбГЭТУ «ЛЭТИ»