Mindscape Blog » Blog Archive » Database connection strategies in LightSpeed 3

Database connection strategies in LightSpeed 3

In LightSpeed 2, a unit of work is bound pretty tightly to a database connection. A unit of work opens a database connection the first time it needs to talk to the database, and keeps it open until the unit of work is disposed.

This is a simple, efficient pattern for short-lived units of work, which is the pattern we encourage. For example, in a Web application, a unit of work lasts the duration of one HTTP request, so there’s no point trying to micro-optimise the connection lifecycle any further.

However, in a rich client application, the user may build up elements of the business transaction over a longer period. For example, the application design may require an edit window to remain open for minutes or even hours; and all of the activity in that edit window remains part of a unit of work, a logical business transaction, a changeset awaiting a commit. In this scenario, holding the database connection open for those minutes or hours isn’t such a great idea. With many users, you could starve the database of connections. And if there’s a network error and the connection gets broken, the unit of work is stuck with a bad connection and won’t be able to save the user’s work.

To get around this, LightSpeed 3 introduces the idea of a connection strategy. The connection strategy’s job is to provide a connection whenever the unit of work needs to talk to the database. The default connection strategy, therefore, creates a connection the first time the unit of work asks for it, and then just keeps returning the same connection object. The net result is as in LightSpeed 2: the connection is held alive and open until the unit of work is disposed.

But if you want different behaviour, you can provide your own connection strategy instead. In the rich client example, you could build a “closeable connection” strategy, which created a connection the first time the unit of work asked for it, and then kept returning the same connection object, but provided another method through which the application could close the connection — and if the unit of work asked again after the application closed the connection, would create another new connection. A still smarter strategy would also check for connection errors and recreate the connection if required.

Some care is required when using custom connection strategies. For example, if you’re doing work in a transaction, then closing a connection and opening a new one is unlikely to win friends or influence people. For more information about connection strategy usage considerations, and example code for a simple rich-client-friendly connection strategy, see this forums post.

Want to try it out? The LightSpeed 3 beta is available for download from the store today.