Myles Wakeham wrote:

>> The current software holds the transaction over several actions, and
> only if all can be completed does it commit. Moving this to PHP is
> giving me a headache since up until now I've not had to do anything
> that
> goes over a single page load.
>
>
> I suspect this isn't a problem that is isolated solely to Firebird but to
> any database associated with web access. The concept of non-persistence of
> the web vs. the idea of persistence that a client/server form would
> traditionally have is definitely where the issue is. And its something that
> all of us doing larger scale, transactional PHP applications, have to deal
> with.
>
> What I do is to manually develop a table that contains those sorts of
> pending updates and have the code work with those tables before allowing a
> user to modify, or handle anything that could result in a conflict state
> with the database. Its not a perfect solution, but it allows me to have
> some form of 'undo' control over updates if a user elects to not commit
> them, or if I have to do something in some partial update mode that spans
> multiple pages, etc.
>
> I use a 'soft locks' table concept when I have a user enter a record to
> modify that means all other users cannot touch that record until the user
> commits their changes. Normally you'd have the database take care of this
> sort of thing, but in the case of a non-persistent connection such as the
> web, the database will never know if the user is working on the data, gone
> off to lunch, crashed their computer, or migrated from the page site to
> Yahoo or whatever. Therefore the general problem is that if you have some
> form of temporary tables used to handle some form of soft-locking facility,
> you also have to have some form of 'cronjob' type script that elects to
> timeout the locks so that they eventually die and the record can then be
> allowed to be modified by other users again.
>
> The other way might be to have some code running on the web page (ie. Such
> as Ajax style code) that is running continuously during the data update, and
> that it communicates periodically back with the web server while 'alive'.
> When the web server doesn't hear from the 'alive client' it could make an
> assumption that the user has gone away and the updates could be rolled back
> if not completed.
>
> Could Firebird be made to address this? Probably. But unlike its
> client/server counterparts, some form of 'time out' mechanism would have to
> be implemented since users who don't return back to a pending update would
> only be known to have done this after a period of time had elapsed.

I think that sums it up nicely!

My Client Management System suffers from a little niggle where staff do
not complete activity and so tickets get left open on terminals until
someone else clears them. Adding a 'timeout' is a little difficult since
complex interviews CAN take 2 hours, but the average is more like 15
minutes. But that system works nicely 'transaction at a time'. It's the
more complex 'stock movement' transactions that work nicely
client/server, but need to be attacked differently when using web based
access. Probably one of the reasons temporary tables are needed in a
database, but as you say - the problem is tidying up the unfinished work.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php