Снятие блокировки в oracle

В посте http://itech-notes.blogspot.com/2013/07/select-for-update.html был рассказ о работе с конструкцией select for update в oracle. И был там такой текст: "если ... обнаруживается, что на какой-то строке уже имеется лок от другой сессии, то текущая сессия будет ожидать - бесконечно! - пока лок не будет снят, и только после этого продолжит выполнение".

Интересный вопрос - собственно, а как и когда снимается лок (т. е. блокировка)?

Теория.

1)Лок освобождается, только когда завершается транзакция.

2)Транзакция завершается, когда выполняются явные фиксация (commit) или откат (rollback) изменений, а также когда завершается открытая сессия (что по факту означает неявный откат).

А как на практике?

Прим. 1: здесь предполагается, что сам код написан безопасно, что после наложения блокировки следует блок finally для снятия лока. Вопрос в том, что случится при непредвиденной ситуации, когда безопасный код просто не будет иметь возможности выполниться и "подчистить" за собой.

Ок, проверка. На одном компе запущено 2 приложения, первое лочит строки таблицы, второе "повисает" в ожидании возможности залочить. При остановке первого приложения (например, кнопка stop в ide, когда прикладной код по корректному закрытию транзакции не выполняется) второе приложение сразу подхватывает.

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

При "жёстком" отключении сетевого шнура - беда. Блокировка остаётся. Висит долго. У меня не хватило терпения ждать вечность, но времени прошло немало. И никаких поводов разлочиться самостоятельно нет. Аналогично при переводе сетевого соединения в статус disabled в окне "network connections" win os.

Итак, получается, что сделать явный коммит или откат не можем, т. к. соединение благополучно потеряно. Что насчёт сессии - похоже, она остаётся? Для определения сессии, которая наложила лок, можно сделать такое:

select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

Пример результат:

TCMCP     NCS_DATA     TABLE     5223     63810     INACTIVE     username     hostname

Или что-нибудь в этом же духе. Смысл - проверить существование лока и его уровень, а также найти сессию и пользователя. To see who is locking the table... then you can find out what and why they are doing... before you kill them :)

Ну, в данном случае и искать никого не надо - видно, что сессия-таки осталась. Её можно специально закрыть (http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php):
(Прим. 2: конечно, на принудительное закрытие сессии нужны соответствующие права, иначе результатом окажется жёстокое разочарование в виде ORA-01031: insufficient privileges. При том, что обычно такие права кому попало не раздаются...).

ALTER SYSTEM KILL SESSION '5223, 63810';

Но вопрос-то - почему сессия осталась открытой? Спросим Тома (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2233109200346833212):

TCP/IP doesn't interrupt things by default, by design. When a connection goes away, the client and/or server do not immediately get notified. So, if your client connects to the database and does nothing - and you unplug your client (blue screen it, kill it, pull out the network cable, crash the computer, whatever) the odds are the session will stay in the database. The server will not know that it will never receive a message from you. We have dead client detection for that if it becomes an issue: http://docs.oracle.com/cd/B12037_01/network.101/b10776/sqlnet.htm#sthref476

Получается, что серверу oracle’а глубоко параллельны проблемы наподобие "мышь перегрызла сетевой кабель" и "внезапно упавший метеорит разбил компьютер". В качестве варианта решения предлагается параметр SQLNET.EXPIRE_TIME в sqlnet.ora. Хотя и тут не всё просто.

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

Прим. 3. Какие ещё могут быть действия? Если используется сервер приложений (jboss, websphere, weblogic, glassfish), то они позволяют задать таймаут для открытой транзакции, по истечении которого сам AS выполнит откат.