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
