File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes jdbc and Threads...Theory Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "jdbc and Threads...Theory" Watch "jdbc and Threads...Theory" New topic

jdbc and Threads...Theory

Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
hi all:
According to my understanding of Threads and Connection pool, I have this theory, which I don't know if it is correct or not.
Assuming a single proccessor machine, only one thread can be excuted at a time. Threads alternate, but only one thread can be excuted at one time. For simplicity, consider a web application with one servlet. Inside this servlet there is a single method that can access the database.
Here is the quistion:
1- will it be more effecient to creat a single connection object to the poole, in the initilizaion proccess of the application, and leave it open, so all thread can access it sequentially?
2- or, it is more effecient to create a new connection and statement objects inside the method, and then close the connection before the method return?
From my understanding of how Threads work, case one shuold eleminate unnecesarry creation of objects, every time a thread ask to access this method, and overall should be better performace.
I did a test on both cases, and I was surprised that case number 2 won. The time needed to create about 5000 connection in 5 threads was much less than in case number 1.
any help in uderstanding what is going on or ideas is appreciated.

SCJD 1.4<br />SCJP 1.4<br />-----------------------------------<br />"With regard to excellence, it is not enough to know, but we must try to have and use it.<br />" Aristotle
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
There's just one problem with your theory. It is true that only one thread can REALLY be running at any given time on a single-processor machine. However, the threads are not allowed to run all the way to completion when they DO get the processor. They are each given a chunk of time to do some work, and you do not have any control over WHEN your thread will lose the processor and another will take over. Consider the following scenario where two different threads share the same Connection object (and thus the same transaction)...
Thread 1 debits a bank account.
Thread 2 rolls back the current transaction.
Thread 1 credits another bank account.
Thread 1 commits the current transaction.
What Thread 1 wanted to do was a transfer from one account to another within one transaction. What actually happened, since Thread 2 rolled back the transaction without Thread 1 knowing it, was simply a credit to a bank account.

James Carman, President<br />Carman Consulting, Inc.
I agree. Here's the link:
subject: jdbc and Threads...Theory
It's not a secret anymore!