2

I have a java application with multiple thread started and they are all sharing a single db connection created in the main function. So far things have been running smoothly. I am worried will there be any problem in future like corruption etc?

user111196
  • 1,257
  • 2
  • 13
  • 14

1 Answers1

2

You will have problem if:
- you use transactions. Two threads starting transactions on the same connection would not be nice for your datas. Think about what mysql will do if one thread want a rollback
- you use auto increment and LAST_INSERT_ID. If two threads work on the same table at the same moment, the last id is the same for both if they share the same connection
- and maybe much more problems if you don't only use "simple" things (aka select/insert/update)

Gregory MOUSSAT
  • 1,673
  • 2
  • 25
  • 50
  • thank you for the input yes each of my thread is using one transaction and sure enough I am using last_insert_id too. So what is your best suggestion then use individual connection is it but that will end up with too many connection problems right? – user111196 Mar 11 '12 at 04:55
  • so far I have been experimenting with 20 thread looks ok on the last_insert_id not much conflicts? But do you expect in future there might be a problem is it? – user111196 Mar 11 '12 at 05:07
  • I just saw your comments. You can use really lots of connections before this will be a problem. Just adjust mysql config file in case you have really too many, but in this case you'll have a big server so no problem at all. – Gregory MOUSSAT May 23 '12 at 02:25