• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how we should return a connection to connectio pool

 
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi
How i should return a connection to connection pool ?
I get connections from a data source and then when i finish my work with them i close them using con.close() , will this method return the connection to the pool or i should do something else to return in to the pool.



thanks
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by raminaa niilian:
Hi
How i should return a connection to connection pool ?
I get connections from a data source and then when i finish my work with them i close them using con.close() , will this method return the connection to the pool or i should do something else to return in to the pool.

thanks



It depends on how the connection pool is implemented; you will need to read the documentation for your pool.

Many poolers will hand you a wrapped connection object, such that calling close() will return it to the pool; a few poolers will give you a bare connection and calling close() will actually close the real connection and "leak" the connection from the pool, in those types of poolers, there is often something like Pool.return(aConnection). The former style is very popular, among other things it allows code to be transparently moved from a non-pooled environment to a pooled environment. However, I intensely dislike that style, personally, because there are significant changes to side-effects between closing a connection and returning it to a pool. Code that works well when the connections are closed may break when connections are pooled.
 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
please can you explain the side effects in that ?i really want to know,do the closing of the connection and returing it to pool has some sideeffetcs?
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by swapnil paranjape:
please can you explain the side effects in that ?i really want to know,do the closing of the connection and returing it to pool has some sideeffetcs?



For example, the Oracle "ALTER SESSION" statements can:
  • change your current schema
  • change the transactionality of your connection from serializable to read committed or vice versa
  • change the default date format, which therefore changes the allowable format for implicit string to date conversions
  • change your sort order


  • With the change of the default date format, the SQL statement:
    would work with a connection that had been created directly, but if a connection retrieved from a pool had had ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd'; applied to it before being returned, then the SQL statement will fail. This type of problem can be excruciatingly hard to debug because the error will be intermittent and the code unit will always work correctly in isolation.

    Using one type of pooler or the other doesn't really solve the problem; the issue of side-effects can occur with either style. Howver, a developer who is reminded that the code is targeted at a pooled environment is going to more likely remember that statements that alter an aspect of the connection will need to be undone when the alteration is no longer needed; totally hiding the possibility of pooling, by using close() to return the connection to the pool, hides that reminder.
    [ May 04, 2006: Message edited by: stu derby ]
     
    Don't get me started about those stupid light bulbs.
    reply
      Bookmark Topic Watch Topic
    • New Topic