Q: PHP and transactions with PostgreSQL

huhu

Hi all!

I have following concurrency problem.
I have two files: one html-file which displayes data in a form which can be changed by the user by pressing the submit-button.
after submitting the form the second file (php-file) writes the changes to the postgres-database.
The problem: the data displayed in the html-file is accessible by several users. If user A is currently watching data, user B should not be able to watch the same data, cause user A could change it with a submit and user B would see old data.
I need transactions which begin in the html-file and lock the data there and then are commited in the php-file. I dont know, how to increase the lifetime of a transaction from the html-file to the php-file.
Any suggestions?

Thanks in advance.
Hüseyin

ultraslacker

php is dicey for this - you are asking for trouble here if more than a handful are using the pages.

You will have to maintain the session between pages - html (with some php) page will start transaction at mode access restricted.

Page 1 (HTML + PHP)
BEGIN WORK;
-- the transaction mode is a question here - I'm not sure what you want. Read the LOCK and SET TRANSACTION sections in postgres manual.

Page 2 (PHP)
COMMIT or ROLLBACK WORK;

niyogi

you could use a workaround solution for this.

Have a session table that keeps an active logged of a user thats currently logged in. When another user attempts to update a particular table in the PostgreSQL database, first check the session table to find out whether a user is currently logged in.

If you want to go crazier, you could tie this to a cron job and make it perfect.

Just a thought..(although using transactions would be better though).

If all else fails, you can post to the PostgreSQL mailing lists since those guys are PostgreSQL pros like no tomorrow. 🙂

-niyogi
PostgreSQL Web Hosting
http://www.pghoster.com

huhu

thx for your replies, i am trying to solve my concurrency problem with transaction, without success till now.
i began a session as suggested by ultraslacker, but without success.
ill post the relevant code pieces, maybe its more clear than what i want to do and do wrong.

file
----------------------------------------------
<?php
session_start();

$verbindung = pg_pconnect("host=$host port=$port user=$user password=$password dbname=$dbname");

$queryString = "BEGIN WORK; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE";
$ergebnisNr = pg_exec ($verbindung, $queryString);

$queryString = "LOCK medium IN ROW EXCLUSIVE MODE";
$ergebnisNr = pg_exec ($verbindung, $queryString);

$queryString = "select * from medium where id=$medienid";
$ergebnisNr = pg_exec ($verbindung, $queryString);
?>

<html>
...
<form>
DISPLAY data selected from medium which is can be changed by a submit from the second file
</form>
...
</html>

2.file - This file gets the data of the form in the 1.file and changes it

<?php
session_start();
$verbindung = pg_Connect("host=$host port=$port user=$user password=$password dbname=$dbname");

$queryString = "update medium set typ='$medientyp', titel='$titel', author='$author', schlagworte='$schlagworte', erscheinungsjahr='$erscheinungsjahr', kommentar='$kommentar', zusammenfassung='$zusammenfassung' where id='$medienid'";
$ergebnisNr = pg_exec ($verbindung, $queryString);

$queryString = "commit work";
$ergebnisNr = pg_exec ($verbindung, $queryString);

Do you know why this doesnt prevent concurrent updates?
2 User begin to read 1.file and see the same data, the first user alters the data and the data of the second user is dirty then and then the second user is able to alter the data too so the changes of the first user are lost - what i tried to prevent

Thx in advance