Thread: incomplete transaction keeps table locked?

Hi. I'm running PostgreSQL 7.0.2 on Linux 2.2.14, i686.

Are there any circumstances when exiting a PHP web script
where you've started a transaction (with Begin Work) but haven't explicitly
done a Commit Work or Rollback before it ends will lock you out of
a table (until you restart postgresql)?  I wouldn't do this normally but i
was commenting out different portions of the PHP script in question to isolate
a bug in my code.  Does Postgres somehow get notified when a PHP script ends
so it knows to end a transaction in progress?  If not, how can i ensure a
clean slate before attempting more sql commands in the next running of a PHP
script?

In any case, at some point in my commenting and re-running of a script with
a transaction, PHP returned error:

"Unable to jump to row 0 on PostgreSQL result index 6".

The query in question was:

update uledger set cnt=2 where hr=4098 and yr='1' and mid=29;

I then tried this query via psql.  I got no output, not even a prompt. I hit ^c
and got:

Cancel request sent
ERROR:  Query cancel requested while waiting lock

This re-occured everytime i tried the update.  To try and clear what appeared
to be a lock, I'd restarted postgresql.  I re-entered psql and re-tried the
same update.  I got:

ERROR:  Cannot insert a duplicate key into unique index uq_hym

This was a update that had worked correctly hundreds of times.

I took a break, came back a few hours later, ran a few Selects (that's all) and
retried the same update.  It worked fine.

What could have been going on?  The problem is gone for now but i'd to learn
what happened to avoid the problem in the future.

Thank you,

George Herson

From: "George Herson" <gherson@snet.net>

> Hi. I'm running PostgreSQL 7.0.2 on Linux 2.2.14, i686.
>
> Are there any circumstances when exiting a PHP web script
> where you've started a transaction (with Begin Work) but haven't
explicitly
> done a Commit Work or Rollback before it ends will lock you out of
> a table (until you restart postgresql)?  I wouldn't do this normally but i
> was commenting out different portions of the PHP script in question to
isolate
> a bug in my code.  Does Postgres somehow get notified when a PHP script
ends
> so it knows to end a transaction in progress?  If not, how can i ensure a
> clean slate before attempting more sql commands in the next running of a
PHP
> script?

You using persistent connections? If so, Apache+PHP will keep connections
hanging around and PG doesn't know your session is finished. See mail
archives for details and the latest 4.0.x release (4/5?) changelog for
details.

If you're not using persistent connections you've got problems since PHP
should be clearing down the connection at the end of each request.

- Richard Huxton

gherson@snet.net (George Herson) writes:
> Are there any circumstances when exiting a PHP web script
> where you've started a transaction (with Begin Work) but haven't explicitly
> done a Commit Work or Rollback before it ends will lock you out of
> a table (until you restart postgresql)?

The symptoms you describe sound like multiple transactions trying to
update the same row.  There is a row-level lock that prevents the later
ones from completing until the first one commits or aborts.

            regards, tom lane

Yes, thanks, i'm using persistant connections.  You're right about the
php4.0.5 changelog (http://www.php.net/ChangeLog-4.php). It says:

"Fixed pgsql transaction support. (Stig, PEAR/DB)
. . .
PostgreSQL now does a rollback at the end of a request on every
  persistent connection. This is done by doing an "empty" transaction
  on the connection. This was advised by someone from the PostgreSQL
  core-team. (Thies)
Fixed PostgeSQL pg_connect() bug. We would sometimes close the default
  link by accident. (Patch by: aja@nlgroup.ca)"

so i upgraded.  That should help.

But since i made the original post i rebooted my server and now i think
that (a reboot) was also needed, as a couple of little things outside
PHP/Apache/Posgresql (in Netscape, actually) are now working that were
broken pre-boot.

Is it true that when Linux needs a reboot it sometimes isn't obvious?
That can be very dangerous from a time-spent-on-debugging point of
view.  Are there any tests (eg, vmstat) that will quickly tell me when a
reboot is a good idea?  (I'm more used to having to reboot in Windows,
which of course succeeds in making it fairly obvious when your time is
up. :)

thx,
George

Richard Huxton wrote:
>
> From: "George Herson" <gherson@snet.net>
>
> > Hi. I'm running PostgreSQL 7.0.2 on Linux 2.2.14, i686.
> >
> > Are there any circumstances when exiting a PHP web script
> > where you've started a transaction (with Begin Work) but haven't
> explicitly
> > done a Commit Work or Rollback before it ends will lock you out of
> > a table (until you restart postgresql)?  I wouldn't do this normally but i
> > was commenting out different portions of the PHP script in question to
> isolate
> > a bug in my code.  Does Postgres somehow get notified when a PHP script
> ends
> > so it knows to end a transaction in progress?  If not, how can i ensure a
> > clean slate before attempting more sql commands in the next running of a
> PHP
> > script?
>
> You using persistent connections? If so, Apache+PHP will keep connections
> hanging around and PG doesn't know your session is finished. See mail
> archives for details and the latest 4.0.x release (4/5?) changelog for
> details.
>
> If you're not using persistent connections you've got problems since PHP
> should be clearing down the connection at the end of each request.
>
> - Richard Huxton

Ok, thanks.

Hopefully upgrading PHP will get auto-rollbacks performed on exit of any
script where i've started a transaction and forgotten to do an explicit
rollback or commit.

george

Tom Lane wrote:
>
> gherson@snet.net (George Herson) writes:
> > Are there any circumstances when exiting a PHP web script
> > where you've started a transaction (with Begin Work) but haven't explicitly
> > done a Commit Work or Rollback before it ends will lock you out of
> > a table (until you restart postgresql)?
>
> The symptoms you describe sound like multiple transactions trying to
> update the same row.  There is a row-level lock that prevents the later
> ones from completing until the first one commits or aborts.
>
>                         regards, tom lane

On Wed, May 23, 2001 at 11:55:09AM -0400, George Herson wrote:
> But since i made the original post i rebooted my server and now i think
> that (a reboot) was also needed, as a couple of little things outside
> PHP/Apache/Posgresql (in Netscape, actually) are now working that were
> broken pre-boot.
>
> Is it true that when Linux needs a reboot it sometimes isn't obvious?
> That can be very dangerous from a time-spent-on-debugging point of
> view.  Are there any tests (eg, vmstat) that will quickly tell me when a
> reboot is a good idea?  (I'm more used to having to reboot in Windows,
> which of course succeeds in making it fairly obvious when your time is
> up. :)

Linux and most other *nix shouldn't ever "need" a reboot.  Probably all
that was needed was to restart the various services.  If you upgrade
php, for instance, without restarting Apache, Apache will probably still
be referencing the earlier version of the php shared library.  Files aren't
really deleted until their reference count goes to zero.

--
Eric G. Miller <egm2@jps.net>

Problem solved. (My early transactions worked because they were
Inserts.  When they become Updates, this caused an error because of a
subsequent call to pg_fetch_array that didn't make sense.)



thank you,

george