Optimal Use of Transactions within Microsoft Dynamics CRM Plug-ins

By default, Microsoft Dynamics CRM 2011 plug-ins that are triggered by the same event run within the same SQL Server database transaction, as do any related calls to the SDK. For example, both the creation of an account and a subsequent call to the SDK to generate a related task to welcome the customer would be included in a single transaction. This functionality is intended to ensure that in the event of an error or failure, an entire activity is rolled back together, which is an incredibly valuable benefit in most scenarios.

While this capability offers strong protection against failure, however, the longer a transaction is help open the greater the chances that it can impact other concurrent activity in the system, thereby causing delays or errors. As a result, be sure to keep this in mind during plug-in design to help minimize any application blocking implications associated with initiating actions that result in database locks.

Some common causes of locking are described below.

Performing queries. When a user queries the database, SQL Server uses transaction locking strategies to ensure that the transaction is completed consistently and reliably. When data is queried, SQL Server determines whether or not to lock the data for read, thereby preventing other transactions from writing to that data. SQL Server will determine the most appropriate range of data to lock, and for queries that access large volumes of data in a table or that search for data that is spread across a range of table storage, then the entire table may be locked. In this scenario, the lock would not be released until the enclosing transaction is committed or aborted, which can in turn impact other transactions that also may need to lock data in the table. For example, if a user accesses Accounts with an owner of the current user, this could cause the application to query data across the Account tables.

for more information visit :  http://blogs.msdn.com/b/crm/archive/2013/02/19/optimal-use-of-transactions-within-microsoft-dynamics-crm-plug-ins.aspx