Практическая работа по БД (4 задания, 6 вариант)

Задание 6.1. Дайте определения следующим понятиям:
1). листовой уровень индекса; корневой уровень индекса;
2). глубина (высота) индекса; порядок (степень) индекса;
3). фактор заполнения индексных страниц (fill-factor).
Задание 6.2. Рассчитайте количество индексных записей (пар «ключ –
ссылка») K на листовом уровне некластеризованного индекса, если известна
мощность индексируемой таблицы n, количество строк кучи в одной
странице m и порядок индекса p (фактор заполнения индексных страниц FF
принять равным 100%):
а) n = 1000, m = 16, p = 512: K = ?
б) n = 10 000, m = 16, p = 512: K = ?
в) n = 100 000, m = 16, p = 512: K = ?
г) n = 1 000 000, m = 16, p = 512: K = ?
д) n = 10 000 000, m = 16, p = 512: K = ?
Задание 6.3. Рассчитайте количество индексных страниц L на листовом
уровне некластеризованного индекса, если известна мощность
индексируемой таблицы n, количество строк кучи в одной странице m и
порядок индекса p (фактор заполнения индексных страниц FF принять
равным 100%):
а) n = 1000, m = 16, p = 512: L = ?
б) n = 10 000, m = 16, p = 512: L = ?
в) n = 100 000, m = 16, p = 512: L = ?
г) n = 1 000 000, m = 16, p = 512: L = ?
д) n = 10 000 000, m = 16, p = 512: L = ?
Задание 6.4. Выведите формулу для расчета количества индексных страниц
на i-том уровне некластеризованного индекса, если известны: количество
индексных страниц на (i+1)-м (вышележащем) уровне, порядок индекса p и
фактор заполнения индексных страниц FF. Экспериментально проверьте
правильность этой формулы.
Задание 6.5. Выведите формулу для расчета количества индексных страниц
на i-том уровне некластеризованного индекса, если известны: количество
индексных страниц на (i-1)-м (нижележащем) уровне, порядок индекса p и
фактор заполнения индексных страниц FF. Экспериментально проверьте
правильность этой формулы.
Задание 6.6. Выведите формулу для расчета количества индексных страниц
на i-том уровне некластеризованного индекса, если известны: количество
индексных страниц на листовом (i=0) уровне, порядок индекса p и фактор
заполнения индексных страниц FF. Экспериментально проверьте
правильность этой формулы.
8
Задание 6.7. Выведите формулу для расчета глубины некластеризованного
индекса H, если известна мощность индексируемой таблицы n, порядок
индекса p и фактор заполнения индексных страниц FF. Экспериментально
проверьте правильность этой формулы (листинг 6.3).
Задание 6.8. Заданы мощность индексируемой таблицы n, порядок индекса
p и фактор заполнения индексных страниц FF. Определите для этих условий
глубину индекса H, а также диапазон изменения мощности индексируемой
таблицы n, в котором глубина индекса будет оставаться равной
рассчитанному значению.
а) n = 1000, p = 512, FF = 100%.
б) n = 1000, p = 512, FF = 75%.
в) n = 1000, p = 512, FF = 50%.
г) n = 10 000, p = 512, FF = 100%.
д) n = 10 000, p = 512, FF = 75%.
е) n = 10 000, p = 512, FF = 50%.
ж) n = 100 000, p = 512, FF =100%.
з) n = 100 000, p = 512, FF = 75%.
и) n = 100 000, p = 512, FF = 50%.
к) n = 1 000 000, p = 512, FF =100%.
л) n = 1 000 000, p = 512, FF = 75%.
м) n = 1 000 000, p = 512, FF = 50%.
Задание 6.9. Дайте определения следующим типам индексов, определите
области применения и приведите примеры SQL-кода для их создания:
а) кластеризованный индекс;
б) некластеризованный индекс;
в) уникальный/неуникальный индекс;
г) композитный индекс;
д) индекс с включенными столбцами.
Задание 6.10. Проведите эксперименты по исследованию структуры
индексных страниц листового, корневого и промежуточных уровней для
индексов следующих типов:
а) кластеризованный индекс;
б) некластеризованный индекс при отсутствии кластеризованного;
в) некластеризованный индекс при наличии кластеризованного;
г) некластеризованный индекс с включенными столбцами;
д) некластеризованный уникальный индекс.
Задание 6.11. Опишите (и подтвердите экспериментально) алгоритм поиска
строк таблицы Tabl: SELECT * FROM Tabl WHERE IndKey = const, если:
а) по столбцу IndKey сформирован некластеризованный индекс, и
других индексов в этой таблице нет;
б) по столбцу IndKey сформирован некластеризованный индекс, а по
столбцу PrmKey сформирован кластеризованный индекс.
9
Задание 6.12. Опишите (и подтвердите экспериментально) алгоритм поиска
строк таблицы Tabl: SELECT * FROM Tabl WHERE IndKey > const, если:
а) по столбцу IndKey сформирован некластеризованный индекс, и
других индексов в этой таблице нет;
б) по столбцу IndKey сформирован некластеризованный индекс, а по
столбцу PrmKey сформирован кластеризованный индекс.
Задание 6.13. Опишите (и подтвердите экспериментально) алгоритм поиска
строк таблицы Tabl: SELECT * FROM Tabl WHERE IndKey < const, если:
а) по столбцу IndKey сформирован некластеризованный индекс, и
других индексов в этой таблице нет;
б) по столбцу IndKey сформирован некластеризованный индекс, а по
столбцу PrmKey сформирован кластеризованный индекс.
Задание 6.14. Опишите (и подтвердите экспериментально) алгоритм поиска
строк таблицы Tabl: SELECT * FROM Tabl WHERE IndKey Between const1 AND
const2, если:
а) по столбцу IndKey сформирован некластеризованный индекс, и
других индексов в этой таблице нет;
б) по столбцу IndKey сформирован некластеризованный индекс, а по
столбцу PrmKey сформирован кластеризованный индекс.

