1

We have implemented Spring OAuth 2.0 in one of my application and when we did performance test on the sam, the application started throwing exception DuplicateKeyException and we assume that this happens because the same user tries to create token multiple time. We will avoid this issue by ensuring that we are not calling create token for the same user concurrently.

We are using password grant flow in our application.

One observation we have noted is that the token creation and verification happens perfectly fine when the concurrency is low, as soon as the load increases the response time for the services also shoots up. From the logs we could see that there a lot of sql transaction time outs, there were deadlocks intermittently . We are using MySQL and the SQL script used for creating the table is as given below.

create table oauth_access_token (
  token_id VARCHAR(256),
  token LONGVARBINARY,
  authentication_id VARCHAR(256) PRIMARY KEY,
  user_name VARCHAR(256),
  client_id VARCHAR(256),
  authentication LONGVARBINARY,
  refresh_token VARCHAR(256)
);

create table oauth_refresh_token (
  token_id VARCHAR(256),
  token LONGVARBINARY,
  authentication LONGVARBINARY
);

The queries used can be found in this java class.

Is there any way to fix this issue by optimizing the table structure or adding index to columns.

One of the related question found is Spring OAuth2 JDBCTokenStore performance and database schema.

Delon
  • 741
  • 1
  • 6
  • 14
  • Concurrency issues usually tend to show up when there's enough load. Optimizing can help postpone the problem, but if you don't fix the underlying issue it's not a fix. – Kayaman Jan 20 '18 at 18:40
  • @Kayaman I have been trying really hard to find the root cause of this issue. Tried various approaches like indexing, custom token store that uses spring-data instead of spring-jdbc-template. The one thing which i strictly look forward is to fix this without violating the principles. – Delon Feb 06 '18 at 12:50
  • Violating the principles? – Kayaman Feb 06 '18 at 12:56
  • Oauth 2.0 principles – Delon Feb 06 '18 at 12:57
  • Oauth 2.0 principles don't prevent you from fixing your database handling. Of course you need to actually fix things. It's not a speed issue, it's a logic issue. – Kayaman Feb 06 '18 at 13:10

0 Answers0