在数据库访问应用程序中管理并发的最佳方式是什么?

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.
• 我使用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!

预先感谢您的帮助!

2 个解决方案

#1

1. Consider leveraging Connection Pooling to reduce # of connections. See: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

   考虑利用连接池减少连接数量。参见: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

   尽可能晚地锁定并尽快发布，以最大限度地提高并发性。您可以使用TransactionScope(参见:http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx和http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/us1/using-newtransactionscop.aspx)，如果您有多个操作需要在proproproproproprodb查询中同时处理它们，那么就可以保持一致性。遵循以下技巧，了解锁定如何工作以及如何减少资源争用和死锁: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

   我不确定是否有其他db，但是对于SQL，您可以使用SQL依赖项，参见http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

#2

Concurrency is usually granted by the DBMS using locks. Locks are a type of semaphore that grant the exclusive lock to a certain resource and allow other accesses to be restricted or queued (only restricted in the case you use uncommited reads).

并发通常由使用锁的DBMS授予。锁是一种信号量，它将独占锁授予特定的资源，并允许其他访问被限制或排队(仅在您使用未加逗号的读时受到限制)。

The number of connections itself does not pose a problem while you are not reaching heights where you might touch on the max_connections setting of your DBMS. Otherwise, you might get a problem connecting to it for maintenance purposes or for shutting it down.

连接的数量本身不会造成问题，但当您没有达到可能接触到DBMS的max_connections设置的高度时。否则，您可能会遇到用于维护或关闭它的问题。

DBMSes usually use a concept of either table locks (MyISAM) or row locks (InnoDB, most other DBMSes). The type of lock determines the volume of the lock. Table locks can be very fast but are usually considered inferior to row level locks.

dbmse通常使用表锁(MyISAM)或行锁(InnoDB，大多数其他dbms)的概念。锁的类型决定锁的容量。表锁可以非常快，但通常被认为不如行级锁。

Row level locks occur inside a transaction (implicit or explicit). When manually starting a transaction, you begin your transaction scope. Until you manually close the transaction scope, all changes you make will be attributes to this exact transaction. The changes you make will also obey the ACID paradigm.

行级锁发生在事务中(隐式或显式)。当手动启动事务时，您将启动事务范围。在手动关闭事务范围之前，您所做的所有更改都将是该事务的属性。你所做的改变也将遵循ACID范式。

Transaction scope and how to use it is a topic far too long for this platform, if you want, I can post some links that carry more information on this topic.

事务范围和如何使用它对于这个平台来说是一个太长的主题，如果您愿意，我可以发布一些包含更多关于这个主题的信息的链接。

For the automatic updates, most databases support some kind of trigger mechanism, which is code that is run at specific actions on the database (for instance the creation of a new record or the change of a record). You could post your code inside this trigger. However, you should only inform a recieving application of the changes, not really "do" the changes from the trigger, even if the language might make it possible. Remember that the action which triggered the code is suspended until you finish with your trigger code. This means that a lean trigger is best, if it is needed at all.

对于自动更新，大多数数据库都支持某种触发机制，即在数据库上的特定操作(例如创建新记录或更改记录)运行的代码。你可以把你的代码发布在这个触发器中。但是，您应该只通知收到更改的应用程序，而不是真正“执行”触发器的更改，即使语言可能使其成为可能。请记住，触发代码的操作将被挂起，直到您完成触发器代码。这就意味着，如果需要的话，最好用精益的触发器。