Задание 7.1. Определите назначение модуля лексического преобразования
исходного SQL-кода:
а) Какие задачи решает этот модуль в процессе трансляции SQLзапроса?
б) Использует ли этот модуль информацию о текущем состоянии базы
данных? Если да, то с какими целями?
в) Какие компоненты системного каталога базы данных использует
этот модуль и с какими целями?
г) Становится ли процедурным представление исходного SQL-кода,
сформированное этим модулем?
Задание 7.2. Определите назначение модуля логического преобразования
исходного SQL-кода:
а) Какие задачи решает этот модуль в процессе трансляции SQLзапроса?
б) Использует ли этот модуль информацию о текущем состоянии базы
данных? Если да, то с какими целями?
в) Какие компоненты системного каталога использует этот модуль и с
какими целями?
г) Становится ли процедурным представление исходного SQL-кода,
сформированное этим модулем?
Задание 7.3. Предложите варианты эквивалентных логических
(синтаксических) преобразований следующих SQL-запросов с целью
исключения подчиненных запросов, использованных в исходном коде, и
экспериментально подтвердите эквивалентность таких преобразований:
а) SELECT R1.a FROM R1
WHERE R1.b IN (SELECT R2.d FROM R2 WHERE R2.e = R1.c)
б) SELECT R1.a FROM R1
WHERE R1.b IN (SELECT R2.d FROM R2 WHERE R2.e > R1.c)
в) SELECT R1.a FROM R1
WHERE R1.b IN (SELECT R2.d FROM R2 WHERE R2.e < R1.c)
г) SELECT R1.a FROM R1
WHERE R1.b = (SELECT DISTINCT R2.d FROM R2)
д) SELECT R1.a FROM R1
WHERE R1.c = (SELECT DISTINCT R2.e FROM R2)
11
Задание 7.4. Рассмотрите следующие SQL-запросы, заданные на
представлениях, и предложите процедурные планы их реализации.
Предложите варианты эквивалентных логических (синтаксических)
преобразований с целью объединения кода запроса с кодом представления, и
экспериментально подтвердите эквивалентность таких преобразований:
а) CREATE View V(A1, B1, C1) AS
SELECT T.A, T.B, T.C From T Where T.C > 6;
SELECT * From V Where V.C1 < 6;
б) CREATE View V(A1, B1, C1) AS
Select T.A, T.B, T.C From T Where T.C > 7 AND T.A > T.B;
SELECT * From V Where V.C1 < 7 OR V.A1 < V.B1;
в) CREATE View V(A1, B1, C1) AS
SELECT T.A, T.B, T.C From T Where T.C < 8 OR T.A < T.B;
SELECT * From V Where V.C1 > 8 AND V.A1 > V.B1;
г) CREATE View V(A1, B1, C1) AS
Select T.A, T.B, T.C From T Where T.A < T.B;
SELECT * From V Where V.C1 > 8 AND V.A1 > V.B1;
Задание 7.5. Предложите (и обоснуйте) версию процедурного плана
реализации SQL-запроса SELECT * FROM Table WHERE Table.dat = const для
следующих состояний базы данных и оцените его стоимость (в модели
стоимости плана за единицу принять операцию чтения одной файловой
страницы, глубину индексов рассчитать приблизительно):
а) Строки таблицы занимают в файле БД 10 страниц, организованных
в кучу; индексы по столбцам таблицы отсутствуют.
б) Строки таблицы занимают в файле БД 100 000 страниц,
организованных в кучу; индексы по столбцам таблицы отсутствуют.
в) Строки таблицы занимают в файле БД 10 страниц, организованных
в кучу; по столбцу Table.dat сформирован некластеризованный
неуникальный индекс.
г) Строки таблицы занимают в файле БД 100 000 страниц,
организованных в кучу; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно актуальной
статистике, степень селективности предиката Table.dat = const
составляет 10%.
д) Строки таблицы занимают в файле БД 100 000 страниц,
организованных в кучу; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно актуальной
статистике, степень селективности предиката Table.dat = const
составляет 90%.
12
е) Строки таблицы занимают в файле БД 10 страниц; по ключевому
столбцу Table.Key сформирован кластеризованный уникальный
индекс, индексы по остальным столбцам таблицы отсутствуют.
ж) Строки таблицы занимают в файле БД 100 000 страниц; по
ключевому столбцу Table.Key сформирован кластеризованный
уникальный индекс, индексы по остальным столбцам таблицы
отсутствуют.
з) Строки таблицы занимают в файле БД 100 000 страниц; по
ключевому столбцу Table.Key сформирован кластеризованный
уникальный индекс, по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно актуальной
статистике, степень селективности предиката Table.dat = const
составляет 90%.
Задание 7.6. Предложите (и обоснуйте) версию процедурного плана
реализации SQL-запроса SELECT * FROM Table WHERE Table.dat > const для
следующих состояний базы данных и оцените его стоимость (в модели
стоимости плана за единицу принять операцию чтения одной файловой
страницы, глубину индексов рассчитать приблизительно):
а) Строки таблицы Table занимают в файле БД 10 страниц,
организованных в кучу; индексы по столбцам таблицы отсутствуют.
б) Строки таблицы Table занимают в файле БД 100 000 страниц,
организованных в кучу; индексы по столбцам таблицы отсутствуют.
в) Строки таблицы Table занимают в файле БД 10 страниц,
организованных в кучу; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс.
г) Строки таблицы Table занимают в файле БД 100 000 страниц,
организованных в кучу; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно статистике,
степень селективности предиката Table.dat > const составляет 10%.
д) Строки таблицы Table занимают в файле БД 100 000 страниц,
организованных в кучу; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно статистике,
степень селективности предиката Table.dat > const составляет 90%.
е) Строки таблицы Table занимают в файле БД 10 страниц; по
ключевому столбцу Table.Key сформирован кластеризованный
уникальный индекс, индексы по остальным столбцам таблицы
отсутствуют.
ж) Строки таблицы Table занимают в файле БД 100 000 страниц; по
ключевому столбцу Table.Key сформирован кластеризованный
уникальный индекс; индексы по остальным столбцам таблицы
отсутствуют.
13
з) Строки таблицы Table занимают в файле БД 100 000 страниц; по
ключевому столбцу Table.Key сформирован кластеризованный
уникальный индек; по столбцу Table.dat сформирован
некластеризованный неуникальный индекс; согласно статистике,
степень селективности предиката Table.dat > const составляет 90%.
Задание 7.7. Дайте определение объекта статистики (для СУБД MS SQL
Server) и опишите его структуру.
а) Для каких объектов БД возможно создание объектов статистики?
б) Что называют целевым элементом объекта статистики?
в) Опишите структуру гистограммы распределения значений целевого
элемента объекта статистики.
г) На каких этапах трансляции SQL-запросов и для решения каких
задач используются объекты статистики?
д) Предложите алгоритм формирования объекта статистики.
Задание 7.8. Создайте таблицу базы данных, сформируйте
некластеризованные неуникальные индексы по трем ее столбцам и вставьте
в таблицу 10 000 строк со случайными значениями полей. Исследуйте
объекты статистики, сформированные для целевых элементов этой таблицы:
а) Используя средства обозревателя объектов MS SQL Server
Management Studio, определите:
 состав объектов статистики, сформированных для целевых
элементов таблицы Table, и основные мета-данные каждого из
объектов, в том числе – количество интервалов гистограммы
распределения значений;
 количество строк, значение столбцов которых находится в
пределах одного из интервалов гистограммы одного из
объектов статистики (включая верхнюю границу интервала).
б) Используя команду DBCC SHOWSTATISTICS, определите:
 состав объектов статистики, сформированных для целевых
элементов таблицы Table, и основные мета-данные каждого из
объектов, в том числе – количество интервалов гистограммы
распределения значений;
 ширину первого и последнего интервалов гистограммы
распределения значений целевого элемента одного из объектов
статистики и частоты распределения значений целевого
элемента для этих интервалов (включая верхнюю границу
интервала).
в) Используя системное представление sys.stats и TVF-функцию
sys.dm_db_stats_histogram(), определите для одного из объектов
статистики:
 количество интервалов гистограммы распределения значений
целевого элемента;
14
 интервалы гистограммы с максимальным и минимальным
значениями частот распределения значений целевого
элемента;
 предполагаемое значение степени селективности предиката
выборки Key BETWEEN a AND b, где Key – целевой элемент
объекта статистики, a и b задают диапазон значений Key
(диапазон a … b должен охватывать несколько соседних
интервалов гистограммы распределения значений Key).
г) Напишите на TransactSQL хранимую процедуру, формирующую
объект статистики для одного из индексов таблицы Table.
Задание 7.9. Анализ процедурных планов выполнения SQL-запросов
выборки данных из «кучи».
 Создайте таблицу Table, не создавая в этой таблице индексов,
вставьте в нее порядка 10 000 строк со случайными значениями полей.
 Определите количество файловых страниц, занятых строками этой
таблицы (используя, например, процедуру sp_spaceused).
а). Выполните SQL-запрос выборки всех строк таблицы Table, оцените
мощность таблицы и сравните ее с результатами выполнения процедуры
sp_spaceused.
б). Включите режим графического отображения процедурного плана
запроса, повторно выполните запрос и определите:
 состав используемых процедурных операторов;
 стоимость выполнения каждого из них;
 стоимость операций ввода-вывода;
 стоимость операций обработки данных;
 суммарную стоимость выполнения процедурного плана.
