Deadlock in Crowd + MS SQL SERVER 2012 (always on) environment

[JongBum Lee]

Dear All

I am using version 2.8.0 of CROWD and deadlock occurs.

My Environment

OS : WINDOWS Server 2012 64bit

JDK : 1.8.0_21 (64bit)

CROWD 2.8.0

Database : Microsoft SQL Server 2012 - 11.0.5058.0 (X64) (WITH ALWAYS ON)

Connection Pool : 

c3p0.acquire_increment : 1

c3p0.idle_test_period : 100

c3p0.max_size : 30

c3p0.max_statements : 0

c3p0.min_size : 0

c3p0.timeout : 30

The error occurs when performing CROWD authentication.

The strange thing is that the error occurs, but the UPDATE of CWD_TOKEN, CWD_USER_ATTRIBUTE is done.

The same error also occurs in cwd_user_attribute table

---------------------------------------------------------------------------------------

2018-03-15 20:50:19,042 http-bio-8095-exec-57273 WARN [engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1205, SQLState: 40001
2018-03-15 20:50:19,042 http-bio-8095-exec-57273 ERROR [engine.jdbc.spi.SqlExceptionHelper] [SQL Server] Transaction (Process ID 259) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2018-03-15 20:50:19,042 http-bio-8095-exec-57273 ERROR [jdbc.batch.internal.BatchingBatch] HHH000315: Exception executing batch [could not perform addBatch]
2018-03-15 20:50:19,058 http-bio-8095-exec-57273 ERROR [[Catalina].[localhost].[/crowd].[default]] Servlet.service() for servlet [default] in context with path [/crowd] threw exception
org.springframework.dao.CannotAcquireLockException: could not perform addBatch; SQL [update cwd_token set directory_id=?, entity_name=?, random_number=?, identifier_hash=?, random_hash=?, created_date=?, last_accessed_date=?, last_accessed_time=?, duration=? where id=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not perform addBatch
at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:134)
at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:678)
at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:560)
............................

2018-03-15 20:50:19,058 http-bio-8095-exec-57273 ERROR [500ErrorPage] Exception caught in 500 page could not perform addBatch; SQL [update cwd_token set directory_id=?, entity_name=?, random_number=?, identifier_hash=?, random_hash=?, created_date=?, last_accessed_date=?, last_accessed_time=?, duration=? where id=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not perform addBatch
org.springframework.dao.CannotAcquireLockException: could not perform addBatch; SQL [update cwd_token set directory_id=?, entity_name=?, random_number=?, identifier_hash=?, random_hash=?, created_date=?, last_accessed_date=?, last_accessed_time=?, duration=? where id=?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not perform addBatch
at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:134)
at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:678)
at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:560)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724)

............................

2018-03-15 20:50:19,449 http-bio-8095-exec-57312 WARN [engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1205, SQLState: 40001
2018-03-15 20:50:19,449 http-bio-8095-exec-57312 ERROR [engine.jdbc.spi.SqlExceptionHelper] [SQL Server] Transaction (Process ID 249) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2018-03-15 20:50:19,449 http-bio-8095-exec-57312 ERROR [[Catalina].[localhost].[/crowd].[default]] Servlet.service() for servlet [default] in context with path [/crowd] threw exception
org.hibernate.exception.LockAcquisitionException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:140)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:88)

[AnnWorley]

Hello!

I understand we are seeing DB deadlocks using SQL Server 2012 with Crowd 2.8. I did not find that issue reported for Crowd, but Confluence works similarly and will throw that deadlock error when row versioning is not on: Database deadlock on Microsoft SQL Server

You can diagnose the issue with this query - it should come back as "1":

SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database name>';

And this is the query to change it, if it is off - Important: Crowd will need to be stopped before running the ALTER statement against the database, then started again.

Configure your database to use the isolation level, Read Committed with Row Versioning. You can do this by executing the following query:

ALTER DATABASE <database name>
 SET READ_COMMITTED_SNAPSHOT ON
 WITH ROLLBACK IMMEDIATE;

I look forward to hearing whether the first query returns a 1 or a 0 and if it returns a 0, whether the deadlocks are eliminated after setting read_committed_snapshot to ON.

Thanks,

Ann

[Productivity Standards and Practices]

I'm also experiencing the same error very often on my Crowd server version 2.12.0, running on Win 2008 SP2 and MS SQL 2012.

My SQL db has the read committed snapshot on with the 1 value and still the error happens.  It has to be something else.

Regards,