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

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)


1 answer

0 votes
Ann Worley Atlassian Team Mar 19, 2018

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

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,

Suggest an answer

Log in or Sign up to answer
Community showcase
Published 15 hours ago in Opsgenie

Getting the Most out of Atlassian and Opsgenie Together

We’re excited to invite you to this action-packed webinar where we will demonstrate how to integrate Opsgenie’s powerful alerting and on-call management tools with your entire Atlassian stack. Mar...

19 views 0 0
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you