Preventing deadlocks

8
Sometimes we notice some microflows are hanging and after searching in the log files we see stack trace like this: Caused by: com.mendix.core.CoreRuntimeException: af: Exception occurred while updating data. (SQL State: 40P01, Error Code: 0) at iw.b(SourceFile:213) Caused by: af: Exception occurred while updating data. (SQL State: 40P01, Error Code: 0) Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected Detail: Process 20815 waits for ShareLock on transaction 33121223; blocked by process 21562. Process 21562 waits for ShareLock on transaction 33120767; blocked by process 20815. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at aA.a(SourceFile:969) at aA.a(SourceFile:930) at aA.a(SourceFile:561) at iE.a(SourceFile:61) at at.a(SourceFile:312) at at.a(SourceFile:187) at at.a(SourceFile:101) at jF.a(SourceFile:96) at jp.a(SourceFile:242) at ca.a(SourceFile:81) at ca.executeAction(SourceFile:26) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:49) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:473) at iw.b(SourceFile:209) at u.a(SourceFile:100) at kT.a(SourceFile:72) at eU.executeAction(SourceFile:96) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:49) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:473) at iw.b(SourceFile:155) at com.mendix.core.Core.executeSync(SourceFile:167) at dN.a(SourceFile:68) at kT.a(SourceFile:67) at eU.executeAction(SourceFile:96) at com.mendix.systemwideinterfaces.core.UserAction.execute(SourceFile:49) at com.mendix.core.actionmanagement.CoreAction.call(SourceFile:473) at iw.b(SourceFile:155) at com.mendix.core.Core.execute(SourceFile:191) at dD.execute(SourceFile:183) at jx.a(SourceFile:299) at jx.a(SourceFile:230) at jx.processRequest(SourceFile:174) at fI.a(SourceFile:71) at com.mendix.core.MxRuntime.processRequest(SourceFile:938) at com.mendix.m2ee.server.handler.RuntimeHandler.handle(RuntimeHandler.java:42) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113) at org.eclipse.jetty.server.Server.handle(Server.java:334) at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559) at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.java:1007) at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747) at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209) at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406) at org.eclipse.jetty.server.nio.BlockingChannelConnector$ConnectorEndPoint.run(BlockingChannelConnector.java:187) at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436) at java.lang.Thread.run(Thread.java:662) How can I prevent things like this? In this action we hit a microflow button which is retrieving some objects and updates a status and then goes to another form.
asked
2 answers
3

In addition to Michel's answer:

You could check whether one microflow will run concurrently and if these microflows change the same objects or not. As Michel said, microflows wait on each other to finish. In this case your microflow might have other activities to do, and all locks still exist until the microflow finishes. If it's possible, you should take a look to simplify the microflows, limit the changes and put all change activities as much as possible to the end of the microflow.

I faced a similar issue, in our case it was the 'changedBy' and 'changedDate' of the audit trail module which causes the problem.

Jonathan provided me of another check which might be beneficiary in your case:

On all SQL Server databases, Mendix sets the database option READCOMMITTEDSNAPSHOT to ON, so the database engine does not acquire locks and uses row versioning. But sometimes, the option READCOMMITTEDSNAPSHOT cannot be automatically enabled successful. Can you check the option with the following query:
SELECT sd.name, sd.isreadcommittedsnapshoton FROM sys.databases AS sd ORDER BY sd.name
The value of the column 'isreadcommittedsnapshoton' must be '1'. If it is '0', please set the value to 'on' by executing the following query:
ALTER DATABASE [databaseName] SET READCOMMITTEDSNAPSHOT ON
Please check with the SELECT query if the option is enabled now."

answered
1

Deadlocks are triggered when two different transactions are manipulating the same object, and are waiting for each other to finish. I often encountered this when my webservice A invokes remote webservice B that invokes my webservice C. Now if A and C want to change the same object(s), C will wait until A finishes it transaction to commit its own data, but A will not finish until C (and thus B) returns because it is waiting for an answer.

Deadlocks can occur when not using webservices as well, but then they are harder to track. One way to see if a certain call is waiting forever is: stop the mendix app, check if a Jetty warning with something like '1 thread could not be stopped' or something similar appears.

answered