Random LockAcquisitionException errors when publishing large number of pages

Question

In our development env, we have a single Tridion 2013 installation, with only 2 threads for rendering and 2 threads for deploying configured. Storage is a SQL Server 2008R2 database. Our Deployer runs as a Websphere 8.5 application (JDK1.7).
The Deployer uses the following connection-related settings:
+ connP.agedTimeout=0
+ connP.connectionTimeout=180
+ connP.maxConnections=10
+ connP.minConnections=0
+ connP.purgePolicy=EntirePool
+ connP.reapTime=180
+ connP.unusedTimeout=1800
+ prop.syncQueryTimeoutWithTransactionTimeout=
+ prop.webSphereDefaultIsolationLevel=
+ statementCacheSize=20

When we publish some tens of pages, we randomly get LockAcquisitionException errors in our deployer/broker log:

[DEBUG] 2015-02-10 14:16:55,232 com.tridion.util.xml.XMLParser:98     - Parsing XML with encoding null
[ INFO] 2015-02-10 14:16:55,234 com.tridion.deployer.phases.DeployPipelineExecutor:97     - Executing deployment phase: Deployment Processing Phase for transaction: tcm:0-1359110-66560
[ INFO] 2015-02-10 14:16:55,237 com.tridion.deployer.phases.ProcessingPhase:65     - Starting executing of phase: PRE_PROCESSING for transaction: tcm:0-1359110-66560
[DEBUG] 2015-02-10 14:16:55,237 com.tridion.deployer.workers.ProcessorWorker:54     - Starting processors for phase: pre-processing
[ INFO] 2015-02-10 14:16:55,237 com.tridion.deployer.ProcessorFactory:105     - Creating processors for phase: pre-processing
[DEBUG] 2015-02-10 14:16:55,237 com.tridion.deployer.workers.ProcessorWorker:80     - Processors finished for phase: pre-processing
[ INFO] 2015-02-10 14:16:55,238 com.tridion.deployer.phases.ProcessingPhase:70     - Starting executing of phase: PROCESSING for transaction: tcm:0-1359110-66560
[DEBUG] 2015-02-10 14:16:55,238 com.tridion.deployer.workers.ProcessorWorker:54     - Starting processors for phase: processing
[ INFO] 2015-02-10 14:16:55,238 com.tridion.deployer.ProcessorFactory:105     - Creating processors for phase: processing
[ INFO] 2015-02-10 14:16:55,240 com.tridion.deployer.Module:53     - No TransformProcessor configured, will not transform files before deployment for module com.tridion.deployer.modules.SchemaDeploy
[ WARN] 2015-02-10 14:16:55,242 org.hibernate.engine.jdbc.spi.SqlExceptionHelper:143     - SQL Error: 1205, SQLState: 40001
[ERROR] 2015-02-10 14:16:55,242 org.hibernate.engine.jdbc.spi.SqlExceptionHelper:144     - Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[ WARN] 2015-02-10 14:16:55,243 com.tridion.deployer.phases.PreCommitPhase:127     - Failed to Prepare: tcm:0-1359100-66560 error: org.hibernate.exception.LockAcquisitionException: Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[ INFO] 2015-02-10 14:16:55,243 com.tridion.storage.StorageManagerFactory:466     - Rolling back storage transaction: tcm:0-1359100-66560

If we republish the failed page(s) separately at some later moment, they publish succesfully? If anyone else is using SQL Server 2008R2, please let me know the connection properties/pooling used to connect to the DB so we can troubleshoot.

Answer 1

The most common reason of commit issues with the Deployer is due to database statistics needing to be run. SQL Server should automatically run these, but sometimes the DBAs set up rules that override the defaults. So make sure the stats are freshly run on the DB.

The second most common issue is that database swap memory size being too low. In SQL Server terms this is known as the Page File size. Check that it is set to a good amount (e.g. 4GB). Here is a link that suggests some configs for this: http://social.technet.microsoft.com/wiki/contents/articles/13383.best-practices-for-page-file-and-minimum-drive-size-for-os-partition-on-windows-servers.aspx

Answer 2

I played around a bit with the Queue element in the cd_deployer_conf.xml, specifically with the Workers attribute. Interestingly, if Workers="2" then all 100 pages publish succesfully. If I increase the number of workers, this consequently leads to more Failed pages with the mentioned exception. The Tridion docs state that the number of workers should be less than or equal to the number of CPU cores (Increasing Deployer "Workers" setting based on CPU cores). Well, for now I am happy the issue is gone, but not understanding exactly why is a bit unsatisfactory...

Answer 3

As a basic rule, # of threads should not exceed # of cores (otherwise you start swapping threads). But, you also have to guard other resources also.

A deadlock does not -have- to be a problem. The system reports a deadlock even if it cycles for another try. But you have seem failures on the publishing queue relating to a deadlock.

This is basically multiple threads competing for updating the same table and it will most commonly do this under load or when you are publishing "similar" items. Simply because the same resources are under high use. There are two ways to make this better

1) slow down deployment through fewer threads or fewer publish threads or 2) increase the retries for deployment; the depoyer will try, by default, 10 times

Of course, optimal database performance (statistics and good I/O) will help. The faster the DB the less likely you are to run into an issue.