Thursday, June 28, 2007

SET TRANSACTION READ TFM

Recently I've been having to log on the production server as SYSTEM (don't ask why). Obviously, this is a risky thing to do, so I have taken to running

set transaction read only
/

at the start of the session. This prevents me accidentally dropping a key table due to a sudden moment of madness (or just because I have lost track of which PuTTY session is logged on to which database).

This morning I was monitoring the status of a job going through the system. It seemed to have been stuck on one stage for over half an hour. I was worried because this stage ought to have taken a few seconds. I checked the V$SESSION_WAIT view and there was loads of activity: a different wait event every time I queried on that session.

Of course, what I had forgotten was that setting the transaction to READ ONLY doesn't just prevent me executing DML or DDL [see update below - APC] in the session: it also sets the isolation level. So (in the words of the documentation) "All subsequent queries in that transaction only see changes committed before the transaction began." No wonder I wasn't seeing any changes in the status table data! Obviously the V$ views, being dynamic views on the X$ tables, have a back door through the isolation level. Which I suppose makes sense but it could confuse a stupid person.

One to file under Oracle Things I Shouldn't Forget.

Update


In his comment Hemant Chitale points out that SET TRANSACTION READ ONLY does not prevent DDL (because DDL issues an implicit commit and so ends the transaction). Actually in real life I really just worry about changing production data; I just used the DROP TABLE example for dramatic effect. But of course, this is no excuse for not writing something wrong in article, especially as the documentation I linked to makes that very point.

As an ironist I can only applaud the name I gave the article.

2 comments:

Hemant K Chitale said...

Not sure where you got that information that SET TRANSACTION READ ONLY prevents DML and DDL.

Every DDL includes an implicit commit that is issued _first_ even if the DDL fails.

See :
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
ANOTHER_TABLE
MY_SMALL_TABLE
PLAN_TABLE
TEST_SORT

SQL> set transaction read only;

Transaction set.

SQL> drop table another_table;

Table dropped.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
MY_SMALL_TABLE
PLAN_TABLE
TEST_SORT

SQL>

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情人歡愉用品,情趣用品,AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰,自慰套,性感吊帶襪,情趣用品加盟,情人節禮物,情人節,吊帶襪,成人網站,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,A片,A片下載,免費A片,免費A片下載,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,正妹,成人網站,A片,免費A片,A片下載,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,色情影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,嘟嘟成人網

愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片