Re: Default Connection Pooling

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Aug 9, 2005, at 20:45, [email protected] 
wrote:

> Date: Tue, 9 Aug 2005 08:48:02 -0400
> From: Jacob Smullyan <[email protected]>
> To: Shibu Chacko Manalil <[email protected]>
> Cc: [email protected]
> Subject: [Skunkweb-list] Re: Default Connection Pooling
>
>
> ...
>
> Without seeking to understand the full context of your application, I
> would suppose that by setting max_poolsize very low and refreshing the
> browser 1+max_poolsize times, you are sometimes getting connection
> reuse because the older thread has given up the old connection, and
> sometimes not, and as a result a new connection has to be created.  I
> suggest that in every request in which you get a connection you
> dispose of it, either implicitly by calling commit() or rollback()
> somewhere, or explicitly; waiting for it to be garbage-collected may
> not be fast enough in this situation.  Of course, you could also be
> sloppy and simply turn max_poolsize way up (or off) -- 4 does seem
> awfully low anyway -- and garbage collection would periodically clean
> out old connections and send them either to the pool or to perdition.

Does this mean that one cannot call commit() or rollback() more than 
once on a given connection?

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-----



-------------------------------------------------------
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