Re: locking tables between db connections in the same script

Hmm... it seems the reason is that in DB_oci8::connect() the OCILogon() method is used, rather than OCINLogon() which means that a connection is shared for the lifetime of the page/script. Is there a neat to get DB_oci8 to use a new connection each time? Tristan. "Tristan Ludowyk" <tristan00au@yahoo.com> wrote in message news:20030904072317.75209.qmail@pb1.pair.com... > Hi, I've been experimenting with locking tables in oracle 8 using queries > like "lock table x in exclusive mode nowait". In my stand-alone script, > using PEAR::DB objects, I am trying to lock a table for the duration of a > transaction, and using a second connection, attempt to take out the same > lock. > > I would expect that a 2nd db connection would not be able to take out the > lock after the 1st one has acquired it, but it seems to work. If I run the > same script twice (simultaneously), the 2nd script fails when it tries to > get the lock, as expected. > > ie. I thought the following would output: > > got first lock > can't get second lock > > but instead i get: > > got first lock > got second lock > > and if run a second time while the first instance is sleeping: > > can't get first lock > > Why does this behaviour occur? > Tristan. > > <? > require_once ('DB.php'); > $conn1 = DB::connect("oci8://***"); > $conn2 = DB::connect("oci8://***"); > > $conn1->autoCommit(false); > $conn2->autoCommit(false); > > // Lock the table in session 1 > if (DB::isError($conn1->query("lock table x in exclusive mode nowait"))) > { > echo "can't get first lock\n"; > echo $conn1->error; > } > else > { > echo "got first lock\n"; > // Attempt to take out lock in another session - this succeeds as well > if (DB::isError($conn2->query("lock table x in exclusive mode nowait"))) > { > echo "can't get second lock\n"; > echo $conn2->error; > } > else > echo "got second lock\n"; > } > > // wait a bit so we can run the script again > sleep(10); > ?>