Crowd token storage in database causing Crowd to hang

Problem Report

Support has had an influx of cases since Crowd release 2.7 regarding 'hung' instances effectively causing any application using Crowd for authentication down the line to be unable to authenticate.

In first case, we reviewed the db connection pool live and found that threads are waiting on a query to execute in the database, but the query ran for over 15 minutes before we killed the application, truncated the cwd_token table, and restarted. Upon restart we can watch the connections get used and increase to the limit at which point the application becomes unresponsive again. This can take seconds, minutes, or an hour or more, just depends on the number of applications and users hitting the systems.

The query identified in a postgres env:

postgres=# select * from pg_stat_activity where current_query like 'update%';
datid | datname | procpid | usesysid | usename | current_query waiting	| xact_start |	 query_start |	 backend_start | client_addr | client_port 
17618 |	 crowd	| 4253	| 17617	 | crowduser	| update cwd_token set directory_id=$1, entity_name=$2, random_number=$3, identifier_hash=$4, random_hash=$5, created_date=$6,
last_accessed_date=$7, last_accessed_time=$8, duration=$9 where id=$10	| t	 2013-10-23 11:15:19.802626-04	| 2013-10-23 11:15:19.806601-04	| 2013-10-23 11:15:11.270126-04	| 127.0.0.1	| 45819
(1 row)

Using visualvm and mbeans plugin we were able to modify the c3p0 connection pool limits in real time and watch the connections climb from 30 to 100 where the system effectively hung again. Changing the connections back to 30 dropped all connections at which point they were picked up again and not released - all waiting on the long running query against the cwd_token table to finish.