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