в). Дополните пакет выполнения предыдущего запроса командами:
SET STATISTICS XML ON,
SET STATISTICS PROFILE ON,
SET STATISTICS IO ON,
SET STATISTICS TIME ON.
Включите режим графического отображения фактического плана,
повторно выполните запрос и сравните предполагаемый и фактический
планы.
г) Проведите сравнительный анализ предполагаемого и фактического
процедурных планов следующих запросов выборки данных из кучи:
1. Select * From Table Where Key_1=555;
2. Select * From Table Where Key_2>666;
3. Select Data From Table Where Key_1>20 And Key_2<100
4. Select * From Table Order By Data
5. Select Key_1, Count(*) From Table Group By Key_1
15
Задание 7.10. Анализ процедурных планов выполнения SQL-запросов
выборки данных из индексированных таблиц (сформированы
некластеризованные индексы по поисковым полям (ключам поиска) в
условиях отсутствия кластеризованного индекса по первичному ключу).
Создайте таблицу Table и сформируйте в этой таблице
некластеризованные неуникальные индексы по столбцам
Key_1 и Key_2.
а) Вставьте в таблицу 100 строк:
 убедитесь в том, что для таблицы сформирована «куча»,
определите количество занятых «кучей» файловых страниц;
 определите глубину сформированных индексов и количество
индексных страниц на каждом их уровне;
 выполните задание 7.10, б) для таблицы в ее текущем состоянии.
б) Вставьте в таблицу еще 100 000 строк:
 определите количество занятых «кучей» файловых страниц;
 определите глубину сформированных индексов и количество
индексных страниц на каждом их уровне;
 выполните задания 7.10: б) и в) для таблицы текущем состоянии.
в) Проведите сравнительный анализ предполагаемого и фактического
процедурных планов следующих запросов выборки данных из кучи:
1. Select * From Table Where Key_1=555;
2. Select * From Table Where Key_2>666;
3. Select Data From Table Where Key_1>20 And Key_2<100;
4. Select * From Table Order By Data;
5. Select Key_1, Count(*) From Table Group By Key_1;
Задание 7.11. Анализ процедурных планов выполнения SQL-запросов
выборки данных из индексированных таблиц (сформированы
некластеризованные индексы по поисковым полям (ключам поиска) в
условиях наличия кластеризованного индекса по первичному ключу).
Создайте таблицу Table с первичным ключом Key_0
и сформируйте в этой таблице некластеризованные неуникальные
индексы по столбцам Key_1 и Key_2:
CREATE TABLE Table (
Key_0 INT NOT NULL IDENTITY CONSTRAINT Key0_PK PRIMARY KEY,
Key_1 INT NOT NULL,
Key_2 INT NOT NULL,
Data CHAR(61) NOT NULL)
а) Вставьте в таблицу 100 000 строк, заполнив случайными значениями
поля Key_1, Key_2 и Data:
16
 убедитесь в отсутствии «кучи» и наличии кластеризованного
индекса по столбцу Key_0;
 определите глубину сформированных индексов и количество
индексных страниц на каждом их уровне;
 выполните задания 7.10: б) и в) для таблицы текущем состоянии.
б) Проведите сравнительный анализ предполагаемого и фактического
процедурных планов следующих запросов выборки данных:
1. Select * From Table Where Key_1=555;
2. Select * From Table Where Key_2>666;
3. Select Data From Table Where Key_1>20 And Key_2<100
4. Select * From Table Order By Data
5. Select Key_1, Count(*) From Table Group By Key_1
Задание 7.12. Анализ процедурных планов выполнения SQL-запросов с
соединением (JOIN) неиндексированных таблиц.
 Создайте две таблицы BigTable и SmallTable.
 Не создавая в этих таблицах индексов, вставьте в одну из них 100 000
строк, а в другую – 500 строк со случайными значениями полей.
 Определите количество файловых страниц, занятых строками
каждой таблицы (используя, например, процедуру sp_spaceused).
 Проанализируйте процедурные планы выполнения следующих SQLзапросов:
а) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_0 = SmallTable.Key_1;
б) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_0;
в) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_2;
г) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_2 = SmallTable.Key_1;
д) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_0 = SmallTable.Key_1;
е) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_0;
17
ж) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_2;
з) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_2 = SmallTable.Key_1;
Задание 7.13. Анализ процедурных планов выполнения SQL-запросов с
соединением (JOIN) индексированных таблиц.
 Создайте две таблицы BigTable и SmallTable, сформируйте индексы по
столбцам этих таблиц (см. задание 7.10).
 Вставьте в одну из таблиц 100 000 строк, а в другую – 500 строк со
случайными значениями полей.
 Определите количество файловых страниц, занятых строками каждой
таблицы (используя, например, процедуру sp_spaceused).
 Проанализируйте процедурные планы выполнения SQL-запросов:
а) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_0 = SmallTable.Key_1;
б) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_0;
в) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_2;
г) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable INNER JOIN SmallTable ON
BigTable.Key_2 = SmallTable.Key_1;
д) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_0 = SmallTable.Key_1;
е) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_0;
ж) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_1 = SmallTable.Key_2;
з) SELECT BigTable.Key_2, BigTable.Data, SmallTable.Data
FROM BigTable LEFT JOIN SmallTable ON
BigTable.Key_2 = SmallTable.Key_1;

Задание 8.1. Анализ обновлений (UPDATE), проводимых в таблице двумя
конкурирующими транзакциями, которые успешно завершились и были
зафиксированы (COMMIT).
− в сессии А: создать таблицу соответствующей структуры, заполнить ее
определенным набором строк и наблюдать за изменениями состояний
очереди транзакций и журнала блокировок в результате выполнения
команд обновления строк этой таблицы (в транзакциях сессий Б и В);
− в сессии Б: начать транзакцию.
− в сессии В: начать транзакцию.
− в сессии Б: выполнить запрос обновления данных в одном столбце
одной строки таблицы.
− в сессии В: выполнить запрос обновления данных в этом же столбце
этой же строки таблицы.
− в сессии Б: зафиксировать транзакцию.
− в сессии В: зафиксировать транзакцию.
− провести анализ процессов наложения и снятия блокировок (уровни и
режимы блокирования объектов БД, типы накладываемых блокировок,
моменты снятия блокировок и время ожидания освобождения
заблокированных ресурсов);
 сохраните в отчете результаты проведенного эксперимента;
 сделайте выводы и ответьте на следующие вопросы:
 что означают режимы блокирования S, X и IX ?
 какая из блокировок (рисунок 8.7) находится в статусе
ожидания, какой режим блокирования у этой блокировки, и чего она
«ожидает»?
 в какой момент фиксируются изменения, внесенные
транзакциями – сразу после выполнения команды UPDATE в этой
транзакции или только после ее фиксации (COMMIT)?
Задание 8.2. Анализ обновлений (UPDATE), проводимых в таблице двумя
конкурирующими транзакциями, одна из которых не была зафиксирована,
и был выполнен ее принудительный откат (ROLLBACK).
− Проведите эксперимент, аналогичный заданию 8.1, только вместо
фиксации 1-й транзакции в сессии Б выполните ее откат (ROLLBACK).
21
Задание 8.3. Анализ обновлений (UPDATE), проводимых конкурирующими
транзакциями в различных строках индексированных и
неиндексированных таблиц.
Для каждого варианта задания 8.3 потребуется создать три сессии:
− в сессии А: создать таблицу соответствующей структуры, заполнить
ее определенным набором строк и наблюдать за изменениями
состояний очереди транзакций и журнала блокировок в результате
выполнения команд обновления строк этой таблицы (в транзакциях
сессий Б и В, подобно тому, как это сделано в рассмотренном выше
примере);
− в сессиях Б и В: начать по одной транзакции, в каждой транзакции
выполнить запрос обновления соответствующих строк таблицы;
− провести анализ процессов наложения и снятия блокировок (уровни
и режимы блокирования объектов БД, типы накладываемых
блокировок, моменты снятия блокировок и время ожидания
освобождения заблокированных ресурсов);
− сформировать отчет, в котором привести иллюстрации
выполненного эксперимента и сделать выводы по результатам
проведенного анализа.
Вариант 1). Неиндексированная таблица. Обновление двух различных
строк двумя транзакциями.
1. Создаем таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3]([ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL, [ID_TYPE] [int] NULL);
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID = 1.
4. Во второй транзакции обновляем строку с ID = 5.
Вариант 2). Неиндексированная таблица. Первая транзакция обновляет
диапазон строк, а вторая транзакция обновляет строку,
входящую в диапазон строк, обновляемых первой
транзакцией.
1. Создаем таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3]([ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL,[ID_TYPE] [int] NULL);
22
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID in (1, 2, 5).
4. Во второй транзакции обновляем строку с ID = 5.
Вариант 3). Неиндексированная таблица. Первая транзакция обновляет
строку, входящую в диапазон строк, обновляемых второй
транзакцией.
1. Создаем таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3]([ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL, [ID_TYPE] [int] NULL);
2. Вставляем в таблицу 50 строк.
3. В первой транзакции обновляем строку с ID = 5.
4. Во второй транзакции обновляем строку с ID in (1, 2, 5).
Вариант 4). Неиндексированная таблица. Пересекающиеся диапазоны
строк, обновляемых двумя транзакциями.
1. Создаем таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3]([ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL,[ID_TYPE] [int] NULL);
2. Вставляем в таблицу 20 строк.
3. В первой транзакции обновляем строки с ID between 1 and 10.
4. Во второй транзакции обновляем строки с ID between 8 and 16.
Вариант 5). Индексированная кластеризованная таблица. Выборка по
первичному ключу. Обновление двух различных строк двумя
транзакциями.
1. Создаем кластеризованную таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3](ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL, [ID_TYPE] [int] NULL
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]);
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID = 1.
4. Во второй транзакции обновляем строку с ID = 5.
23
Вариант 6). Индексированная кластеризованная таблица. Выборка по
первичному ключу. Первая транзакция обновляет диапазон
строк, а вторая транзакция обновляет строку, входящую в
диапазон строк, обновляемых первой транзакцией.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID = 5.
4. Во второй транзакции обновляем строку с ID in (1, 2, 5).
Вариант 7). Индексированная кластеризованная таблица. Выборка по
первичному ключу. Первая транзакция обновляет строку,
входящую в диапазон строк, обновляемых второй
транзакцией.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID = 1.
4. Во второй транзакции обновляем строку с ID in (5, 2, 1).
Вариант 8). Индексированная кластеризованная таблица. Выборка по
первичному ключу. Пересекающиеся диапазоны строк,
обновляемых двумя транзакциями.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 10 строк.
3. В первой транзакции обновляем строку с ID between 1 and 10.
4. Во второй транзакции обновляем строку с ID between 8 and 16.
Вариант 9). Индексированная кластеризованная таблица. Обе транзакции
обновляют одну строку таблицы: первая транзакция выбирает
строку по значению первичного ключа, а вторая – по
значению неиндексированного вторичного.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 3 строки:
INSERT INTO [dbo].[T_TEST_3](C_NAME, ID_TYPE)
VALUES ('SOME', 10), ('ELSE', 12), ('SOMETHING', 15);
3. В первой транзакции обновляем строку с ID = 1
4. Во второй транзакции обновляем строку с C_NAME = 'SOME’
24
Вариант 10). Индексированная кластеризованная таблица. Обе
транзакции обновляют одну строку таблицы: первая
транзакция выбирает строку по значению
неиндексированного вторичного ключа, а вторая – по
значению первичного.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 3 строки (см. Вариант 9).
3. В первой транзакции обновляем строку с C_NAME = 'SOME’.
4. Во второй транзакции обновляем строку с ID = 1.
Вариант 11). Индексированная кластеризованная таблица. Две
транзакции обновляют две разных строки таблицы: первая
транзакция выбирает строку по значению
неиндексированного вторичного ключа, а вторая – по
значению первичного.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 3 строки (см. Вариант 9).
3. В первой транзакции обновляем строку с C_NAME = ‘SOME'.
4. Во второй транзакции обновляем строку с ID = 3.
Вариант 12). Индексированная кластеризованная таблица. Первая
транзакция обновляет две строки, выбранные по значениям
неиндексированного вторичного ключа, а вторая – одну из
этих двух строк, выбранную по значению первичного ключа.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 3 строки (см. Вариант 9).
3. В первой транзакции обновляем строку с C_NAME LIKE 'SOME%’.
4. Во второй транзакции обновляем строку с ID = 3.
Вариант 13). Индексированная кластеризованная таблица. Выборка по
первичному ключу. Первая транзакция обновляет одну
строку, не входящую в диапазон строк, обновляемых второй
транзакцией.
1. Создаем таблицу (см. Вариант 5)
2. Вставляем в таблицу 3 строки (см. Вариант 9).
3. В первой транзакции обновляем строку с ID = 1.
4. Во второй транзакции обновляем строку с ID > 1.
25
Вариант 14). Индексированная кластеризованная таблица. Две
транзакции обновляют две разных строки таблицы: первая
транзакция выбирает строку по значению первичного ключа,
а вторая – по значению индексированного
некластеризованного вторичного.
1. Создаем таблицу, например, так:
CREATE TABLE [dbo].[T_TEST_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL,
[ID_TYPE] [int] NULL
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY] );
CREATE NONCLUSTERED INDEX [IX_T_TEST_3_ID_TYPE]
ON [dbo].[T_TEST_3] (ID_TYPE] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];
2. Вставляем в таблицу 4 строки:
INSERT INTO [dbo].[T_TEST_3](C_NAME, ID_TYPE)
VALUES ('SOME', 10), ('ELSE', 12), ('SOMETHING', 15), ('META', 10);
3. В первой транзакции обновляем строку с ID = 1.
4. Во второй транзакции обновляем строку с ID_TYPE = 12.
Вариант 15). Индексированная кластеризованная таблица. Обе
транзакции обновляют одну строку таблицы: первая
транзакция выбирает строку по значению первичного ключа,
а вторая – по значению индексированного
некластеризованного вторичного.
1. Создаем таблицу и вставляем в нее 4 строки (см. Вариант 14)
2. В первой транзакции обновляем 1-ю строку с ID = 1.
3. Во второй транзакции обновляем 1-ю и 4-ю строки с ID_TYPE = 10.
26
Вариант 16). Индексированная кластеризованная таблица. Обе
транзакции обновляют одну строку таблицы: первая
транзакция выбирает строку по значению индексированного
некластеризованного вторичного ключа, а вторая – по
значению первичного.
1. Создаем таблицу и вставляем в нее 4 строки (см. Вариант 14)
2. В первой транзакции обновляем 1-ю и 4-ю строки с ID_TYPE = 10.
3. Во второй транзакции обновляем 1-ю строку с ID = 1.
Задание 8.4. Анализ обновлений (UPDATE), проводимых конкурирующими
транзакциями в условиях возникновения взаимоблокировок (Dead Locks).
Для каждого варианта (кроме 10-го) потребуется создать три сессии:
− в сессии А: создать таблицу соответствующей структуры, заполнить ее
определенным набором строк и наблюдать за изменениями состояний
очереди транзакций и журнала блокировок;
− в сессиях Б и В: начать по одной транзакции, в каждой транзакции
обновить по одной (различной) строке таблицы, транзакции НЕ
фиксировать;
− в сессии В: обновить одну (любую другую) строку таблицы, транзакцию
НЕ фиксировать;
− в сессии Б: обновить ту же строку, которая обновлялась в сессии В,
транзакцию НЕ фиксировать;
− в сессии В: обновить ту же строку, которая обновлялась в сессии Б,
транзакцию НЕ фиксировать;
− провести анализ процессов наложения и снятия блокировок (уровни и
режимы блокирования объектов БД, типы накладываемых блокировок,
моменты снятия блокировок и время ожидания освобождения
заблокированных ресурсов;
− сформировать отчет, в котором привести иллюстрации выполненного
эксперимента и сделать выводы по результатам проведенного анализа.
Вариант 1). Индексированная кластеризованная таблица. Обновление
пяти различных строк двумя транзакциями. Выборка строк по
первичному ключу.
1. Создаем таблицу (см. Задание 8.3, Вариант 5), вставляем 10 строк.
2. Сессия Б: обновляем строку с ID = 1, транзакцию не фиксируем.
3. Сессия В: обновляем строку с ID = 2, транзакцию не фиксируем.
4. Сессия Б: обновляем строку с ID = 3, транзакцию не фиксируем.
5. Сессия В: обновляем строку с ID = 4, транзакцию не фиксируем.
6. Сессия Б: обновляем строку с ID = 5, транзакцию не фиксируем.
27
Вариант 2). Индексированная кластеризованная таблица. Обновление
совпадающих строк двумя транзакциями. Выборка строк по
первичному ключу.
1. Создаем таблицу (см. Задание 8.3, Вариант 5), вставляем 10 строк.
2. Сессия Б: обновляем строку с ID = 1, транзакцию не фиксируем.
3. Сессия В: обновляем строку с ID = 2, транзакцию не фиксируем.
4. Сессия Б: обновляем строку с ID = 3, транзакцию не фиксируем.
5. Сессия В: обновляем строку с ID = 1, транзакцию не фиксируем.
6. Сессия Б: обновляем строку с ID = 2, транзакцию не фиксируем.
Вариант 3). Индексированная кластеризованная таблица. Обновление
перекрывающихся множеств строк двумя транзакциями.
Выборка строк по первичному ключу.
1. Создаем таблицу (см. Задание 8.3, Вариант 5), вставляем 10 строк.
2. Сессия Б: обновляем строки с ID in (1, 2, 3), не фиксируем.
3. Сессия В: обновляем строку с ID in (4, 5, 6), не фиксируем.
4. Сессия Б: обновляем строку с ID in (8, 7, 6), не фиксируем.
5. Сессия В: обновляем строку с ID in (9, 2, 4), не фиксируем.
Вариант 4). Индексированная кластеризованная таблица. Обновление
перекрывающихся диапазонов строк двумя транзакциями.
Выборка строк по первичному ключу.
1. Создаем таблицу (см. Задание 8.3, Вариант 5), вставляем 10 строк.
2. Сессия Б: обновляем строки с ID < 3, не фиксируем.
3. Сессия В: обновляем строку с ID BETWEEN 2 AND 6, не фиксируем.
4. Сессия Б: обновляем строку с ID > 6, не фиксируем.
5. Сессия В: обновляем строку с ID = 6, не фиксируем.
Вариант 5). Индексированная кластеризованная таблица. Обновление
перекрывающихся диапазонов строк двумя транзакциями.
Первая транзакция выбирает строки по первичному ключу,
вторая – по вторичному.
1. Создаем таблицу (см. Задание 8.3, Вариант 5).
2. Вставляем в таблицу 3 строки:
INSERT INTO [dbo].[T_TEST_4](C_NAME, ID_TYPE)
VALUES ('FIRST', 10), ('ELSE', 12), ('SOMETHING', 15);
3. Сессия Б: обновляем строки с ID = 3, транзакцию не фиксируем.
4. Сессия В: обновляем строку с C_NAME = 'FIRST', не фиксируем.
5. Сессия Б: обновляем строку с ID = 1, транзакцию не фиксируем.
28
Вариант 6). Индексированная кластеризованная таблица. Обновление
перекрывающихся диапазонов строк двумя транзакциями.
Первая транзакция выбирает строки по вторичному ключу,
вторая – по первичному.
1. Создаем таблицу (см. Задание 8.3, Вариант 5).
2. Вставляем в таблицу 3 строки (см. Вариант 5).
3. Сессия В: обновляем строку с C_NAME = 'FIRST', не фиксируем.
4. Сессия Б: обновляем строки с ID = 3, транзакцию не фиксируем.
5. Сессия Б: обновляем строку с ID = 1, транзакцию не фиксируем.
Вариант 7). Индексированная кластеризованная таблица. Обновление
перекрывающихся диапазонов строк двумя транзакциями.
Выборка строк по различным полям таблицы.
1. Создаем таблицу (см. Задание 8.3, Вариант 5).
2. Вставляем в таблицу 3 строки (см. Вариант 5):
3. Сессия Б: обновляем строки с ID_TYPE = 3, не фиксируем.
4. Сессия В: обновляем строку с C_NAME = 'SOMETHING', транзакцию
не фиксируем.
5. Сессия Б: обновляем строку с ID = 3, транзакцию не фиксируем.
Вариант 8). Индексированная кластеризованная таблица. Обновление
различных строк двумя транзакциями. Выборка строк по
различным полям таблицы.
1. Создаем таблицу (см. Задание 8.3, Вариант 5).
2. Вставляем в таблицу 3 строки (см. Вариант 5).
3. Сессия Б: обновляем строки с ID = 3, не фиксируем.
4. Сессия В: обновляем строку с C_NAME = 'FIRST', не фиксируем.
5. Сессия Б: обновляем строку с ID_TYPE = 12, не фиксируем.
Вариант 9). Индексированная кластеризованная таблица с неуникальным
некластеризованным индексом. Обновление совпадающих
строк двумя транзакциями: первая выбирает строки по
первичному ключу, вторая – по неуникальному вторичному.
1. Создаем таблицу и некластеризованный индекс (см. Задание 8.3,
Вариант 14).
2. Вставляем в таблицу 7 строк:
INSERT INTO [dbo].[T_TEST_4](C_NAME, ID_TYPE)
VALUES ('TEST_1', 10), ('TEST_2', 10), ('TEST_3', 15), ('TEST_4', 15),
('TEST_3', 20), ('TEST_2', 20), ('TEST_1', 10);
3. Сессия Б: обновляем строки с ID = 1, транзакцию не фиксируем.
29
4. Сессия В: обновляем строку с ID_TYPE = 15, не фиксируем.
5. Сессия Б: обновляем строку с ID = 4, не фиксируем.
6. Сессия В: обновляем строку с ID_TYPE = 10, не фиксируем.
Вариант 10). Индексированная кластеризованная таблица с
неуникальным некластеризованным индексом. Обновление
совпадающих строк ТРЕМЯ транзакциями с выборкой строк
по первичному ключу.
Для выполнения 10-го варианта потребуется дополнительная четвертая
сессия Г, в которой будет выполняться третья транзакция, конкурирующая с
первыми двумя, выполняемыми в сессиях Б и В.
1. Создаем таблицу и некластеризованный индекс (см. Задание 8.3,
Вариант 14).
2. Вставляем в таблицу 7 строк (см. Вариант 9).
3. Сессия Б: обновляем строки с ID = 1, транзакцию не фиксируем.
4. Сессия В: обновляем строку с ID = 3, транзакцию не фиксируем.
5. Сессия Г: обновляем строку с ID = 5, транзакцию не фиксируем.
6. Сессия Б: обновляем строку с ID in (3, 5), транзакцию не фиксируем.
7. Сессия В: обновляем строку с ID = 5, транзакцию не фиксируем.
8. Сессия Г: фиксируем (COMMIT) транзакцию.
Задание 8.5. Анализ процесса эскалации блокировок при массовом
обновлении (UPDATE) строк таблицы.
Для выполнения задания рекомендуется создать две сессии:
1. Сессия А: создаем кластеризованную таблицу
CREATE TABLE [dbo].[T_TEST_5](
[ID] [int] IDENTITY(1,1) NOT NULL,
[C_NAME] [nvarchar](100) NULL,
[ID_TYPE] [int] NULL
PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]);
2. Сессия А: вставляем в таблицу 50 000 строк.
3. Сессия Б: начинаем транзакцию.
30
4. Сессия Б: обновляем (пакетно) 10 строк таблицы с выборкой строк
по диапазону значений первичного ключа ID.
5. Сессия А: просматриваем очередь транзакций и журнал блокировок,
сохраняем в отчете количество пользовательских транзакций,
количество наложенных блокировок, режимы блокирования и
уровни блокирования ресурсов.
6. Повторяем пункты 4 и 5 до момента эскалации блокировки – то есть
до тех пор, пока уровень блокируемого ресурса не поднимется от
строки таблицы (ROW) или ключа индекса (KEY) до уровня таблицы
(OBJECT).
7. Сохраняем в отчете количество пакетов и количество обновляемых
строк, при котором произошла эскалация блокировки.
8. Трижды повторяем пункты с 4-го по 7-й, каждый раз увеличивая
размер пакетов обновления (соответственно, по 100, 1000 и 10 000
строк в пакете).
9. Сессия Б: определяем количество строк в таблице с ID_TYPE = 9999 и
обновляем все такие строки.
10. Сессия А: просматриваем очередь транзакций и журнал блокировок,
сохраняем в отчете информацию о результатах выполнения п. 9.
Задание 8.6. Анализ процессов наложения и снятия блокировок с
намерениями (INTENT LOCK).
Для каждого из вариантов этого задания потребуется создать три сессии: