MySQL Forums :: Transactions :: CR: add support of interactive transactions for webclients

Thank you for the warning "Deadlocks will kill you."

First I have to say that my application is not intended to be accessed by www from unknown count of unknown users all over the world. (as is the intention of the majority of web-applications).

My application is intended to be used for "database content maintenance" by a limited count of employees of a small or medium sized company.
(PHPmyAdmin is useful for administration but too complex for non-experts that only perform "database content maintenance")
I could implement the "database content maintenance"-Client even with a different technology (e.g. windows executable based on C++ sourcecode) but then I cannot take advantage of Web-Technology (easy access from different company locations, access from different machines with different operating systems, etc.)


Users who never get to the COMMIT will tie up resources and lock rows/tables.
>> Yes, thats an big issue in applications with unlimited unknown users from www.
>> In case of limited count of employees its reasonable that system-administrator has to kill the MySQL process if the employee never gets to the COMMIT (e.g. because of technical reason or suffer from illness or quits the job immediately)

Do not try to implement "transactions" using START...COMMIT across long-running, stateless HTTP protocol.
There is a timeout (defaults to 50 seconds) that will kill you.
>> Yes I did run into trouble with that
>> Now I use the middleware to keep connection to MySQL
>> and now I always close HTTP immediately after data transfer between webserver and webclient
- only the web-session I keep open to store MySQL connectionId,...

Network glitches will cause spurious disconnects, which will kill transactions. (This can happen now, but much less frequently, because transactions are much shorter-lived.)
>> Webserver, middleware (for keeping connection to MySQL) and MySQL server in my configuration run at the same machine

You need to implement statefulness and transactions in your application, and use extra tables to hold the information needed for rollback, blocking access, etc.
>> I am a lazy guy and I know that correctly implementing all the stuff around rollback, blocking access, etc. is a really hard job.
I dont like to implement this another time if MySQL server (InnoDB engine) still provides this functionality.
>> There are two session-variables in MySQL that make me optimistic:
INTERACTIVE_TIMEOUT (default 28800 seconds)
WAIT_TIMEOUT (default 28800 seconds)
>> I think in former times somebody had the idea of interactive usage of MySQL
>> The new challenge is the combination with Web-Technology