It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

JongBum Lee Mar 16, 2018

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Opsgenie

Handling Opsgenie Alerts

Hi everyone, welcome to the kb articles for Opsgenie FAQs. I'm one of the Technical Support Engineers for Opsgenie who will be providing weekly posts on FAQs from customers.   All alerts are ...

69 views 0 3
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you