When a transaction begins? - Bilal Hatipoglu's Blog

Recommend

Contact

Menu
∙ Home ∙ Index (All Topics) ∙ About Me ∙ About This Blog ∙ Favourite Links ∙ RSS Feed

Categories
∙ ASP & PHP (1) ∙ HTML, XML and CSS (2) ∙ C / C++ (5) ∙ Java, JSP and Servlet (0) ∙ SQL-Oracle-PL/SQL (28) ∙ Operating Systems (1) ∙ OFF-Topic (8)

Popular Topics (Top 10)
∙ Decode Function in Oracle SQL (10431) ∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (9614) ∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (8683) ∙ Oracle performance analysis - Tracing and performance evaluation (7822) ∙ Implicit vs. Explicit cursors - Performance analysis (5222) ∙ Turkcell Staj Günlüğü - 4: Transaction Management (4268) ∙ Differences between C and C++ (3983) ∙ Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files (3779) ∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (3607) ∙ Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments (3506)

Most Recent (Last 10)
∙ Matematik Asla Yalan Söylemez! ∙ Finding and Removing Loop on a Singly-Linked List ∙ Obfuscated C ∙ Is C a Vitamin? Yes, of course... ∙ Differences between C and C++ ∙ Whence C? Why C? Whither C? ∙ Türkçe Karakterli Domain'lerin İç Yüzü ∙ Windows Source Codes ∙ Decode Function in Oracle SQL ∙ Hello World!

Recent Comments (Last 10)
∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez! ∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı? ∙ "bilgi paylaşıldıkça deger kazanır." By zülfü yıldırım on Turkcell Staj Günlüğü - 12: Partitioning ∙ "Teşekkürler" By serdar on Turkcell Staj Günlüğü - 12: Partitioning ∙ By Gokhan on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "The Power of Attitude" By Tonguç Yılmaz on Matematik Asla Yalan Söylemez! ∙ ":)" By Ozay Akdora on Obfuscated C ∙ By Ömer YURDUSEVEN on Windows Source Codes ∙ By Özgür Macit on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "Thanks" By Ganesh on Difference between db block gets and consistent gets

Archive (Last 12 Months)
∙ Feb, 2008 (4) ∙ Jan, 2008 (2) ∙ Dec, 2007 (1) ∙ Sep, 2007 (4) ∙ Aug, 2007 (9) ∙ Jul, 2007 (22) ∙ Jun, 2007 (3) ∙ Index (All Records)

Other Related Blogs
∙ Tom Kyte’s Blog ∙ Steven Feuerstein’s Blog ∙ Jonathan Lewis’s Blog ∙ H.Tonguç Yılmaz Oracle Blog ∙ Mennan Tekbir's Blog ∙ Hakkı Oktay’s Blog ∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors

: 45
: 120092
: 36

When a transaction begins?

Category: SQL-Oracle-PL/SQL
Date: 24.07.2007 19:02:39

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit. The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Transaction management is the main mechanism that distinguishes between DBMS from other DBMS and DBMS from file system. The main purpose is to take the database from one consistent state to next. This satisfies the consistency of database for many cuncurrent users. That is imposible with file systems.

In Oracle, every manipulation statement must be in a transaction, so whether you give a "set transaction" command or not, Oracle opens a transaction. It is obvious that a transaction ends with a commit or rollback command. But it actually is not obvious that when a transaction begins.

Many peope say that "A transaction begins with first DML statement" or like that. I will show you some examples and some experimental about that. But first, we will look at some descriptions about that.

In Oracle 10gR2 Documentation, it is written like "A transaction begins with the first executable SQL statement". That is ambiguous, what is an "executable statement". In what conditions a transaction begins.

Also some people writes like "A new transaction begins when a session is opened and after each rollback or commit". That is really wrong, no doubt about it :)

And I see some descriptions about this topic like "Transaction begins at execution of first data change" or like "A transaction begins with the first statement that gets a TX lock" (that is first statement that modifies data). These are actually true. I will illustrate with examples now. There are a lot of things to understand for knowing people. I will not comment for each of them.

SQL> conn test@bilalEE as sysdba
Connected.
SQL> select * from v$transaction;

no rows selected

SQL> create table t as select rownum n from all_objects where rownum < 1000;

Table created.

SQL> select * from v$transaction;

no rows selected

SQL> select * from t where n=1;

         N                                                                      
----------                                                                      
         1                                                                      

SQL> select * from v$transaction;

no rows selected

SQL> update t set n=2000 where n=3000;

0 rows updated.

SQL> select * from v$transaction;

no rows selected

SQL> delete from t where n=3000;

0 rows deleted.

SQL> select * from v$transaction;

no rows selected

SQL> alter table t add constraint pk_t primary key(n);

Table altered.

SQL> select * from v$transaction;

no rows selected

SQL> insert into t values(500);
insert into t values(500)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.PK_T) violated 


SQL> select * from v$transaction;

no rows selected

SQL> create trigger trig_t before insert on t
  2  begin
  3  raise_application_error(-20001,'trigger not allows to insert to this table');
  4  end;
  5  /

Trigger created.

SQL> select * from v$transaction;

no rows selected

SQL> insert into t values(1001);
insert into t values(1001)
            *
ERROR at line 1:
ORA-20001: trigger not allows to insert to this table
ORA-06512: at "TEST.TRIG_T", line 2
ORA-04088: error during execution of trigger 'TEST.TRIG_T'


SQL> select * from v$transaction;

no rows selected

SQL> savepoint sp1;

Savepoint created.

SQL> select * from v$transaction;

no rows selected

SQL> create procedure test_p as
  2  begin
  3  dbms_output.put_line('selam');
  4  end;
  5  /

Procedure created.

SQL> select * from v$transaction;

no rows selected

SQL> exec test_p;
selam

PL/SQL procedure successfully completed.

SQL> select * from v$transaction;

no rows selected

SQL> update t set n=1000 where n=999;

1 row updated.

SQL> select * from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC ST
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --
288E7124          8         44        305          2       1309        203         58 AC

SQL>

Download Code