This topic contains 5 replies, has 4 voices, and was last updated by  Julian Muir 2 years, 8 months ago.

retry() and closing database connections

  • In the sample code for retrying a database, http://wiki.interfaceware.com/482.html there is no explicit call to close the database connection.

    Is that by design, or would calling conn:close() in DoInsert() be best practice?

    Thanks.
    Dominic

    I think that article was written before the new connection orientated interface was put in place.

    I guess there are pros and cons. Eventually the garbage collector should close the connection out.

    For some databases there is overhead in opening a connection.

    I’d suggest putting the conn:close() in place and seeing if there are any negative impacts on performance. If it is fine then leave it in place.

    Some databases can get unhappy if there are too many open connections.

    I noticed the article also shows doing a LLP client connection – for an LLP connection it will vary considerably depending on the nature of the receiving system and whatever foibles and bugs it has. Some receiving systems don’t work well if you have persistent LLP connections – some don’t work well if you disconnect all the time.

    In our LLP client we support configuration of a variety of different ways because of the variability of what receiving systems require.

    Your mileage may vary.

    Is that by design, or would calling conn:close() in DoInsert() be best practice?

    Well, calling conn:close() in DoInsert itself is probably not what you want, as then the connection would get closed before you can retry.

    However, a reasonable default approach is to tweak that code to the following:
    require ‘retry’

    local function DoInsert(args)
    return args.conn:query(args.sql)
    end

    function main(Data)
       local conn = db.connect{ api = ... }
       retry.call{func=DoInsert, arg1={conn = conn, sql = 'Hello'}, retry=1000, pause=10}
       conn:close()
    end

    Then you don’t have any connections hanging around that you’re not using at the moment. However, if the overhead of opening up new connections each iteration through the channel is a factor, then you can define the connection globally (as on the wiki page) and not close it explicitly. Though you would then want to make sure that your connection is still live (and re-connect if it isn’t).

    OK, thanks for the responses!

    The page for the retry module http://help.interfaceware.com/kb/482 has been updated.

    An explicit call to close the database was added to DoInsert(), see http://help.interfaceware.com/kb/482/2

    Also using a error function to to handle only lost connections, and a more complex error function that handles other errors as well, see http://help.interfaceware.com/kb/482/5

You must be logged in to reply to this topic.