• 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

Clarification

 
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am not sure if this is the right forum, but since it falls within the perview of J2EE application i've posted it here.Please re-direct if otherwise.

In a web-based application, when we create a connection object inside a static method, what are the implications. For instance if the method does the following


1) Create connection
2) Create temp table
3) Insert into temp table
4) Call SP
5) Return from SP
6) Close connection


Is this code fool-proof against synchronization - are there chances of 2 threads using the same connection object and hence the same temp tables ? Is there a possibility of multiple temp tables being created ?
 
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Subramanian,


Is this code fool-proof against synchronization


As long as there are no instance or global variables involved and the connection object is created/destroyed within this method�s body then I would answer yes the code is thread safe.


are there chances of 2 threads using the same connection object


Again if the connection object is instantiated within the method�s body then the answer is no; it is kind of auto variable and it�s local to that method. If on the other hand the connection is retrieved from a pool then probably the pool itself is synchronized and again the threads won�t share the same connection instance.


and hence the same temp tables ?


This is little bit different and doesn�t have much to do with thread synchronization. The database is a global resource by itself and two different client threads could access the same table through two different connections. Coding your method thread safe won�t prevent this from happening. It has more to do with the transaction isolation and the way the database handle the data concurrency. However what I imagine is that your code creates different temporary tables for each thread. I would imagine that somehow your code construct unique names for these tables and the question would be what strategy one needs in order to come up with such unique names. I won�t make any more suppositions unless you�ll clarify this problem little bit.


Is there a possibility of multiple temp tables being created ?


Every call to this method will try create/override the existing temp tables. The only way to prevent that is to use unique names for these temporary tables. But again I believe that I do probably not understand you exactly here. Please try to explain little bit more what you�re trying to achieve.
Regards.
 
PNS Subramanian
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Appreciate your detailed reply.

Here i am actually talking about temporary tables - whose life time is typically the life time of the connection object.


"Two different client threads could access the same table through two different connections. ."


Theoretically, i suppose the above should not happen since each Connection object (each session) would have its own temp tables.


Every call to this method will try create/override the existing temp tables.


Won't the temp tables be dropped when the connection object is closed at the end of the method ?



Every call to this method will try create/override the existing temp tables.


Arent these temporary tables created in different sessions (connection objects) ?

As stated earlier the temp table gets created after the connection object - i.e scope is restricted to that of the life time of a connection object. Due to some reason we find multiple temp tables getting created after the request gets processed i.e control comes out of the method, which i feel shouldn't be happening considering that the connection object is closed within a finally block. The issue here is one of temp tables not getting dropped.

I hope i've been able to make the problem more explicit.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just to be sure: you are talking about code in an EJB or called by an EJB?
 
PNS Subramanian
Ranch Hand
Posts: 150
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is about code that gets called from POJO. There is no EJB involved here. Its a Java to backend SP call.
 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by PNS Subramanian:
The issue here is one of temp tables not getting dropped.



In the operations flow you don't mention that the temp table is deleted or the transaction is rolled back.
 
Valentin Tanase
Ranch Hand
Posts: 704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Now I understand what you�re doing. I did not use temporary tables that often and I cannot tell you all the implications, but from what I�m understanding there are a number of restrictions related to temporary tables, which are version specific. On the other hand you right, they are specific to that database session and the tables should be removed after closing the connection. Hypothetically there is one scenario when all this might not work and I cannot say for sure whether it is true or not but is worth trying. Usually the containers provide connection pooling services and therefore closing a connection will actually return the connection to the pool and will not physically close the connection or release the database resources. I would imagine that this could impact the way temporary tables are automatically removed. I wouldn�t be surprised to see that the code works pretty well outside of the container though, but again I might be wrong.
Regards.
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

This is about code that gets called from POJO. There is no EJB involved here. Its a Java to backend SP call.


But this is an EJB forum.

However, we'll try and help. Note that closing a connection will not cause that connection to be returned to the pool until the transaction ends. How are you demarcating your transactions?
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic