1

Im developing a multi-threaded application in which different threads are required to update the database concurrently. Hence,i passed a new statement object to each thread, while creating it(to avoid locking,if i send a single object). My doubts are :

  1. Is there a limit on the number of statement objects that could be obtained from a single jdbc connection ? would the database connection fail if i create too many statement objects ?

  2. If i close the statement properly before the thread dies out,what would be the number of threads that can be spawned at a time (on a system with 512Mb RAM) ?

  3. Wouldn't the driver update the database while keeping the data consistent,no matter how many statement objects i use to update the db parallelly ? i use mysql.

Improve this question
2
  • 2
    It might be prudent to not share the single connection with multiple threads. That seems to be asking for trouble, for example with transaction boundaries. Can you use a connection pool with more than one connection? Commented Jul 8, 2011 at 11:19
  • i may look into it.Which one would you recommend ? dbcp /bonecp/c3po ? Commented Jul 9, 2011 at 10:51

3 Answers 3

Reset to default
1
  1. Practically the number of statement objects you would be able to create should suffice your needs. Then again, how much is "too many" in your case?
  2. The number of threads that can be created depends on a lot of factors. Do realize that these threads you create would be "OS level" threads and not real threads (assuming you have a dual core system, that would make it 2 hardware threads or 4 if hyper-threading is available). Profiling you would be of prime importance here to determine how many threads can be created before your system slows down to a crawl.
  3. This would depend on the locking mechanism used by the database. What are you aiming for; high integrity or high performance? Read this.

IMO, you would be better off looking up Connection objects from a connection pool in each of those threads rather than trying to pass around "statement" objects.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

1. about 100~200 ? 2. On a typical dual core server, how many threads would be right ? threads are lightweight, doing stuffs like updating a table and passing some data to a server. 3. im trying to strike the sweet spot between high performance and maximum integrity. any help is appreciated :)
I need more context to give out concrete answers. What kind of application is this? Is it a server application or a client side UI which accesses the database? Also, striking a sweet spot for a given system would involve a bit of trial and research. I'm sure no one here can give an answer for that.
it is a server application doing a little bit of db update.like updating a row in a db,process it and update the row again.
1

Although I am not a java programmer, sharing a single connection between multiple threads is a bad idea. What happens when 2 threads are trying to write on the same socket? - so - each thread must have its own db connection

Yes, the data should be consistent in the DB if many threads are writing at the same time - anyway, you will have to take care in code of managing the transactions correctly - and of course, use InnoDB as the storage engine for MySQL because MyISAM does not permit transactions

Improve this answer

6 Comments

+1. Do not share Connections among threads. See also: stackoverflow.com/questions/1182163/multithreaded-jdbc
actually, connections are supposed to be thread-safe. not sure where you are getting this idea from.
JDBC and Multithreading "The Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol."
@Tudor - exactly my point. sharing will work just fine, it just may not be the most efficient. your answer makes it sound like it will not work correctly.
well - that (working correctly) depends on the implementation of the threads access policy I guess
|
0
  1. that's probably up to the jdbc implementation, but, in general, just about everything has limits.
  2. who knows. in practice, probably thousands. however, that many probably won't increase your performance.
  3. yes, you should be able to share 1 connection across multiple threads, however, many jdbc implementations perform poorly in this scenario. better to have a connection per thread (for some reasonable number of connections/threads).
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.