Avoiding Database Deadlocks - Revision history

Sichen at 19:20, 7 April 2008

2008-04-07T19:20:36Z

Sichen at 01:50, 23 February 2008

2008-02-23T01:50:19Z

Sichen at 01:48, 23 February 2008

2008-02-23T01:48:59Z

Sichen at 19:15, 22 February 2008

2008-02-22T19:15:01Z

Sichen at 19:13, 22 February 2008

2008-02-22T19:13:28Z

Sichen: Protected "Avoiding Database Deadlocks": Sysop page [edit=sysop:move=sysop]

2008-02-22T19:12:50Z

Protected "Avoiding Database Deadlocks": Sysop page [edit=sysop:move=sysop]

Sichen: New page: In a high-volume scenario, repeated access to the database could cause a database deadlock, where a table becomes unavailable. this could in turn cause the system to become stuck and for ...

2008-02-22T19:12:49Z

New page: In a high-volume scenario, repeated access to the database could cause a database deadlock, where a table becomes unavailable. this could in turn cause the system to become stuck and for ...

New page

In a high-volume scenario, repeated access to the database could cause a database deadlock, where a table becomes unavailable. this could in turn cause the system to become stuck and for critical functions to fail.

The actual occurrence of database deadlocks will depend somewhat on the database itself. However, there are good general practices that you could follow to reduce your risk. To minimize the risk of deadlocks, [http://www.sql-server-performance.com/tips/deadlocks_p1.aspx Reducing SQL Server Deadlocks] recommended the following:
<pre>
Keep transactions as short as possible. One way to help accomplish this
is to reduce the number of round trips between your application and SQL
Server by using stored procedures or keeping transactions with a single
batch. Another way of reducing the time a transaction takes to complete
is to make sure you are not performing the same reads over and over
again. If your application does need to read the same data more than
once, cache it by storing it in a variable or an array, and then
re-reading it from there, not from SQL Server.
</pre>

In opentaps, we recommend that you follow these best practices when writing more complex business logic:
* Do not rely on the automatic transaction management of the ofbiz service engine. If you have business logic which requires repeated access to the database, both to retrieve and store data, turn off transactions at the service level by using the
use-transaction="false"
flag in the service XML definition. Manage the transaction manually inside of your Java code.
* As a general rule, if you find yourself needing to set a longer transaction timeout for your service, such as by using the
transaction-timeout="600"
parameter in the service XML definition, then your service should be rewritten to avoid potential deadlocks.
* Structure all of your database access code together, especially writes to the database, and put them inside a transaction block.

For example, the following service has a greater risk of database deadlocks:
<pre>

<service name="myService" engine="java" path="org.opentaps.MyServices" invoke="myService"/>

// inside of myService
List orderHeaders = delegator.findByAnd("OrderHeader", UtilMisc.toMap("statusId", "ORDER_APPROVED"));
for (GenericValue orderHeader: orderHeaders) {
List orderItems = orderHeader.getRelatedByAnd("OrderItem", UtilMisc.toMap("statusId", "ITEM_APPROVED"));

for (GenericValue orderItem: orderItems) {
delegator.create(myEntity, myMapOfValues);
// or
Map tmpResult = dispatcher.runSync("someOrderRelatedService", paramterMapValues);
}
}
</pre>

Essentially, this code is going through the database again and again to read and write data. What if you had 1,000 approved orders without an average 10 items per order? You would be doing 1,000 SELECT queries and nesting inside of them 10 INSERT queries. This is very risky.

A better way to do it would be like this (know that this is just an example and is not meant to run in real life):
<pre>

<service name="myService" engine="java" path="org.opentaps.MyServices" invoke="myService" use-transaction="false"/>

// inside of myService

// group all your select queries together
List orderHeaders = delegator.findByAnd("OrderHeader", UtilMisc.toMap("statusId", "ORDER_APPROVED"));
List orderIds = EntityUtil.getFieldFromEntityList(orderHeaders, "orderId", true); // get List of distinct orderIds
List orderItems = delegator.findByAnd("OrderItem", UtilMisc.toList(
new EntityCondition("orderId", EntityOperator.IN, orderIds),
new EntityCondition("statusId", EntityOperator.EQUALS, "ITEM_APPROVED")));

// make a list of values to store
List valuesToCreate = new LinkedList();
for (GenericValue orderItem: orderItems) {
// do something
GenericValue newValue = delegator.makeValue(myEntity, myMapOfValues);
valuesToCreate.add(newValue);
}
}

// one transaction to store all your values
TransactionUtil.begin();
delegator.storeAll(valuesToCreate);
TransactionUtil.commit();
</pre>

@@ Line 41: / Line 41: @@
 </pre>
-Essentially, this code is going through the database again and again to read and write data.  What if you had 1,000 approved orders with an average of 10 items per order?  You would be doing 1,000 SELECT queries and nesting inside of each SELECT 10 INSERT queries.  This is very risky and will probably lead to a deadlock, especially of several threads start trying to run the same service and do all those selects and inserts around the same time.
+Essentially, this code is going through the database again and again to read and write data.  What if you had 1,000 approved orders with an average of 10 items per order?  You would be doing 1,000 SELECT queries and nesting inside of each SELECT 10 INSERT queries.  This is very risky and will probably lead to a deadlock, especially if several threads start trying to run the same service and do all those selects and inserts around the same time.
 A better way to do it would be like this (remember this is just an example and is not meant to run in real life):

@@ Line 20: / Line 20: @@
     transaction-timeout="600"
 parameter in the service XML definition, then your service should be rewritten to avoid potential deadlocks.
-* Structure all of your database access code together, especially writes to the database, and put them inside a transaction block.  your code should be structured as much as possible to follow a three-part pattern: get data, process data, store data.  A transaction should only be opened in in the part of the code where you're actually storing data.
+* Put all of your database access code together, especially writes to the database, and put them inside a transaction block.
 For example,  this hypothetical service has a greater risk of database deadlocks:
@@ Line 40: / Line 41: @@
 </pre>
-Essentially, this code is going through the database again and again to read and write data.  What if you had 1,000 approved orders without an average 10 items per order?  You would be doing 1,000 SELECT queries and nesting inside of them 10 INSERT queries.  This is very risky.
+Essentially, this code is going through the database again and again to read and write data.  What if you had 1,000 approved orders with an average of 10 items per order?  You would be doing 1,000 SELECT queries and nesting inside of each SELECT 10 INSERT queries.  This is very risky and will probably lead to a deadlock, especially of several threads start trying to run the same service and do all those selects and inserts around the same time.
-A better way to do it would be like this (know that this is just an example and is not meant to run in real life):
+A better way to do it would be like this (remember this is just an example and is not meant to run in real life):
 <pre>
    <!-- services XML definition -->

@@ Line 1: / Line 1: @@
-In a high-volume scenario, repeated access to the database could cause a database deadlock, where a table becomes unavailable.  this could in turn cause the system to become stuck and for critical functions to fail.
+In a high-volume scenario, repeated access to the database could cause a database deadlock, where a table becomes unavailable.  This could in turn cause the system to become stuck and for critical functions to fail.
 The actual occurrence of database deadlocks will depend somewhat on the database itself.  However, there are good general practices that you could follow to reduce your risk.  To minimize the risk of deadlocks, [http://www.sql-server-performance.com/tips/deadlocks_p1.aspx Reducing SQL Server Deadlocks] recommended the following:
@@ Line 16: / Line 16: @@
 * Do not rely on the automatic transaction management of the ofbiz service engine.  If you have business logic which requires repeated access to the database, both to retrieve and store data, turn off transactions at the service level by using the
     use-transaction="false"
-flag in the service XML definition.  Manage the transaction manually inside of your Java code.
+flag in the service XML definition.  Manage the transaction inside of your Java code.
 * As a general rule, if you find yourself needing to set a longer transaction timeout for your service, such as by using the
     transaction-timeout="600"

← Older revision	Revision as of 19:12, 22 February 2008
(No difference)