Applies to :
Ephesoft v3.0.x & v3.1.x
This solution Applies to Ephesoft installations using MSSQL and All versions of Ephesoft. Due to default transaction lock settings in MSSQL and incompatibility with JBPM tables, Deadlock errors occur.
Some cause the batches to freeze, stall or fail.
2013-07-16 14:15:59,939 [ERROR] [DispatcherThread] [org.hibernate.util.JDBCExceptionReporter] – Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We have provided a new SQL script (link below) to avoid deadlocks.
What the SQL Script is Doing?
The script goes into the Ephesoft Application Database and create Indexes for JBPM tables. These indexes are primary key indexes which exists for every primary key.
These indexes have page level lock and row level lock enabled by default. For prevention of deadlock we only need row level locks on primary key indexes of JBPM4_EXECUTION and JBPM4_VARIABLE tables.
So, by running the given commands we remove the page level locks from the primary key indexes for the above given tables.
In general the name of the primary key indexes for the above given tables are ‘PK__JBPM4_EX__EE2DF1FD07020F21’ and ‘PK__JBPM4_VA__EE2DF1FD37A5467C’ respectively.
But in the below ticket these indexes seems to be named differently.
The Script contains commands that will automatically find the names of the indexes and remove the page level locks as required.
In case of any problems, please share following artifacts:-
- Database dump.
- Error message after running the commands defined in the sql files.
Steps to execute SQL script:
- Kindly take the back-up of customer database, before executing the script.
- In the attached script, change the first line to add customer database name.
- Copy the content and paste it in SQL Server Management Studio query editor and then execute it.
- After all the queries are successfully executed, kindly verify that the Unique Key constraint of JBPM4_EXECUTION table is deleted or not.
If not, then kindly delete it manually as shown in screenshot below:
- Verify that indexes have been applied on JBPM tables correctly. The indexes on JBPM tables could be seen inside Indexes tab as highlighted in screenshot above. Revert to the backed-up database if something unexpected happens.
How to verify if the script worked?
select * from sys.indexes where object_id = (select object_id from sys.objects where name = ‘<JBPM4_EXECUTION or JBPM4_VARIABLE>’);
All Page locks should be set to “0”.
Verify that whether READ_COMMITTED_SNAPSHOT is ON at client database. This can be done by executing below query:
SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases;
Both the values should come as 1 for the Ephesoft database. If it is not so, please set these value ON by executing the below steps:
- Keep the back up of existing database.
- Execute the following queries on the database. While executing query, there should not be any connection present on the database:
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
If the query is taking long time to execute, it means all the connection has not been closed. Restart the MSSQL server and execute the same queries again.
- Kindly revert the changes in case of any issues by executing following queries on MSSQL server database.
ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE <database_name> SET ALLOW_SNAPSHOT_ISOLATION OFF;