* Hassan Schroeder
> Roger Baklund wrote:
>
> > Ouch. Very special, imo... this means the connection is
> > re-negotiated for each statement? Sounds strange. Maybe the
> > client connection is 'virtual' or 'by proxy', so that multiple
> > users actually share the same connection at the same time?
>
> If you want to think of it that way, yes -- there is *one* user:
> Tomcat. It "proxies", if you like, all requests from *end users*
> through its connection pool to the MySQL server.
Yes, I got that part. :)
When you have a car pool, it means a small number of cars can be used by a
greater number of drivers, but of course not at the same time. In this
context "same time" means within the time it takes to do one "trip". It
would not be usefull if each driver can only drive 5 meters, then a
different driver can drive the car for 5 meters and so on. It is still car
pooling, but it is not usefull. It is more usefull if one driver can take
one "trip" and be done with it, then the next driver can have one "trip" and
so on.
ok, that was a silly metaphor, but I am having problems explaining how I
think a connection pool should be implemented. There are probably thousands
of different connection pools out there with many different implementations.
I'm not saying my way is the only way. Sometimes it may be usefull to have
each user drive only 5 meters, I don't know when, but it's usually not when
using mysql. :)
> > Sounds like it by your description. When you say user #1 and user
> > #2, you really mean session #1 and session #2, right? (In this
> > context, one click from a user is one session, usually containing
> > multiple queries against the mysql server.)
>
> I think we have a terminology disconnect :-)
ouch. new handshake. helo. ;)
> To me a session is
> an HTTP session, managed by the servlet container. The point of
> creating the temp table was to place it in session scope, to save
> customer selections for the life of that HTTP session...
I see. I'm thinking database session, as seen from the mysql server.
To me a HTTP session is a click. :) What you call a HTTP session is what I
would call a web session, a series of user interactions (clicks) with your
web- (or application-) server. A virtual session is maintained by the web
server using an id in the URL or a cookie or possibly some other HTTP
header.
For each _click_ do all queries come together in the query log? If so, you
have a "good" connection pooling implementation.
I have never used servlets, there may be something about them that makes my
line of thinking wrong... will a web page typically contain multiple
servlets? and will all (or more than one) have a separate "Connection conn =
ds.getConnection()" or similar? In that case there is a many to one relation
between the click and the mysql server, as opposed to the "normal" one to
one relation. If so this could explain some of the confusion. :)
In a "normal" (I know, what is "normal" to me is not necessarily the common
case for you) situation a user click on a web page will trigger _one_ "conn
= connect()" statement (connection pooling or not), and all consecutive
statements resulting from the same user click will be done using the same
"conn" connection construct, usually representing a physical (tcp) database
connection. In such a situation any multi-statement functionality
(LAST_INSERT_ID(), user variables, temporary tables and transactions) will
work as expected and intended. In this case we have an _isolation_,
different user sessions (in this case "sessions" means clicks on a web page)
are kept separate from each other.
A connection pooling implementation without this isolation, where multiple
users can share the same database connection at the "same time" is either
broken or intended for a special purpose (some non-multi-statement purpose).
imnsho.
> But regardless, in that case I was referring to the requests of
> discrete end users -- make that "end user 1" , "end user 2".
This doesn't tell me much... :) If each _click_ involves only one query,
that would be a normal log also for a "good" connection pool implementation.
Forget about sessions then. Think user clicks (including pressing ENTER). If
a user click involves multiple queries, each query should be executed in the
same connection, and no other user should use the connection at the same
time. After the last query related to the same click, the same connection
may be used by another user, handling _all_ queries of that user-click, and
so on.
If you wanted a temporary table to survive through multiple user clicks, for
the lifetime of what I call a "web session" and you call a "HTTP session",
you would need a special type of connection pooling, where you can "lease" a
physical database connection and keep it through multiple clicks (multiple
"connect()/action/close()" sequences). You seem to experience the _opposite_
with dbcp: you can't even isolate a single "connect()/action/close()"
sequence. ("action" in this case means any number of any type of database
queries).
> > How does transactions work in such an environment? Can they be
> > used at all?
>
> Dunno. Haven't had a need.
If you think about it... how would it work? I'm fairly sure it wouldn't. And
I find it strange, if not unbelievable, if a Jakarta project has
ignored/broken transaction support in database connection pooling.
> > I don't know dbcp, but being under the wings of Jakarta I would expect a
> > certain level of quality... I don't have any experience with
> Tomcat either,
>
> Well, that could explain why this seems unfamiliar :-)
I am talking about connection pooling in general. It is possible that Tomcat
with dbcp sucks, and it is possible that it works just fine. I would say it
sucks if it breaks LAST_INSERT_ID(), user variables, temporary tables and
transactions. I believe it works, I just haven't yet figured out how. It is
not very important to me, as I don't use neither Tomcat nor dbcp, but I am
curious. :)
> Do the Web application environments you've used handle connection
> pooling differently, then?
:)
For web applications I have mostly used Python and PHP, I don't use any web
application environment anyone have ever heard of, and I have never used
connection pooling with mysql. :)
Common sense tells me that connection pooling should work in such a way that
the service being pooled is not broken.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
