On 20/11/16 05:30, Daniel Miller dmiller@... [firebird-php] wrote:

> Hi!
>
> I'm not asking what a deadlock is, or why it happens. I'm not even
> asking how to avoid them. I'm asking how to handle them properly with
> Firebird & PHP - because I'm not finding documentation for it.
>
> All my calls (I think!) to ibase_query() are prefaced by ibase_trans(),
> and my standard options are
>
> IBASE_READ|IBASE_COMMITTED|IBASE_REC_VERSION
>
> or IBASE_WRITE if appropriate.
>
> It's possible that my deadlock issues will go away now...as I just
> changed the flags from using '+' to '|'. Oops. However...I'm obviously
> not understanding something. The following is typical of my Firebird
> PHP code:

The transaction flags are single bit flags, so YES you should be using
'|' to combine them .. the default is BASE_WRITE | IBASE_CONCURRENCY |
IBASE_WAIT which has always seemed wrong so the switch to
IBASE_COMMITTED is normal.

I continue to use ADodb and have the firebire driver selecting
IBASE_WAIT | IBASE_REC_VERSION | IBASE_COMMITTED and as long as changes
are committed correctly I don't get a problem, but I do get the update
conflict from time to time when a few people are trying to view the site
and all generate a +1 on the hits table. Not actually using the hits
information these days I've simply switched that bit off and rely on a
Firebird/ADOdb version of piwik for tracking page access.

> try {
> $th = ibase_trans( $this->dbh,
> IBASE_WRITE|IBASE_COMMITTED|IBASE_REC_VERSION );
> if ( !$th ) {
> throw new Exception( 'Unable to create new transaction
> because: ' . ibase_errmsg(), ibase_errcode() );
> }
>
> $qs = "update or insert into NODES (NODE) values ($node)
> matching (NODE)";
> $qh = ibase_query( $th, $qs );
> if ( $qh === false ) {
> throw new Exception( "Failed to update node $node " .
> ibase_errmsg() );
> }
>
> return $qh;
> } catch (Exception $e) {
> echo "Caught exception: $e\n";
> return false;
> }

ADOdb manages to handle that without any of the extra try/catch wrapper.
The main problem I see here is that it's not these operations that will
fail if they are being queued to be committed later. Firebird is working
outside of the PHP program flow in a lot of cases, so 'IBASE_NOWAIT'
will give an error on the transaction if the query is trying to queue
something that is a conflict.

> Now...I would have though that between checking the return values of
> both ibase_trans() and ibase_query(), or by enclosing in a try/catch
> block, I would be able to at least see the conflict. But that doesn't
> happen. All I get is via stderr:
>
> PHP Warning: ibase_query(): deadlock update conflicts with concurrent
> update concurrent transaction number is 13403
>
> So...if the above code construct isn't able to trap the deadlock - what
> would?

I'll put my hands up here ... I know what is going on and to some extent
I know why PHP is not the ideal platform to handle it but my own
processes are based around ADOdb wrapping the SQL elements and giving a
consistent interface even where the database does not ACTUALLY have
transactions ... and my systems work fine.

Bottom line ... I just log the 'deadlock update' error message and
ignore it because PHP is in the wrong 'state' when it happens to recover
with a valid page saying there was a problem. Blank pages are something
try/catch should be able to avoid, but not how PHP currently uses them
so catching the problem SQL stuff before using it is easier than trying
to handle the out of line crash.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk