* Hassan Schroeder > Roger Baklund wrote: > > > ok... If you did this: > > > > - create temp table > > - use temp table > > - drop temp table > > > > for each session, I would expect it to work. (atleast until a session > > crashes before it drops the temp table.) > > This is straying from the original question :-) but my understanding > of temp tables is that each "user" can create a table with the same > name, and that table is visible *only* to that user, and disappears > at session end. But with connection pooling, the temp table belongs > to the connection, not the end user. So the next user to "create" > one doesn't actually get a new, unique table.
Well, if the connection pooling implementation handles separate sessions and you create/use/drop the temp table for each session, it should work, right? If the connection pooling implementation does _not_ handle sessions, however... > >>OK, I'd say that answers it: I can watch (tail -f) my query log and > >>see *one* MySQL connection id handle requests from different users > >>on different systems. Or am I still misinterpreting something? > > > > No, that sounds right. I would expect all queries from one user (=one > > session) coming together, then all queries from the next > > session and so on. This is not the case with Tomcat? > > No. The same process handles a query from user #1, then one from > user #2, then another from user #1, and so on. 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? 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.) How does transactions work in such an environment? Can they be used at all? > Hence my impression > that requests are interleaved, and LAST_INSERT_ID() isn't really > guaranteed in this circumstance. > > That's using Tomcat 4.1.x + Jakarta commons-dbcp pooling, BTW. 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, there could be something special that makes this a usefull design... however it breaks LAST_INSERT_ID(), user variables, temporary tables and transactions, so I guess I must be missing something. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]