Тупики в Oracle
Я хочу сделать сценарий, в котором сеансы оракула, которые переходят в тупик, автоматически убиваются. Можно ли узнать идентификатор сеанса для сеансов, которые попадают в тупик. На данный момент я должен отскочить базу данных, чтобы удалить тупик. Возможно ли какое-либо решение этой проблемы?
3 ответа
Я хочу сделать скрипт, где сеансы оракула, которые зашли в тупик, автоматически убиваются
РЕДАКТИРОВАТЬ Лучше объяснил, исправил несколько предложений и добавил тестовый пример, чтобы продемонстрировать сценарий тупика.
Почему вы хотите заново изобрести колесо? Oracle автоматически обнаруживает тупик, выбрасывает ORA-00060: deadlock detected while waiting for resource и откатывает одну из транзакций, вовлеченных в тупик, который Oracle определил как жертву. Предыдущие успешные транзакции не откатываются. Даже после ошибки взаимоблокировки, если выдан коммит, предыдущая успешная транзакция будет зафиксирована. В это время транзакция другого сеанса также будет успешной, и вы можете выполнить фиксацию. Там нет ничего, что вам нужно явно сделать здесь. Блокировки автоматически удаляются - вам никогда не нужно их очищать.
Обычно Oracle занимает секунду или две, чтобы обнаружить тупик и выдает ошибку.
Вы можете попробовать выполнить простой тестовый пример, как показано здесь: Понимание Oracle Deadlock
Давайте посмотрим на тестовый пример -
SQL> CREATE TABLE t_test(col_1 NUMBER, col_2 NUMBER);
Table created
SQL> INSERT INTO t_test VALUES(1,2);
1 row inserted
SQL> INSERT INTO t_test VALUES(3,4);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM t_test;
COL_1 COL_2
---------- ----------
1 2
3 4
Обратите внимание на время каждой транзакции, я установил время для лучшего понимания.
СЕССИЯ: 1
12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2;
1 row updated.
Elapsed: 00:00:00.00
СЕССИЯ: 2
12:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4;
1 row updated.
Elapsed: 00:00:00.00
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2;
На этом этапе СЕССИЯ 2 продолжает ждать.
СЕССИЯ: 1
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;
На данный момент СЕССИЯ 2 является жертвой тупика, СЕССИЯ 1 все еще ждет.
Давайте посмотрим на детали сессии из СЕССИИ 2 -
12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe';
SID STATUS PROGRAM SQL_ID STATE WAIT_CLASS BLOCKING_SE EVENT
---------- -------- --------------- ------------- ------------------- --------------- ----------- ----------------------------------------------------------------
14 ACTIVE sqlplus.exe 60qmqpmbmyhxn WAITED SHORT TIME Network NOT IN WAIT SQL*Net message to client
134 ACTIVE sqlplus.exe 5x0zg4qwus29v WAITING Application VALID enq: TX - row lock contention
Elapsed: 00:00:00.00
12:22:18 SQL>
Так, v$session подробности при просмотре в СЕССИИ 2, т. е. SID 14, говорят о статусе ACTIVE.
Давайте посмотрим на детали сеанса из другого сеанса, давайте назовем это СЕССИЯ 3 ради. Помните, СЕССИЯ 1 все еще ждет.
SQL> set time on timing on
12:24:41 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe'
SID STATUS PROGRAM SQL_ID STATE WAIT_CLASS BLOCKING_SE EVENT
---------- -------- --------------- ------------- ------------------- ---------- ----------- ------------------------------
13 ACTIVE sqlplus.exe 60qmqpmbmyhxn WAITED SHORT TIME Network NOT IN WAIT SQL*Net message to client
14 INACTIVE sqlplus.exe WAITING Idle NO HOLDER SQL*Net message from client
134 ACTIVE sqlplus.exe 5x0zg4qwus29v WAITING Applicatio VALID enq: TX - row lock contention
n
Elapsed: 00:00:00.01
12:24:44 SQL>
Таким образом, для других сессий СЕССИЯ 2, то есть СИД 14, неактивна. СЕССИЯ 1 все еще ждет с событием enq: TX - row lock contention,
Давайте совершим сессию 2 -
12:22:18 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
12:25:43 SQL>
На этом этапе блокировка снята для СЕССИИ 1, давайте также передадим сессию 1 -
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;
1 row updated.
Elapsed: 00:08:27.29
12:25:43 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
12:26:26 SQL>
Elapsed: 00:08:27.29 показывает, что СЕССИЯ 1 ожидала, пока СЕССИЯ 2 не будет завершена.
Подводя итог, вот вся история сессии 1 -
12:16:06 SQL> UPDATE t_test SET col_1 = 5 WHERE col_2=2;
1 row updated.
Elapsed: 00:00:00.00
12:16:15 SQL> UPDATE t_test SET col_1 = 8 WHERE col_2=4;
1 row updated.
Elapsed: 00:08:27.29
12:25:43 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
12:26:26 SQL>
Подводя итог, вот вся история второй сессии -
12:16:04 SQL> UPDATE t_test SET col_1 = 6 WHERE col_2=4;
1 row updated.
Elapsed: 00:00:00.00
12:16:31 SQL> UPDATE t_test SET col_1 = 7 WHERE col_2=2;
UPDATE t_test SET col_1 = 7 WHERE col_2=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:24.47
12:22:15 SQL> select sid,status,program,sql_id, state, wait_class, blocking_session_status, event from v$session where schemaname='LALIT' and program='sqlplus.exe';
SID STATUS PROGRAM SQL_ID STATE WAIT_CLASS BLOCKING_SE EVENT
---------- -------- --------------- ------------- ------------------- --------------- ----------- ----------------------------------------------------------------
14 ACTIVE sqlplus.exe 60qmqpmbmyhxn WAITED SHORT TIME Network NOT IN WAIT SQL*Net message to client
134 ACTIVE sqlplus.exe 5x0zg4qwus29v WAITING Application VALID enq: TX - row lock contention
Elapsed: 00:00:00.00
12:22:18 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
12:25:43 SQL>
Теперь давайте посмотрим, какая транзакция была откатана, а какая зафиксирована -
12:25:43 SQL> select * from t_test;
COL_1 COL_2
---------- ----------
5 2
8 4
Elapsed: 00:00:00.00
12:30:36 SQL>
Заключение
На мой взгляд, лучший способ узнать подробности тупиковой ситуации сеанса - записать детали как можно более подробно. Иначе, это кошмар для администратора базы данных, чтобы расследовать без надлежащей информации, зарегистрированной. В этом отношении, даже Разработчик посчитал бы, что это было бы геркулесовой задачей исправить и исправить фактический недостаток проекта, если подробности ошибки взаимоблокировки не зарегистрированы подробно. И, наконец, в одном утверждении: тупик вызван недостатком дизайна, Oracle - просто жертва, а приложение - виновник. Тупики страшны, но они указывают на недостатки дизайна, которые рано или поздно должны быть устранены.
Пользователь 1
update table_c set id = 200 where id = 13;
BEGIN
DBMS_LOCK.sleep(14);
END;
/
update table_c set id = 200 where id = 15;
пользователь 2
update table_c set id = 2000 where id = 15;
BEGIN
DBMS_LOCK.sleep(14);
END;
/
update table_c set id = 1000 where id = 13;
Что такое тупик?
Тупик возникает, когда сеанс (A) хочет, чтобы ресурс удерживался другим сеансом (B), но этот сеанс также хочет, чтобы ресурс удерживался первым сеансом (A). Может быть задействовано более 2 сессий, но идея одна и та же.
Диагностическая информация, выдаваемая ORA-60
Ошибка ORA-60 обычно записывает сообщение об ошибке в журнал предупреждений вместе с именем созданного файла трассировки. Точный формат зависит от версии Oracle. Файл трассировки будет записан в каталог, указанный USER_DUMP_DEST или BACKGROUND_DUMP_DEST , в зависимости от типа процесса, который создает файл трассировки.
Разрешение взаимоблокировки
Oracle умен и может найти тупиковую ситуацию за 3 секунды. Ниже приведены варианты предотвращения взаимоблокировок внутри базы данных.
Попросите сеанс, получающий ошибку взаимоблокировки ORA-00060 , выдать либо COMMIT, либо ROLLBACK.
Попросите ожидающий сеанс убить SQL/транзакцию
Загляните в журнал предупреждений / файл трассировки для сеансов, вовлеченных в тупик, и сообщите команде приложения, чтобы улучшить код.
Как администратор базы данных, всякий раз, когда вы получаете предупреждение о взаимоблокировке, немедленно свяжитесь с командой приложения и сообщите им.