List:General Discussion« Previous MessageNext Message »
From:Herbert Huber Date:February 14 2011 4:29pm
Subject:CR: add support of interactive transactions for webclients
View as plain text  
CR: add support of interactive transactions for webclients

Hello,
I dont know how to place an idea (CR) for mySQL.
I try it that way.

At the moment I am implementing an "easy-to-use" multiuser webclient for 
database usage.
(phpMyAdmin in contrast is a very powerful tool for people with technical 
background knowledge and I like to use it.)

In an multiuser environment the usage of transactions to avoid data loss by 
access conflicts between different users is mandatory.

But:
Webserver (e.g. apache) doesnt keep open the connection to mySQL after the 
script (e.g. PHP) has been executed.
mySQL thread is terminated and any open transaction is "rolled back".

Idea (CR):
provide new session-variable KEEP_PROCESS and new command CHANGE_PROCESS.

-------------------------------------------------------
scenario (simplified):

user1 navigates through database 
SELECT but NOT "for update"

user1 likes to "checkout" a row to do some changes:
<?PHP
mySQL authentification
....
START TRANSACTION
SELECT .... FOR UPDATE
SET @@KEEP_PROCESS=1         //
process shall NOT be terminated after connection 
has been closed
SELECT CONNECTION_ID()
data is displayed in webclient <FORM> for editing
?>

row (InnoDB) is locked for user1 and cannot be "SELECT .... FOR UPDATE" by any 
other user

user1 likes to write the changes back:
<?PHP
mySQL authentification
....
CHANGE_PROCESS $connectionId   // process shall be "overtaken" if a lot of

conditions are satisfied (see bellow)
UPDATE .....
COMMIT
SET @@KEEP_PROCESS=NULL      // process shall be terminated
after connection has 
been closed
?>
-------------------------------------------------------



conditions for
CHANGE_PROCESS $connectionId :
- user (and host) have to match between currently active (new) process and (old, 
kept) process with ID=$connectionId
- currently active (new) process and (old, kept) process have to have 
successfully finnished user authentification
- (original, last) connection of (old, kept) process has to be terminated before 
active (new) process can overtake
  if (original, last) connection has not been terminated then CHANGE_PROCESS is 
queued until (original, last) connection terminates



I have been implementing a "workaround" (around 500 lines of code) that 
implements the above described behaviour.
But this is implemented in C++ as "PHP to MySQL bridge" acting towards PHP as 
"simulated mySQL server" on port 3307
and acting towards mySQL as "simulated PHP client" on port 3306.

This workaround does satisfy my needs but of course it would be much better to 
have this functionality implemented directly in mySQL 

if other users need "interactive transactions for webclients" too.



Please give me feedback.

Thank you very much!
Herbert


 
____________________________________________________________________________________
Don't get soaked.  Take a quick peek at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather
Thread
CR: add support of interactive transactions for webclientsHerbert Huber14 Feb
  • Re: CR: add support of interactive transactions for webclientsJohan De Meersman15 Feb