Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,456,630
Community Members
 
Community Events
176
Community Groups

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
AnnWorley 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
TAGS

Atlassian Community Events