Проект автоматизации процесса грузоперевозок

• Введение
• 1. Нормативные ссылки
• 2. Анализ предметной области
• 2.1 Общее описание предметной области
• 2.2 Описание входных документов и сообщений
• 2.3 Описание выходных документов и сообщений
• 2.4 Список ограничений
• 3. Выбор программных средств
• 4. Проектирование распределенной базы данных
• 4.1 Построение концептуальной модели базы данных
• 4.2 Реализация реляционной базы данных
• 4.2.1 Описание таблиц
• 4.2.2 Описание представлений
• 4.2.3 Диаграмма базы данных
• 5. Распределение данных и репликация
• 5.1 Выбор типа репликации РБД
• 5.2 Настройка репликации
• 5.3 Распределенные запросы к данным
• 6. Управление распределенными транзакциями
• 6.1 Запуск распределенных транзакций
• 6.2 Завершение транзакции
• 6.3 Блокировки
• 6.4 Взаимоблокировки
• 6.4.1 Создание взаимоблокировки
• 7. Масштабирование распределенных баз данных
• 7.1 Создание функции разделения
• 7.2 Создание схемы разделения
• 7.3 Создание разделенных индексов
• 8. Оптимизация распределенных запросов
• 8.1 План выполнения запросов
• 8.2 Монитор производительности
• 8.3 Трассировка запросов
• 9. Индивидуальной задание
• Заключение
• Список использованных источников
• Приложение

Данная курсовая работа призвана рассмотреть ключевые особенности работы транспортной компании, а также выявить процессы грузоперевозок, которые возможно автоматизировать с помощью СУБД.

Транспортные компании ежедневно получают большое количество заказов на грузоперевозки, которые нужно быстро обрабатывать, имеют больший парк автотранспорта, за которым нужно следить и поддерживать в работоспособном состоянии. Чтобы не упустить ни одного заказа, компании предложено автоматизировать процессы с помощью СУБД на базе Microsoft SQL Server 2012, имеющий большой спектр возможностей. Предполагается, что это упростит обработку заказов, ускорит их прием и позволит собирать статистику, что благоприятно скажется на прибыли компании.

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

1. Нормативные ссылки

В настоящей курсовой работе использованы ссылки на следующие руководящие документы:

ГОСТ 34. ххх «Информационная технология. Комплекс стандартов и руководящих документов на автоматизированные системы»

ГОСТ 19. ххх «Единая система программной документации»

IEEE 830−1994 «Рекомендуемая практика формирования спецификаций программного обеспечения»

IEEE 829 — Планирование тестирования программных средств

ГОСТ 28 806 «Качество программных средств. Термины и определения»

ГОСТ 28 195 «Оценка качества программных средств. Общие положения»

ГОСТ 9126 «Информационная технология. Оценка программного продукта Характеристики качества и руководящие указания по их применению»

ГОСТ Р ИСО/МЭК 92 991−93. Руководство по управлению документированием программного обеспечения. — М.: Изд-во стандартов, 1994

2. Анализ предметной области

2.1 Общее описание предметной области

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

2.2 Описание входных документов и сообщений

На вход подаются тип груза, вес, пункт отправления и пункт назначения.

2.3 Описание выходных документов и сообщений

На выходе получаем подобранный автомобиль, дату перевозки, цену

2.4 Список ограничений

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

3. Выбор программных средств

Для реализации поставленной задачи выбран Microsoft SQL Server 2012 в виду его широких возможностей, доступности и богатой документации. Для программирования приложения работы с БД выбрана среда Microsoft Visual Studio 2012 и язык C#, обладающий простотой реализации оконных приложений, привычных обычным пользователям Microsoft Windows.

4. Проектирование распределенной базы данных

4.1 Построение концептуальной модели базы данных

Концептуальная модель РБД выглядит следующим образом:

Рисунок 1 — Концептуальная модель

4.2 Реализация реляционной базы данных

4.2.1 Описание таблиц

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

Таблица Criterias содержит типы грузов в виде иерархии: номер типа, родитель, уровень и название.

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

Таблица TransportCriterias устанавливает соответствие между автотранспортом и типами грузов.

Таблица Timetable отражает занятость автотранспорта в перевозках и дату совершения перевозки.

4.2.2 Описание представлений

Представление «Запрос Критерии+Заказы» отражает данные о заказах в приемлемом для пользователя виде — в шапке таблицы заголовки на русском языке, номера типов грузов заменены на текстовые значения.

