Re: PEAR DB seems to block 2nd connection from same browser (or CLI execution)

On 9/7/05, Jocke Selin <the.joxor@gmail.com> wrote: > On 9/6/05, Justin Patrin <papercrane@gmail.com> wrote: > > On 9/6/05, Jocke Selin <the.joxor@gmail.com> wrote: > > > Hi all, > > > > > > My first post here (in 4 years or so ;)) so I guess a quick intro is > > > in place; I've been developing PHP since 1998 or so, and used MySQL or > > > PostgreSQL as the backend DB. I've also worked a lot with extensions > > > such as ImageMagick, GD, DOMXML, Sablotron, etc. > > > > > > Anyway.... > > > > > > We've just discovered a peculiar behaviour from our scripts. It > > > started out with us noticing that our back-end CLI scripts weren't > > > running asynchronously, but instead they ran consecutively. It turned > > > out that the 1st script was running just fine, while the 2nd script > > > was waiting to access the database. (PostgreSQL via PEAR DB, using the > > > same DSN). > > > > > > I've since created a test case to narrow down our "discovery". > > > I've created a simple table, something like this: > > > CREATE TABLE test_concurrency ( > > > concurrency_id serial primary key NOT NULL, > > > one integer, > > > two integer, > > > created timestamp DEFAULT now() NOT NULL > > > ); > > > I've then created a frameset that holds two pages; scripts "One" and > > > "Two". Script "One" will update column 'one' in the > > > aforementioned DB > > > with 1, something like UPDATE test_concurrency SET one = (one + 1). > > > Script "Two" does the same thing, but on column 'two'. > > > Script "One" runs for a longer time (say 800 iterations), and has got > > > a slight delay in the loop (usleep(10000);), and script "Two" runs for > > > a shorter time, say 200 iterations. > > > In the loop both scripts also query the same table to output the > > > values of the row, both columns 'one' and 'two' (referred to C1 > > > and C2 > > > later in the post). > > > Before entering the loop, the scripts reset their "own" column to 0. > > > > > > > > > Using PEAR DB, I start "One" (reload the frame), and then almost > > > immediately after I start "Two"... The result is that is that > > > "One" > > > reports C1 starting at 1 and finishing at 800, while C2 stays at 200. > > > When "One" finishes, "Two" starts, reporting C1 at 800, and C2 > > > from 1 > > > to 200. There's one exception to this, and that seems to be when > > > opening a new browser window and pasting the URI to the frameset into > > > this; upon when the scripts display the behaviour I'll explain below. > > > Reloading the frameset displays the aforementioned behaviour. > > > > > > If I use native PostgreSQL functions, I get the behaviour that I'm > > > looking for and expecting; > > > Starting "One" and then quickly starting "Two" results in > > > "One" > > > displaying C1 starting at 1 going up to 800, while C2 starts at 200, > > > and at some point resets to 1, and increments to finish at 200, to > > > stay there. > > > "Two" displays C1 starting at an arbitrary number between 1 and 800, > > > and finishes ca +200 later. C2 displays 1 to 200. > > > > > > This indicates that while "One" was running "Two" was able to > > > access > > > the same table and manipulate it. This is what we like! :) > > > > > > We've tried various things, such as adding $aDSN['new_link'] = TRUE; > > > to the options for DB::connect($aDSN); > > > > > > Can anyone explain the behaviour we're getting with PEAR DB, and if > > > there's some way to turn it off as we need to be able to run several > > > CLI scripts simultaneously. We really don't want to track back to 1997 > > > and write our own PostgreSQL wrapper - that'd feel a bit wrong. > > > > > > If required I can tidy up the scripts and post them somewhere for you to try. > > > > > > Any comments and ideas appreciated! > > > > > > > A quick look at DB/pgsql.php shows this on line 328:$result = > > @pg_exec($this->connection, 'begin;'); > > > > It *looks* like DB is automatically putting a transaction around the > > queries. A little further looking shows that this is only done if > > autocommit is false and the query is a manipulation query (update). > > autocommit is by default true so this shouldn't be happening. Are you > > perhaps setting autocommit to false? If you're not doing it explicitly > > try var_dump($db->autocommit) and see if it's set true or false. > > > Thanks for that pointer Justin, > > I've added some (quite a lot) of var_dump()s in the code and at no > point is autocommit set to FALSE - it's set to TRUE all the time, > which means that there should not be an "automatic" transaction > wrapped around the code. > If nobody can shed some more light on this issue, I guess my next action is to file it as a bug?! :-/ Cheers, /Jocke