Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа №2
Блокировки DML - табличные и строчные.
Цель работы: познакомиться с одной из главных функций СУБД ORACLE 10g – механизмом блокирования (высокий уровень одновременной обработки данных).
Программные средства: Система управления базами данных ORACLE 10g, интерактивная среда PL/SQL Developer. На начальном этапе достаточно блокнота, встроенного в операционную систему Windows и среды SQL*Plus.
2.1 Краткая теория
Сервер ORACLE автоматически управляет блокированием. Механизм блокирования по умолчанию устанавливает блокировку на самом низком уровне ограничений доступа, чтобы обеспечить целостность данных при предоставлении высокой степени конкурентного доступа, то есть возможность безопасного и одновременного доступа к одним и тем же данным для многих пользователей. Блокировки DML применяются для блокирования таких объектов, как таблицы, строки таблиц.
Основное назначение блокировок DML: заблокировать объект на время выполнения над ним операции DML (INSERT или DELETE или UPDATE) до момента фиксации результата выполнения операции DML - commit. При выполнении операции SELECT СУБД блокирование объектов не производит. Если происходит аварийное прерывание транзакции, процесс PIMON освобождает блокировки.
Режимы блокировки:
Например, при параллельном выполнении двух транзакций накладываются монопольные блокировки на уровне строк.
|
ТРАНЗАКЦИЯ 1 |
ТРАНЗАКЦИЯ 2 |
|
SQL> UPDATE employee 2 SET salapy=salary*1.1 3 WHERE id= 24877; 1 row updated. SQL> commit; Commit complete. |
SQL> UPDATE employee 2 SET salapy=salary*1.1 3 WHERE id= 24877; Transaction 2 waits. Транзакция 2 ждет , пока не зафиксируется транзакция 1. 1 row updated. |
Например, для транзакций установлены разделяемые блокировки на уровне таблицы. Две транзакции изменяют различные строки одной и той же таблицы.
|
ТРАНЗАКЦИЯ 1 |
ТРАНЗАКЦИЯ 2 |
|
SQL> UPDATE employee 2 SET salapy=salary*1.1 3 WHERE id= 24877; 1 row updated. |
SQL> UPDATE employee 2 SET salapy=salary*1.1 3 WHERE id= 24878; 1 row updated. |
Блокировка на уровне таблицы носит тип ТМ, блокировка на уровне строки таблицы – тип ТХ
2.2 Выполнение лабораторной работы
Пример 1.Блокировки операции INSERT.
1) Создать таблицу SALARY: create table SALARY( USER_ID NUMBER,
DATE_OF_CHANGE DATE,
SALARY NUMBER(19,4));
2) Внести строку в таблицу: insert into SALARY values (1,sysdate,1899.67);
3 ) Определение блокировок DML.
3.1) Определение SID - уникальный идентификатор, присваемый подключению пользователя на момент его работы с сервером, изменяющий сессии (SID – номер сессии).
В окне PL/SQL Developer, в котором производились изменения, выполнить следующий запрос
Select distinc sid from v$mystat; или
select sid from v$session s where s.AUDSID = any (select userenv('sessionid') from dual);
3.2) Определение блокировок, поставленных ЭТОЙ сессией
Select * from v$lock where sid = номер полученный на шаге 3.1
Краткая информация по таблице v$lock. SID номер блокирующей сессии, TYPE тип блокировки, ID1 (интерпретация в зависимости от типа блокировки), ID2(интерпретация в зависимости от типа блокировки), LMODE режим блокирования ( перечень режимов в таблице 1), CTIME время в секундах в ожидании (удержании) блокировки.
Типы блокировки TX. Блокировка TX захватывается, когда транзакция делает первое изменение любого объекта и удерживается до тех пор, пока транзакцией не сделан COMMIT или ROLLBACK. Параметры ID1 и ID2 генерируются из идентификаторов транзакции(фактически это ссылка на слот в сегменте отката(отмены)).
Типы блокировки TM. Блокировка типа TM ставится на объект в случае исполнения над ним DML команд .Параметр ID1 для блокировки типа TM - номер объекта в системе, на котором удерживается блокировка.
/*Получаем следующую картину (некоторые столбцы будут отличаться от полученного результата)*/.
Работающая сессия с номер 34 захватила две блокировки, первая типа TX - все изменения, проводимые текущей транзакцией в рамках сессии, вторая ТМ - все изменения, проводимые текущей транзакицией 34 сессии над объектом 52161.
3.3) Определение имени объекта 52161 с помощью запроса
select name from sys.obj$ o where o.obj# = 52161;
На экране выводится таблица SALARY.
Блокировка TM удерживается в режиме LMODE =3 (sub exclusive - режим монопольной блокировки) – заблокированна эксклюзивно одна строка объекта, которая была добавлена в результате операции вставки.
В классическом варианте операция INSERT не может вызвать ожидания своих блокировок транзакциями других сессий, так как никто не может обратиться к строке, которую INSERT внес. Эти новые данные другие транзакции не видят ( изоляция транзакций ).
Единственное исключение- вставка в таблицу с уникальным ключевым атрибутом (PRIMARY KEY NOT NULL). В этом случае, если две транзакции пытаются внести в ключ одинаковые значения, вторая по времени прихода “зависнет”, так как будет ожидать окончания первой, чтобы понять может она внести значение или должна прерваться по ошибке по условию уникальности ключа.
Пример 2. Блокировки оператора UPDATE и назначение блокировки TX.
1) Внести в созданную в примере 1 таблицу SALARY строчку,
insert into SALARY values(1,sysdate,1989.90); и выполнить операцию commit
commit;
2) Убедиnmся, что PL/SQL Developer работает в режиме “Multi Session” (пункт меню Tools вкладка Preferences).
3) Открыть два окна типа SQL Window, так как режим работы Multi Session заключается в том, что каждое окно будет иметь свою собственную сессию, т.е. запросы могу исполняться из окон параллельно.
4) Для каждого из окон узнать идентификаторы сессий SID и запомнить их:
select distinct sid from v$mystat;
5) В окне 1 выполнить следующий оператор
update SALARY set SALARY = 0.0,DATE_OF_CHANGE = sysdate where user_id =1
В окне 2 выполнить следующий оператор
update SALARY set SALARY = 1000000.0,DATE_OF_CHANGE = sysdate where user_id =1
6) Убедиться, что во втором окне исполнение зависает. Выяснить причины зависания.
7) Открыть еще одно окно SQL Window и запросить блокировки для “зависшей” сессии:
select * from v$lock where sid = записанно на шаге 3)
Как видно причина зависания сессии блокировка типа TX( для блокировки, которую мы ожидаем LMODE равен 0, так она не захвачена, а строка REQUEST содержит режим, в котором блокировка запрошена. Строка запрошена в эксклюзивном режиме, т.е. “зависшее” окно ожидает, когда некая транзакция зафиксирует или откатит свои изменения.
8) Выяснить, из какой сессии транзакция, с помощью следующего запроса
select * from v$session s where s.SADDR in (
select ses_addr from v$transaction where xidsqn = параметр ID2 транзакции типа TX )
Параметр SID результата совпадает с SID первого окна, т.е. что и следовало ожидать: “зависщая” сессия обратилась к строке таблицы, измененной первой сессией и, соответственно, зависла, ожидая освобождения строки.
/*Назначение блокировки TX. Так как Oracle блокирует записи построчно, создание блокировки на каждую строку неэффективно с точки зрения ресурсов, очевидно, что вместо этого сессии синхронизируются, повисая на абстракции более высокого уровня.*/
При выполнении операций DML(UPDATE,DELETE, INSERT) над строкой данных одной таблицы Oracle создает блокировку TM. TM блокирует таблицу на sub exclusive доступ.
При созданнии транзакции, что предшествует любой операции DML, выполняемой после создания сессии, операторов commit, rollback, опреаторов DDL , Oracle создает блокировку TX. Все сессии DML, операции которых затрагивают данные, удерживаемые транзакцией, могли запросить эту блокировку ( т.е. повиснуть на ожидании завершения транзакции ).
Пример 3. UPDATE и SELECT(Продолжение примера 2)
- Сессия, внесшая изменение – незафиксированная
update SALARY set SALARY = 0.0,DATE_OF_CHANGE = sysdate where user_id =1
- Сессия, пытающаяся внести изменения – зависшая
update SALARY set SALARY = 1000000.0,DATE_OF_CHANGE = sysdate where user_id =1
Операция чтения не была заблокирована, и вернула данные, которые в данный момент являются последними зафиксированными даными для этой строки.
СУБД Oracle никогда не блокирует операцию чтения!!!
UPDATE всегда блокирует обновляемую строку для операций DML и DDL из других транзакий.
Пример 4. Принудительное блокирование.
В случае, если необходимо заблокировать объект или часть объекта, вручную, т.е., не выполняя над ним DML или собираясь это сделать в будущем, можно использовать операторы:
Lock table и select for update.
SELECT *FROM SALARY;
Зависает на блокировке.
Оператор select for update позволяет заблокировать выбранный операцией SELECT результирующий набор в sub exclusive режиме, как при обычной операции UPDATE. Блокировка снимается любым оператором, разрывающим транзакцию: COMMIT, ROLLBACK, операторы DDL.
Таблица 1. Режимы блокировок.
|
Operation |
Lock Mode |
LMODE |
Description |
|
Select |
NULL |
1 |
null |
|
Select for update |
SS |
2 |
sub share |
|
Insert |
SX |
3 |
sub exclusive |
|
Update |
SX |
3 |
sub exclusive |
|
Delete |
SX |
3 |
sub exclusive |
|
Lock For Update |
SS |
2 |
sub share |
|
Lock Share |
S |
4 |
share |
|
Lock Exclusive |
X |
6 |
exclusive |
|
Lock Row Share |
SS |
2 |
sub share |
|
Lock Row Exclusive |
SX |
3 |
sub exclusive |
|
Lock Share Row Exclusive |
SSX |
5 |
share/sub exclusive |
|
Alter table |
X |
6 |
exclusive |
|
Drop table |
X |
6 |
exclusive |
|
Create Index |
S |
4 |
share |
|
Drop Index e |
X |
6 |
exclusive |
|
Truncate table |
X |
6 |
exclusive |
Пояснения к таблице:
Exclusive: эксклюзивный доступ, предотвращает любой конкурентный доступ к объекту.
Shared: разделяемая, разрешен доступ на чтение, запрос состояния, запрещена модификация объекта.
Null: Не блокирует объект, устанавливается для получения уведомлений о изменении состояния блокируемого объекта.
Sub-Shared: Shared блокировка на часть объекта.
Sub-Exclusive: Exclusive блокировка на часть объекта.
Shared-Sub-Exclusive: Комбинация Shared and Sub-Exclusive. Эксклюзивный доступ к части объекта и разделяемый на весь объект.
2.3 Задание
Литература