Представление «Фильтр Компьютеры» является фильтром предыдущего представления, отображающий только заказы компьютеров, отсортированных по имени поставщика в прямом порядке и по имени потребителя — в обратном.

4.2.3 Диаграмма базы данных

Рисунок 2 — Диаграмма БД

5. Распределение данных и репликация

5.1 Выбор типа репликации РБД

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

5.2 Настройка репликации

Для настройки репликации слиянием выполним следующие несколько шагов:

1. Убедимся, что Агент SQL Server включен. В диспетчере конфигурации SQL Server (рисунок 3) в пункте «Службы SQL Server» проверим, что Агент SQL Server находится в состоянии «Работает», или включим его если это не так.

Рисунок 3 — Диспетчер конфигурации SQL Server

2. В MS SQL Server Management Studio в пункте меню «Репликация» нажать ПКМ на «Локальные публикации» и выбрать «Создать публикацию» .

Рисунок 4 — Создание публикации

3. Выбрать базу данных для репликации, выбрать тип «Публикация слиянием», выбрать необходимые данные для публикации, фильтрацию можно пропустить за ненадобностью, настроить Агент моментальных снимков (рисунок 5), указать учетную запись и пароль к ней (учетная запись с которой Вы входите на сервер, в нашем случае ASH-PCAsh), нажать клавишу «Готово >>|», ввести имя публикации и снова нажать клавишу «Готово» .

4. В пункте «Локальные публикации» нажать ПКМ на только что созданную публикацию и выбрать «Создать подписку» (рисунок 6).

5. Первые две настройки можно оставить по умолчанию, в окне «Подписчики» указать сервер-подписчик и новую базу данных. В появившемся окне указать имя новой базы. Настроить параметры безопасности Агента SQL Server так же, как пункте 3 (рисунок 7). Нажать «Готово >>|» и снова «Готово». Репликация настроена! После синхронизации все выбранные данные будут перенесены в новую базу данных.

Рисунок 5 — Мастер создания публикаций Рисунок 6 — Создание подписки Рисунок 7 — Мастер создания подписки

5.3 Распределенные запросы к данным

Чтобы поддерживать данные в обеих базах в актуальном состоянии, не прибегая к репликации, можно обратиться к распределенным запросам. А чтобы эти запросы в случае каких-либо сбоев не вызвали рассогласования, обратимся к распределенным транзакциям. Подробнее о распределенных транзакциях написано в следующей главе.

6. Управление распределенными транзакциями

6.1 Запуск распределенных транзакций

Распределенную транзакцию в Transact-SQL можно запустить следующими способами:

1. Явно начать распределенную транзакцию, используя инструкцию BEGIN DISTRIBUTED TRANSACTION.

Например, для изменения данных в таблице Orders в распределенной базе данных expert_db и ее удаленной копии re_ex, выполним следующий запрос:

use expert_db

go

begin distributed transaction;

update Orders set Supplier = 'Izmenen' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Supplier = 'Izmenen' Where ID = '1017';

commit transaction;

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

2. Находясь в локальной транзакции, выполнить распределенный запрос. В этом случае транзакция автоматически будет расширена до распределенной. В примере выше просто опустим слово distributed, при этом эффект от выполнения запроса будет тем же.

Рисунок 8 — Работа распределенной транзакции

6.2 Завершение транзакции

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

В попробуем откатить и применить транзакцию:

use expert_db

go

begin transaction

update Orders set Customer = 'x' Where ID = '1016';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'x' Where ID = '1016';

rollback transaction

begin transaction

update Orders set Customer = 'z' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'z' Where ID = '1017';

commit transaction

В результате выполнения примера изменится только строка с ID 1017, так как изменения в строке 1016 мы откатили.

6.3 Блокировки

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

Выполним 2 запроса на выполнение транзакций. Для удобства создадим два окна запроса с среде SQL Management Studio. Поместим код транзакций каждый в свое окно.

— Транзакция 1

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Mass = '1000' WHERE ID = '1008'

— Транзакция 2

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Distance = '100' WHERE ID = '1008'

COMMIT TRANSACTION

Поочередно выполним запросы. Транзакция 2 будет ожидать выполнения до тех пор, пока транзакция 1 не будет завершена либо откачена. Допишем строку COMMIT TRASACTION в первый запрос, выполним его, и второй запрос сразу же выполнится.

6.4 Взаимоблокировки

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

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

Раньше взаимоблокировки представляли собой серьезную проблему, но теперь SQL Server позволяет успешно разрешить ее.

6.4.1 Создание взаимоблокировки

Проще всего создать ситуацию взаимоблокировки в SQL Server с помощью двух подключений в редакторе запросов утилиты Management Studio. Первая и вторая транзакции пытаются обновить одни и те же строки, однако в противоположном порядке.

Поместим код в первое окно запроса и выполним его:

— Транзакция 1 — Шаг 1

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Mass = '1000' WHERE ID = '1008'

Создадим в редакторе запросов второе окно и поместим в него следующий код:

— Транзакция 2 — Шаг 2

USE expert_db

BEGIN TRANSACTION

UPDATE Transport SET MaxDistance = '1000' WHERE ID = '1'

UPDATE Orders SET Distance = '100' WHERE ID = '1008'

COMMIT TRANSACTION

Вторая транзакция ожидает снятие блокировки на строку 1018 в таблице Orders.

Вернемся в первое окно, добавим следующий код и запустим запрос.

— Транзакция 1 — Шаг 3

UPDATE Transport SET InRepair = '1' WHERE ID = '1'

COMMIT TRANSACTION

Транзакция 2 еще не завершена, так как ожидает снятия блокировки, поэтому все еще блокирует строку 1 в таблице Transport. Транзакция 1 обращается к этой строке, продолжая блокировать данные. Таким образом 2 транзакции заблокировали выполнение друг друга. Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее (рисунок 9).

Рисунок 9 — Создание взаимоблокировки

7. Масштабирование распределенных баз данных

7.1 Создание функции разделения

Для удобства обращения к большим объемам данных, разделим таблицу Orders на 3 большие части: заказы до 2006 года, с 2006 по 2011 годы и после 2011 года. Для этого обратимся к функции разделения. Под функцией разделения понимается механизм определения границ разделов. Следующий код разбивает таблицу по столбцу даты на 3 части, используя левую границу, это означает, что диапазон дат входит в раздел до границы, не включая саму границу:

USE expert_db

CREATE PARTITION FUNCTION fnyears (Date)

AS RANGE LEFT FOR VALUES

('31/12/2005', '31/12/2010');

7.2 Создание схемы разделения

Чтобы использовать созданную функцию разделения, применим в таблице схему разделения. Схема разделения позволяет разместить данные в одной или нескольких файловых группах. Так как по умолчанию в базе существует одна файловая группа, добавим еще 3 для таблицы Orders. Следующий код добавляет 3 файловые группы с одним файлом данных в каждой:

USE expert_db;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test1fg;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test2fg;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test3fg;

GO

ALTER DATABASE expert_db

ADD FILE

(

NAME = test1dat1,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt1dat1. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test1fg;

ALTER DATABASE expert_db

ADD FILE

(

NAME = test2dat2,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt2dat2. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test2fg;

GO

ALTER DATABASE expert_db

ADD FILE

(

NAME = test3dat3,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt3dat3. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test3fg;

GO

Теперь можно создать схему разделения. Следующий код создает схему разделения, помещая данные в файловые группы:

CREATE PARTITION SCHEME psYears AS PARTITION fnyears TO (test1fg, test2fg, test3fg);

7.3 Создание разделенных индексов

Для быстрого обращения к данным, создадим кластеризованный индекс в таблице Orders. При создании первичного ключа на ранних этапах проектирования поле ID уже содержало кластеризованный индекс, поэтом сделаем его некластеризованным, так как кластеризованный индекс может быть только один. Для этого в проекте таблице нажать ПКМ на первичном ключе, выбрать «Индексы и ключи», в поле «Создать как кластеризованный» выбрать значение «нет» (рисунок 10).

Рисунок 10 — Изменение индекса

Теперь можно создать кластеризованный индекс. Следующий код выполняет это действие:

CREATE clustered INDEX Order_ID ON Orders (ID, Date) ON psYearsAll (Date);

8. Оптимизация распределенных запросов

8.1 План выполнения запросов

План выполнения запросов позволяет оценить затраты на выполнения конкретного запроса. Это не только информация в виде пиктограмм, но еще и большие таблицы с данными о запросе по щелчку мыши. На рисунке 11 представлен план выполнения запроса. Для вызова плана выполнения запроса в редакторе запросов после ввода необходимого кода нажать на кнопку «Показать предполагаемый план выполнения» в панели инструментов, либо в меню «Запрос» .

Рисунок 11 — План выполнения запроса.

8.2 Монитор производительности

Оценить потребление ресурсов во время выполнения запроса можно не только с помощью плана выполнения, но и с использованием системного монитора производительности. Найти его можно в Панели управления, в папке Администрирование. Системный монитор позволяет в реальном времени просматривать нагрузку на сервер, отслеживая десятки параметров, например, такой важный, как загрузка центрального процессора. На рисунке 12 показан график нагрузки на центральный процессор во время выполнения одного из запросов.

Рисунок 12 — Системный монитор

Как можно пронаблюдать, запрос выборки 100 000 строк в индуктированной таблице увеличивает загрузку процессор на 20−25%.

8.3 Трассировка запросов

Трассировка запросов — это еще один инструмент оптимизации запросов. Трассировка в реальном времени показывает все происходящие на SQL Server процессы, показывая сколько ресурсов и времени было затрачено на выполнение запроса. Чтобы запустить трассировку запросов, необходимо открыть SQL Server Profiler, в меню «Файл» выбрать «Создать трассировку…», подключиться к SQL Server, выбрать необходимые события и запустить. Запустим трассировку и посмотрим, что происходит на сервере в момент выполнения запроса (рисунок 13).

Рисунок 13 — Трассировка запросов

9. Индивидуальной задание

ТЕМА 4. Управление распределенными транзакциями

1 Цели и задачи

1) Изучить основные принципы управления распределенными транзакциями.

2) Выполнить задание по варианту.

3) Ответить на контрольные вопросы по данной теме.

4) Оформить отчет.

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

2.1 Координатор распределенных транзакций

MS SQL Server использует координатор распределенных транзакций (далее DTC) для обслуживания транзакций, затрагивающих несколько серверов, — их подтверждения и отката. Служба DTC использует двухфазную схему подтверждения многосерверных транзакций. Это подразумевает, что доступны оба сервера, при этом служба DTC выполняет следующие действия.

1. Каждый из серверов отправляет сообщение о готовности к подтверждению.

2. Каждый из серверов выполняет первый этап подтверждения, гарантируя тем самым способность к полному подтверждению транзакции.

3. Каждый из серверов сообщает об окончании подготовки к подтверждению.

4. Только после того как все серверы ответили положительно о готовности к подтверждению, сообщение о реальном подтверждении транзакции отправляется всем им.

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

Координатор распределенных транзакций является отдельной службой SQL Server, которая запускается и останавливается с помощью SQL Server Service Manager.

Только один экземпляр этой службы запускается на одном сервере, независимо от количества установленных и запущенных на нем экземпляров SQL Server. Фактическое имя службы — msdtc. ехе, потребляемый объем памяти — 2,5 Мбайт.

Служба DTC должна быть запущена, когда инициируется распределенная транзакция, в противном случае последняя завершится ошибкой.

2.2 Выполнение распределенных транзакций Распределенные транзакции, запускаемые в Transact-SQL, имеют относительно простую структуру. Приложение или сценарий Transact-SQL выполняет инструкцию Transact-SQL, которая инициирует распределенную транзакцию. Экземпляр SQL Server Database Engine, выполняющий инструкцию, становится управляющим сервером в транзакции. Сценарий или приложение затем выполняет либо распределенные запросы к связанным серверам, либо удаленные хранимые процедуры на удаленных серверах.

После того как распределенные запросы и вызовы удаленных процедур выполнены, управляющий сервер автоматически вызывает координатор распределенных транзакций (Майкрософт) (MS DTC), чтобы прикрепить в распределенную транзакцию связанные и удаленные серверы.

Когда сценарий или приложение выполняет инструкцию COMMIT или ROLLBACK, управляющий экземпляр SQL Server вызывает MS DTC, который управляет процессом двухфазной фиксации или уведомляет связанные и удаленные серверы, чтобы они выполнили откат своих транзакций. Инструкции Transact-SQL, контролирующие распределенные транзакции, немногочисленны, поскольку большая часть работы производится внутри экземпляра SQL Server Database Engine и MS DTC.

2.2.1 Запуск распределенных транзакций Распределенную транзакцию в Transact-SQL можно запустить следующими способами:

1. Явно начать распределенную транзакцию, используя инструкцию BEGIN DISTRIBUTED TRANSACTION.

Например, для изменения данных в таблице Orders в распределенной базе данных expert_db и ее удаленной копии re_ex, выполним следующий запрос:

use expert_db

go

begin distributed transaction;

update Orders set Supplier = 'Izmenen' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Supplier = 'Izmenen' Where ID = '1017';

commit transaction;

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

2. Находясь в локальной транзакции, выполнить распределенный запрос. В этом случае транзакция автоматически будет расширена до распределенной. В примере выше просто опустим слово distributed, при этом эффект от выполнения запроса будет тем же.

Рисунок 14 — Работа распределенной транзакции

2.2.2 Завершение транзакции Чтобы транзакция считалась завершенной, после выполнения всех действий ее нужно применить инструкцией COMMIT TRANSACTION, либо откатить инструкцией ROLLBACK TRANSACTION. В первом случае все изменения сохранятся в базе, во втором — отменены.

В следующем примере показано как откатить и применить транзакцию:

use expert_db

go

begin transaction

update Orders set Customer = 'x' Where ID = '1016';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'x' Where ID = '1016';

rollback transaction

begin transaction

update Orders set Customer = 'z' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'z' Where ID = '1017';

commit transaction

В результате выполнения примера изменится только строка с ID 1017, так как изменения в строке 1016 мы откатили.

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

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

Пример. Выполним 2 запроса на выполнение транзакций. Для удобства создадим два окна запроса с среде SQL Management Studio. Поместите код транзакций каждый в свое окно.

— Транзакция 1

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Mass = '1000' WHERE ID = '1008'

— Транзакция 2

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Distance = '100' WHERE ID = '1008'

COMMIT TRANSACTION

Поочередно выполните запросы. Транзакция 2 будет ожидать выполнения до тех пор, пока транзакция 1 не будет завершена либо откачена. Допишем строку COMMIT TRASACTION в первый запрос, выполним его, и второй запрос сразу же выполнится.

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

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

Раньше взаимоблокировки представляли собой серьезную проблему, но теперь SQL Server позволяет успешно разрешить ее.

2.5.1 Создание взаимоблокировки Проще всего создать ситуацию взаимоблокировки в SQL Server с помощью двух подключений в редакторе запросов утилиты Management Studio. Первая и вторая транзакции пытаются обновить одни и те же строки, однако в противоположном порядке.

Пример. Поместите код в первое окно запроса и выполните его:

— Транзакция 1 — Шаг 1

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Mass = '1000' WHERE ID = '1008'

Создайте в редакторе запросов второе окно и поместите в него следующий код:

— Транзакция 2 — Шаг 2

USE expert_db

BEGIN TRANSACTION

UPDATE Transport SET MaxDistance = '1000' WHERE ID = '1'

UPDATE Orders SET Distance = '100' WHERE ID = '1008'

COMMIT TRANSACTION

Вторая транзакция ожидает снятие блокировки на строку 1018 в таблице Orders.

Вернитесь в первое окно, добавьте следующий код и запустите запрос.

— Транзакция 1 — Шаг 3

UPDATE Transport SET InRepair = '1' WHERE ID = '1'

COMMIT TRANSACTION

Транзакция 2 еще не завершена, так как ожидает снятия блокировки, поэтому все еще блокирует строку 1 в таблице Transport. Транзакция 1 обращается к этой строке, продолжая блокировать данные. Таким образом 2 транзакции заблокировали выполнение друг друга. Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее (рисунок 15).

Рисунок 15 — Создание взаимоблокировки

3 Задания Для своего варианта РБД (см. задание темы 3) выполнить следующее:

1. Изучить работу службы DTC.

2. Создать план выполнения нескольких распределенных транзакций.

3. Выполнить запуск транзакций всеми возможными способами, описанными в работе.

4. Выполнить подтверждение и откат транзакций.

5. Создать блокировки разных типов. Исключить блокировки.

6. Сделать выводы по работе.

4 Контрольные вопросы

1. Определение распределенной транзакции и ее назначение.

2. Какие существуют способы выполнения транзакций?

3. Какова структура журнала транзакций?

4. Каковы отличия распределенных транзакций Transact-SQL от MS DTC?

5. В чем заключается особенность использования распределенных транзакций вместо локальных?

6. Какие типы блокировок транзакций существуют?

7. Как удалить взаимоблокировки?

Заключение

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

Список использованных источников

1. Малыхина М. П. Базы данных. Основы, проектирование, использование. — БХВ-Петербург, 2006. — 528 с.

2. Нильсен Пол. Microsoft SQL Server 2005. Библия пользователя. Пер. с англ. — М.: «И.Д. Вильямс», 2008. — 1232 с.

3. Мартин Фаулер, Кендалл Скотт — UML. Основы — 2002.

4. Павловская Т. А. C#. Программирование на языке высокого уровня: учебник для вузов. — СПб.: Питер, 2007. — 432 с.

5. Шилдт Г. Полный справочник по C#. Пер. с англ. — М.: Вильямс, 2004.

6. Орлов С. А. Технологии разработки программного обеспечения. — СПб.: Питер, 2003. — 480с.

7. Леоненков А. Самоучитель UML. — BHV Санкт-Петербург, 2001. — 304с.

8. Иванова Г. С. Технология программирования: Учебник для вузов. — М.: Изд-во МГТУ им. Н. Э. Баумана, 2002.

9. Ляхевич А. Г. Лекции по сетевым технологиям, 2002 г. — 165 с

Приложение А

Листинг использованных запросов

Выборки из таблицы Transport

" SELECT * FROM Transport Where ID IN (select TransportID FROM TransportCriterias WHERE CriteriaID = '" + order. FreightCriteriaID + «') and ID not in (select TransportID from Timetable Where Date = '» + order. Date + «') and Capacity >= '» + order. Mass + «' and MinDistance <= '» + order. Distance + «' and MaxDistance >='» + order. Distance + «' and InRepair = 0»

SELECT * FROM Transport

SELECT CriteriaID FROM TransportCriterias WHERE TransportID = '" + tID + «'

delete from TransportCriterias

INSERT INTO TransportCriterias values ((select ID from Transport where RegNumber Like '" + RegNumber + «'), «+ CriteriaID + «)

SELECT ID, RegNumber FROM Transport

select distinct «+ TransportCrit + «FROM Transport where «+ TransportCrit + «is not null

Выборки из таблицы Criterias

SELECT * FROM Criterias Where [Level] = '" + Level + «' order by Name

SELECT Name FROM Criterias Where ID = '" + ID + «' order by Name

select * from Criterias where Parent = (select ID from Criterias where Name like '" + ParentName + «') order by Name

SELECT * FROM Criterias Where Name LIKE '" + Name + «' and [Level] = '» + Level + «' order by Name

SELECT ID FROM Criterias Where [Name] LIKE '" + Name + «'

SELECT ID FROM Criterias Where [Parent] = '" + ParentID + «'

Добавление записей в таблицу Criterias

программный база данные транзакция

insert into Criterias ([Parent], [Level], [Name]) values ('0','0','" + Name + «')

insert into Criterias ([Parent], [Level], [Name]) values ((select id from criterias where name like '" + Parent + «'),'» + Level + «','» + Name + «')

Запросы на удаление

delete from Orders where FreightCriteriaID =" + Find (Name)

delete from Criterias where [Name] like '" + Name + «'

delete from Orders where FreightCriteriaID IN (SELECT ID FROM Criterias Where Parent LIKE '" + ParentID + «')

delete from Criterias where [Parent] = '" + ParentID + «'

delete from TransportCriterias where [CriteriaID] = '" + ID + «'

delete from Criterias where [ID] = '" + ID + «'

delete from TransportCriterias where [CriteriaID] IN (SELECT ID FROM Criterias Where Parent LIKE '" + ParentID + «')

delete from Criterias where [Parent] = '" + ParentID + «'

delete from Criterias where [ID] = '" + ID + «'

delete from Orders where [ID] = '" + ID. ToString () + «'

delete from TimeTable where [OrderID] = '" + ID. ToString () + «'

Добавление записей в таблицу Orders

insert into orders (Supplier, Customer, Date, FreightCriteriaID, Mass, Destination, Distance) values ('" + os. Supplier + «','» + os. Custumer + «','» + os.Date.ToString () + «',» + os.FreightCriteriaNameID.ToString () + «,» + os.Mass.ToString () + «,'» + os. Destination + «',» + os. Distance + «);

Добавление записей в таблицу Timetable

insert into Timetable (TransportID, OrderID, [Date]) values ((select ID from Transport Where RegNumber = '" + RegNumber + «'),(select MAX (ID) from Orders),'» + Date + «');

Создание хранимой процедуры поиска

USE expert_db;

GO

CREATE PROCEDURE Find @FName varchar (20)

AS

SELECT @FName = '%' + RTRIM (@FName) + '%';

SELECT *

FROM Orders

WHERE Supplier LIKE @FName;

Запуск хранимой процедуры

USE expert_db;

GO

EXEC Find @FName = 'а';

Создание хранимой процедуры проверки существования

use expert_db

go

Create procedure Existance

@Type nvarchar (100)

AS

IF EXISTS (SELECT * FROM Orders WHERE FreightCriteriaID = (select id from Criterias Where Name = @Type))

Print 'Компонент существует'

else

Print 'Компонент не существует'

Запуск хранимой процедуры

use expert_db

go

execute Existance 'Компьютеры'

Создание хранимой процедуры агрегирования

use expert_db

go

create procedure sum_mass_by_supp

@Supp varchar (50)

as

select Supplier, sum (Mass) AS Summ from Orders group by Supplier Having Supplier = @Supp

Запуск хранимой процедуры

use expert_db

go

execute sum_mass_by_supp 'Владос'

Создание распределенной транзакции

use expert_db

go

begin /*distributed*/ transaction;

update Orders set Supplier = 'Izmenen' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Supplier = 'Izmenen' Where ID = '1017';

commit transaction;

Откат транзакции и подтверждение транзакции

use expert_db

go

Declare @Transa varchar (10) = 'Transa1';

begin transaction @Transa;

update Orders set Customer = 'x' Where ID = '1016';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'x' Where ID = '1016';

rollback transaction @Transa;

begin transaction;

update Orders set Customer = 'z' Where ID = '1017';

Update [ASH-PC]. re_ex.dbo.Orders set Customer = 'z' Where ID = '1017';

commit transaction

Взаимоблокировки

— Транзакция 1 — Шаг 1

USE expert_db

BEGIN TRANSACTION

UPDATE Orders SET Mass = '1000' WHERE ID = '1008'

— Транзакция 2 — Шаг 2

USE expert_db

BEGIN TRANSACTION

UPDATE Transport SET MaxDistance = '1000' WHERE ID = '1'

UPDATE Orders SET Distance = '100' WHERE ID = '1008'

COMMIT TRANSACTION

— Транзакция 1 — Шаг 3

UPDATE Transport SET InRepair = '1' WHERE ID = '1'

COMMIT TRANSACTION

Создание индекса

USE expert_db;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test1fg;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test2fg;

GO

ALTER DATABASE expert_db

ADD FILEGROUP test3fg;

GO

— Adds one file for each filegroup.

ALTER DATABASE expert_db

ADD FILE

(

NAME = test1dat1,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt1dat1. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test1fg;

ALTER DATABASE expert_db

ADD FILE

(

NAME = test2dat2,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt2dat2. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test2fg;

GO

ALTER DATABASE expert_db

ADD FILE

(

NAME = test3dat3,

FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQLDATAt3dat3. ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP test3fg;

GO

USE expert_db

CREATE PARTITION FUNCTION fnyears (Date)

AS RANGE LEFT FOR VALUES

('31/12/2005', '31/12/2010');

CREATE PARTITION SCHEME psYears AS PARTITION fnyears TO (test1fg, test2fg, test3fg);

CREATE clustered INDEX Order_ID ON Orders (ID, Date) ON psYearsAll (Date);

Индексированное представление

USE expert_db;

GO

SET NUMERIC_ROUNDABORT OFF;

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;

GO

CREATE VIEW vOrders

WITH SCHEMABINDING

AS

SELECT dbo.Orders.Supplier AS Поставщик, dbo.Orders.Customer AS Покупатель, dbo.Orders.Date AS Дата, dbo.Criterias.Name AS Наименование, dbo.Orders.Mass AS Вес, dbo.Orders.Destination AS Адрес,

dbo.Orders.Distance AS Растояние, dbo.Orders.ID as oID, dbo.Criterias.ID AS cID

FROM dbo. Criterias INNER JOIN

dbo.Orders ON dbo.Criterias.ID = dbo.Orders.FreightCriteriaID

GO

—Create an index on the view.

CREATE UNIQUE CLUSTERED INDEX IDX_V1

ON vOrders (oID, cID);

GO