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 all 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 the misconceptions with them.
There are two different common locking problems you will likely encounter and it is very common to assume you have a single locking problem. Since these two locking problems are actually completely unrelated, it causes a lot of confusion as people look for the solution to “the locking problem” and get stuck on the wrong fix or a combination of the two.
You will notice “deadlock” messages spamming out in your database logs. These will be related to fields and are actually 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 just need to change your transaction isolation level to READ-COMMITTED, which is a wee bit slower on inserts but will give you much better overall performance.
See all of Mike Carpers great work on this https://groups.drupal.org/node/415883 and if you want to learn about transaction isolation levels, the MySQL documentation page is a good primer https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-lev...
You may get locking issues where you see UPDATE FOR commands piling up in you query logs. This is also a locking problem, but completely unrelated to the problem mentioned above. 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 2 processes from editing an order at the same time and overwriting each other. If only 1 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 fully solve the problem as pessimistic order locking is inherently heavy handed, but it does significantly lessen problems 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 save will error and be prevented from overwriting the first. If the data being saved is important, the order will have to be reloaded and saved to make sure no data 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 a bit more work writing the code, we eliminate order locking problems, which can range from a small delay in loading to a complete failure to load.
Hope this helps fix up a lot of the locking issues people are experiencing as I know they are difficult 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.