Home » Blog

Database connection strategies in LightSpeed 3

Tagged as LightSpeed

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.

Add your comments
Posted by Ivan Towlson on 26 October 2009

2 Responses to “Database connection strategies in LightSpeed 3”

1. Thanks for the guidance…could be helpful for apps w/ multiple UI clients.

   Eric Swann Oct 27th, 2009

2. After finding this post, one of my biggest fear suddenly disappeared. I have a rich application using Windows Forms and I don’t use pattern like MVC, MVVM or any other patterns that can gives me nice control over my boudaries when creating and disposing UnitOfWork. For this reason, I have to create a lot of small UnitOfWork that create a lot of connection to the database because each UnitOfWork create his own personnal connection. With the ConnectionStrategy, I can now use my central connection to be use be all of my UnitOfWork.

   I have read a lot about how I can use UnitOfWork in rich application using Windows Forms but none of them works for me. I can create a UnitOfWork for each form open in my application because user can open dozen of them if he want. And if I open and close UnitOfWork with each having his own connection, the user action interacting with the database while changing value in controls can dramatically open a lot of connection.

   Thank you very much for this great feature.

   Samuel Feb 6th, 2011

Leave a Reply