Entry

How does autocommit work in Oracle and PHP?

Are two identical Oracle sessions in a script part of the same transaction?

Jul 10th, 1999 09:07
Nathan Wallace, Thies C. Arntzen

What happens is:

Oracle transactions work on the connection-level, and by default
connection will be reused as long as a "page" (or the httpd if you use
plogon) runs. (as long as u use the same u/p combination. sample:

    $c1 = ocilogon("scott","tiger");
    $s1 = ociparse($c1,"insert into t (1)");
    ociexecute($s1,OCI_DEFAULT); 

    $c2 = ocilogon("scott","tiger");
    $s2 = ociparse($c2,"insert into t (2)");
    ociexecute($st,OCI_DEFAULT);

    ocicommit($c1); // THIS WILL COMMIT BOTH statements, as $c1 and $c2 
                    // are ONE session with different service-contexts!

This is important to understand.

The reason this is done (btw: same in ora_* stuff and maybo others) is
that usually we're looking for speed and establishing session is very
expensive with oracle!

But there's a way to isolate transactions: ocinlogon()

    http://www.php.net/manual/ref.oci8.php3

ocinlogon will create a new session on the server (so here we got the
connection-overhead) but this one will be isolated from all other
session!

The example below illustrates this behaviour.

sessions created with ocinlogon are never persistent! but if you need
your "own-private" transaction context that's the way to go. (but they
will still share the server-connection with other ocilogon, ociplogon
calls)

Some more explanation:

ocilogon, nlogon, plogon are divided into two parts:

we have user,pw and service

OCIServerAttach is called EXACTLY once for every diffrent service you
reference in a script (this call creates the oracle shadow-process)

OCISessionBegin is called EXATLY once for every different pair uf
user/pw
combination. (here is our problem!)

if you use ocinlogon a new OCISessionBegin is issued (no new
server-connection if we already have a match).

Example of the use of ocinlogon:

<?php
//set_time_limit(0);
//ociinternaldebug(1);

$db = "";

$c1 = ocilogon("scott","tiger",$db);
$c2 = ocinlogon("scott","tiger",$db);

/*$co1 = array();
$co2 = array();
$co3 = array();
for ($i = 0; $i < 10; $i ++)
  $co1[] = ociplogon("scott","tiger",$db);
for ($i = 0; $i < 10; $i ++)
  $co2[] = ocilogon("scott","tiger",$db);
for ($i = 0; $i < 10; $i ++)
  $co3[] = ocinlogon("scott","tiger",$db);

$db = "godzilla";

$c1 = ociplogon("scott","tiger",$db);
$c2 = ocilogon("scott","tiger",$db);
$c3 = ocinlogon("scott","tiger",$db);

$co1 = array();
$co2 = array();
$co3 = array();
for ($i = 0; $i < 10; $i ++)
  $co1[] = ociplogon("scott","tiger",$db);
for ($i = 0; $i < 10; $i ++)
  $co2[] = ocilogon("scott","tiger",$db);
for ($i = 0; $i < 10; $i ++)
  $co3[] = ocinlogon("scott","tiger",$db);
*/


function create_table($conn)
{ $stmt = ociparse($conn,"create table scott.hallo (test
varchar2(32))");
  ociexecute($stmt);
  echo $conn." created table\n";
}

function drop_table($conn)

{ $stmt = ociparse($conn,"drop table scott.hallo");
  ociexecute($stmt);
  echo $conn." dropped table\n";
}

function insert_data($conn)
{ $stmt = ociparse($conn,"insert into scott.hallo values(sysdate)");
  ociexecute($stmt,OCI_DEFAULT);
  echo $conn." inserted hallo\n";
}

function delete_data($conn)
{ $stmt = ociparse($conn,"delete from scott.hallo");
  ociexecute($stmt,OCI_DEFAULT);
  echo $conn." deleted hallo\n";
}

function commit($conn)
{ ocicommit($conn);
  echo $conn." commited\n";
}

function rollback($conn)
{ ocirollback($conn);
  echo $conn." rollback\n";
}

function select_data($conn)
{ $stmt = ociparse($conn,"select * from scott.hallo");
  ociexecute($stmt,OCI_DEFAULT);
  echo $conn."----loading\n";
  while (ocifetch($stmt))
    echo $conn." <".ociresult($stmt,"TEST").">\n";
  echo $conn."----done\n";
}

create_table($c1);
insert_data($c1);

select_data($c1);
select_data($c2);

rollback($c1);

select_data($c1);
select_data($c2);

insert_data($c2);
commit($c2);

delete_data($c1);
select_data($c1);
select_data($c2);
commit($c1);
select_data($c1);
select_data($c2);


drop_table($c1);
?>