How to manage db connection especially in case of multi threading

Question

I am working on an online judge.I am using python 2.7 and Mysql ( as I am working on back end-part)

My Method:

I create a main thread which pulls out submissions from database( 10 at a time) and puts them in a queue.Then I have multiple threads that take submissions from queue, evaluate it and write the result back to database.

Now I have some doubts(I know they are doubts from different topics but approach to some of them also is highly appreciated).

1. Currently when I start the threads I give them their own db connections, Which they use.Is this a good practice to give one connection per thread. Does sharing of connections between threads create problems.How do I go about this.
2. My main thread uses a single connection as its only work is to pull submissions from db and put then in queue(also update their status in db to Assessing Submission). But sometimes I get the error: Lost connection to Mysql server while querying. I keep getting it even when I stop the program and start it again.What do I do about it? Also should I implement a Pool of connections for only the main thread?
3. Also does a db connection stay alive for ever? What to do when its session memory etc gets exhausted how to handle that?

Answer 1

1. Use a connection pool. Sharing the database connection is not always bad but you have to be careful about it. You can try SQLAlchemy to manage a lot of this for you: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#unitofwork-contextual

2. The server might be out of connections, your connection might have been killed because it uses too many resources.. etc. A connection pool could help you solve this.

3. It all depends, it could stay alive indefinitely theoretically, but usually you have a timeout somewhere.

Answer 2

1. If you give the same connection to every thread then the threads will not be able to query the database and race condition will occur. So you need to provide separate connection to every thread and indeed it is a good idea. Use a Connection Pool for the purpose it will help you get different connections.

2. Connection Pool will surely help.

3. Release the connection once your work is over. There is a limit to connection which is termed as connection time out. So you need to use some third party library to handle that, c3p0 is a good library which can help you in this.

Please refer the below link to configure it:

Best configuration of c3p0