CR: add support of interactive transactions for webclients

am 14.02.2011 17:29:10 von Herbert Huber

--0-457256283-1297700950=:86299
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

CR: add support of interactive transactions for webclients Hello,=0AI =
dont know how to place an idea (CR) for mySQL.=0AI try it that way. At=
the moment I am implementing an "easy-to-use" multiuser webclient for =0Ad=
atabase usage.=0A(phpMyAdmin in contrast is a very powerful tool for people=
with technical =0Abackground knowledge and=A0I like to use it.) In an=
multiuser environment the usage of transactions to avoid data loss by =0Aa=
ccess conflicts between different users is mandatory. But:=0AWebserver=
(e.g. apache) doesnt keep open the connection to mySQL after the =0Ascript=
(e.g. PHP) has been executed.=0AmySQL thread is terminated=A0and=A0any ope=
n transaction is "rolled back". Idea (CR):=0Aprovide=A0new=A0session-v=
ariable KEEP_PROCESS and new command CHANGE_PROCESS. -----------------=
--------------------------------------=0Ascenario (simplified): user1 =
navigates through database =0ASELECT but NOT=A0"for update" user1 like=
s to "checkout"=A0a row to do some changes:=0A n=0A....=0ASTART TRANSACTION=0ASELECT .... FOR UPDATE=0ASET @@KEEP_PROCESS=
=3D1 // process=A0shall NOT be terminated after con=
nection =0Ahas been closed=0ASELECT CONNECTION_ID()=0Adata is displayed in =
webclient

for editing=0A?> row=A0(InnoDB) is locked for user1 a=
nd cannot be "SELECT .... FOR UPDATE" by any =0Aother user user1 likes=
to write the changes back:=0A E_PROCESS $connectionId =A0// process shall be "overtaken" if a lot of=
=0Aconditions are satisfied (see bellow)=0AUPDATE .....=0ACOMMIT=0ASET @@K=
EEP_PROCESS=3DNULL =A0 // process=A0shall be terminated after co=
nnection has =0Abeen closed=0A?>=0A----------------------------------------=
--------------- conditions for=0ACHANGE_PROCESS $connectionId :=
=0A- user (and host) have to match between currently active (new) process a=
nd (old, =0Akept) process with ID=3D$connectionId=0A- currently active (new=
) process and (old, kept) process have to have =0Asuccessfully finnished us=
er authentification=0A-=A0(original, last) connection of (old, kept) proces=
s has to be terminated before =0Aactive (new) process can overtake if=
(original, last) connection has not been terminated then CHANGE_PROCESS is=
=0Aqueued until (original, last) connection terminates I have b=
een implementing a "workaround" (around 500 lines of code) that =0Aimplemen=
ts the above described behaviour.=0ABut this is implemented in C++ as "PHP =
to MySQL bridge" acting towards PHP as =0A"simulated mySQL server" on port =
3307=0Aand acting towards mySQL as "simulated PHP client" on port 3306.=0A=
=0AThis workaround does satisfy my needs but of course it would be much bet=
ter to =0Ahave this functionality implemented directly in mySQL if ot=
her users need "interactive transactions for webclients" too. Pl=
ease give me feedback. Thank you very much!=0AHerbert =0A =0A____=
____________________________________________________________ _______________=
_____=0ADon't get soaked. Take a quick peek at the forecast=0Awith the Yah=
oo! Search weather shortcut.=0Ahttp://tools.search.yahoo.com/shortcuts/#loc=
_weather
--0-457256283-1297700950=:86299--

Re: CR: add support of interactive transactions for webclients

am 15.02.2011 16:10:24 von Johan De Meersman

--000e0cd1fc6a53d694049c53907e
Content-Type: text/plain; charset=ISO-8859-1

I can't speak for the MySQL people, but in my view your "workaround" is the
correct way of implementing this. It is not the database's job to keep track
of which user wants to keep what session open, and HTTP is stateless by
design. Keeping transactions open for relatively long periods of time would
be a major load on the database, as it needs to keep track of all the
different changesets and consistent views. I'd rather have my database be
good at handling data :-)

I can see your need, but that's what middleware is for.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd1fc6a53d694049c53907e--