Keep Database Connection Alive

The following is a typical flow of program that process data in database:
1. Open database connection(s).
2. Query records
3. Process records of 1 of the connection
4. Update database
5. Close database connection(s).

Problem will occurs when step 3 take too long time. Then when come to step 4, you will found that database connection open in step 1 already not there due to connection timeout.

There is basically 2 solutions I had found for this. The first one is simple, just make sure the connection is only open before it was needed and close immediately after it was used. So it will be as below:
1. Open database connection(s).
2. Query records.
3. Close database all connection(s) that is not used in step 4.
4. Process records of 1 of the connection.
5. Open database connection(s).
6. Update database.
7. Close database connection(s).

This will work fine if there just few time the database connection will be idle. For example, in solution 1 step 4 the connection will idle, so it can be close and open again once needed. But when scenario of steps 4 happen a lots of time in a single program, solution will take lots of resources for repeatingly open and close connection.

So here come solution 2. It is simple, just let all others connection that suppose to be idle to perform some simple query to ensure these connection do not timeout. You will think, it is troublesome to think of a simple query for each idle connection, because each connection connect to different database. My answer is: just query on database current time.

Hope to hear from you if you have other solutions. :D