What's the best way to manage concurrency in a database access application?

Goul picture Goul · Jan 13, 2011 · Viewed 12.6k times · Source

A while ago, I wrote an application used by multiple users to handle trades creation. I haven't done development for some time now, and I can't remember how I managed the concurrency between the users. Thus, I'm seeking some advice in terms of design.

The original application had the following characteristics:

  • One heavy client per user.
  • A single database.
  • Access to the database for each user to insert/update/delete trades.
  • A grid in the application reflecting the trades table. That grid being updated each time someone changes a deal.
  • I am using WPF.

Here's what I'm wondering:

  1. Am I correct in thinking that I shouldn't care about the connection to the database for each application? Considering that there is a singleton in each, I would expect one connection per client with no issue.

  2. How can I go about preventing the concurrency of the accesses? I guess I should lock when modifying the data, however don't remember how to.

  3. How do I set up the grid to automatically update whenever my database is updated (by another user, for example)?

Thank you in advance for your help!

Answer

Jimmy Chandra picture Jimmy Chandra · Jan 13, 2011
  1. Consider leveraging Connection Pooling to reduce # of connections. See: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

  2. lock as late as possible and release as soon as possible to maximize concurrency. You can use TransactionScope (see: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx and http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx) if you have multiple db actions that need to go together to manage consistency or just handle them in DB stored proc. Keep your query simple. Follow the following tips to understand how locking work and how to reduce resource contention and deadlock: http://www.devx.com/gethelpon/10MinuteSolution/16488

  3. I am not sure other db, but for SQL, you can use SQL Dependency, see http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx