Drupal Commerce performance: Locking

Many people have been bugging me to write about Drupal Commerce performance because when you Google “Drupal Commerce Performance,” you pretty much get nothing.

While Commerce 2.x for Drupal 8 is arriving shortly, we’re still running lots of Commerce 1.x Drupal 7 sites. So with this first performance post, I will attempt to explain the common locking problems you encounter in Commerce 1.x and clear up their misconceptions.

Locking

You will likely encounter two different prevalent locking problems, and it is very common to assume you have a single locking problem. Since these two locking problems are entirely unrelated, it causes confusion as people look for the solution to “the locking problem” and get stuck on the wrong fix or a combination of the two.

Field locking

You will notice “deadlock” messages spamming out in your database logs. These will be related to fields and are not a Drupal Commerce-specific problem. They are just particularly problematic when using Drupal Commerce. This comes from the default transaction settings Drupal uses in MySQL, which locks the rows around a record being inserted in an attempt to have slightly faster inserts. This doesn’t work well in Drupal, though, as field inserts don’t need speed; field reads do. This causes field loads to lock fields around them, which leads to this big mess of locked fields and nothing loading. Since Drupal Commerce is a lot heavier on entities than your average blog site, this becomes a real problem even on a medium-sized commerce site.

Luckily, the solution to this is pretty simple. You need to change your transaction isolation level to READ-COMMITTED, which is slightly slower on inserts but will give you much better overall performance.

See all of Mike Carper’s great work on this, and if you want to learn about transaction isolation levels, the MySQL documentation page is a good primer.

Order locking

You may get locking issues where you see UPDATE FOR commands piling up in your query logs. This is also a locking problem but utterly unrelated to the above problem.

The problem here is that Drupal Commerce uses pessimistic locking, which means that while an order is being loaded, it is locked until the process finishes. This is to prevent two processes from editing an order simultaneously and overwriting each other. If only one process is ever loading the order at a time, no problem occurs; if you have only a few, you will get a slight delay but no serious problems. The problem is that ANYTHING that loads an order will lock it, even if it will never be editing the order, but Commerce doesn’t know this, so it locks it as a precaution. This means that things like order views or reports that list a lot of orders and take a while will lock ALL the orders they are loading, which will bottleneck anyone else trying to load the order, such as a CSR or customer.

There is an ongoing patch that has not yet been committed that will help lessen this issue by allowing orders to be loaded without locking when locking is known to be unnecessary because no editing can take place. This doesn’t entirely solve the problem, as pessimistic order locking is inherently heavy-handed. Still, it significantly reduces issues with large loads, which are the worst offenders, but usually not for editing.

Commerce 2.x will use optimistic order locking, which assumes an order does not need to be locked by default. In the event of a conflict where two saves occur without a reload, the latter will error and be prevented from overwriting the first. If the saved data is necessary, the order will have to be reloaded and saved to ensure no information is lost.

The previous approach was immune to this problem but far too strict, as conflicts are not likely and possible to resolve in the event they arise with properly written code. In exchange for more work writing the code, we eliminate order locking problems, ranging from a slight delay in loading to a complete failure to load.

I hope this helps fix many of the locking issues people are experiencing, as I know they are challenging to solve and very confusing to research. Also, thanks to Damien Tournoud, Matt Glaman, Bojan Živanović, Ryan Szrama and Erik Peterson for all their help working on locking issues.