Re: Default Connection Pooling

On Sat, Aug 13, 2005 at 07:43:36AM -0700, Matthew Bogosian wrote:
> Does this mean that one cannot call commit() or rollback() more than 
> once on a given connection?

If you've done so, why do you care whether the connection you have
after committing or rolling back is the same connection, or a
different one?  The transaction is over.  For it to matter, the
connection would need to have some state that you want to preserve
across transaction boundaries.  I don't see anything in your example
that would be a problem.  

The dbi object's "conn" attribute auto-vivifies.  If the connection is
returned to the pool, the next time you need it another equivalent one
is there.

With mysql, if insert_id() is connection-specific state, that would
break with autocommit (which I still haven't had time to fix, sorry)
and maybe even without it, if you tried to call insert_id() after a
commit:

   o=obj.new()
   o.commit()
   # what happens?
   print o.getDBI().conn.insert_id()

j


> 
> What about (real-world) situations like this:
> 
> 	...
> 
> 	MAX_TRIES = 3
> 
> 	...
> 
> 	# Try to update the shopping cart and track other
> 	# customer data. This potentially involves several
> 	# inserts and updates involving rows which could be
> 	# used concurrently by other requests. Since our
> 	# engineers aren't perfect, there is potential for
> 	# a deadlock here, in which case we want to log it
> 	# (so we can eventually fix it), but we want to try
> 	# again a few times so as not to unnecessarily
> 	# penalize the end user.
> 	success = False
> 
> 	for i in xrange(MAX_TRIES):
> 		try:
> 			# Try to update the shopping cart, and keep
> 			# track of aggregate shopping habit data so
> 			# we can make better product
> 			# recommendations to the next customer
> 			updateShoppingCart(db, post_data)
> 
> 			# We were successful; commit the
> 			# transaction and stop trying
> 			success = True
> 			db.commit()
> 			break
> 		except deadlock_error:
> 			# Oops, our fears were justified; log
> 			# whatever pertinent information we have,
> 			# rollback the current transaction (because
> 			# we're not sure how far it got), and let
> 			# the outer for loop try again until it
> 			# gets tired
> 			log.warn('deadlock error encountered')
> 			log.warn(...) # Details of transaction
> 			db.rollback()
> 
> 	...
> 
> The above example could have 0 to MAX_TRIES - 1 rollbacks followed by a 
> rollback or commit, all on the same connection. Do behaviors like this 
> not play well with connection pooling? If not, it seems like 
> connections should be returned to the pool on a close() (or similar) 
> method rather than a transactional boundary method.
> 
> 	-- Matt
> 
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.0 (Darwin)
> 
> iD8DBQFC/gcenLpDzL5I7l8RAn4wAJwMueczi60CzF71XTTnMjUZKbrcugCfR5dI
> uQe//vJJSGT2hsMYmDEZCz4=
> =4RLr
> -----END PGP SIGNATURE-----
> 

-- 
Jacob Smullyan


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf