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
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:
Show quoted text
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'texplicitly
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 toisolate
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 aPHP
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
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:
Show quoted text
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>