MySQL Forums
Forum List  »  InnoDB

InnoDB engine doesn't honor unique key constraints, under a heavy load
Posted by: Rushin Fernando
Date: March 19, 2015 04:02AM

I have the following table in my database.

CREATE TABLE `IDN_OAUTH2_ACCESS_TOKEN` (
`ACCESS_TOKEN` varchar(255) NOT NULL DEFAULT '',
`REFRESH_TOKEN` varchar(255) DEFAULT NULL,
`CONSUMER_KEY` varchar(255) DEFAULT NULL,
`AUTHZ_USER` varchar(100) DEFAULT NULL,
`USER_TYPE` varchar(25) DEFAULT NULL,
`TIME_CREATED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`VALIDITY_PERIOD` bigint(20) DEFAULT NULL,
`TOKEN_SCOPE` varchar(25) DEFAULT NULL,
`TOKEN_STATE` varchar(25) DEFAULT 'ACTIVE',
`TOKEN_STATE_ID` varchar(255) DEFAULT 'NONE',
PRIMARY KEY (`ACCESS_TOKEN`),
UNIQUE KEY `APP_CON_KEY` (`CONSUMER_KEY`,`AUTHZ_USER`,`USER_TYPE`,`TOKEN_SCOPE`,`TOKEN_STATE`,`TOKEN_STATE_ID`),
KEY `IDX_AT_CK_AU` (`CONSUMER_KEY`,`AUTHZ_USER`,`TOKEN_STATE`,`USER_TYPE`),
CONSTRAINT `IDN_OAUTH2_ACCESS_TOKEN_ibfk_1` FOREIGN KEY (`CONSUMER_KEY`) REFERENCES `IDN_OAUTH_CONSUMER_APPS` (`CONSUMER_KEY`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In my application I execute the following queries in order. (please note that the values are samples)

1)SELECT ACCESS_TOKEN, AUTHZ_USER, TOKEN_SCOPE, TOKEN_STATE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE CONSUMER_KEY = 'a7ede2ff2ccf4946aa4176ff9577448d' AND REFRESH_TOKEN = '0f972d6fea304c5ea15e325941a97666' ORDER BY TIME_CREATED DESC LIMIT 1

2)INSERT INTO IDN_OAUTH2_ACCESS_TOKEN (ACCESS_TOKEN, REFRESH_TOKEN, CONSUMER_KEY, AUTHZ_USER, TIME_CREATED, VALIDITY_PERIOD, TOKEN_SCOPE, TOKEN_STATE, USER_TYPE) VALUES ('6bbcf2c04aa2fa787a639f85b13c90af','fe01784f3aa81511b25cbfa2fe2c082','a7ede2ff2ccf4946aa4176ff9577448d','primary/7e8c819935387606a72149@carbon.super','2015-03-19 11:38:42',300000,'PRODUCTION','ACTIVE','APPLICATION_USER')

The table has around 600k entries. So that first query takes around 60 secs to complete while MySQL is consuming 400% CPU load.

If we execute these query set for 2 concurrent users while MySQL is consuming 400% CPU, I can see duplicate entries, which should be not possible since we have a foreign key constraint.

e.g.

1) 'c09eccdd662f11b736ea9c9bd983e3', '6fea8edd4d22c23883958c455092a35e', 'a7ede2ff2ccf4946aa4176ff9577448d', 'primary/7e8c819935387606a72149@carbon.super', 'APPLICATION_USER', '2015-03-19 13:57:36', '300000', 'PRODUCTION', 'ACTIVE', 'NONE'

2) 'c09eccdd662f11b736ea9c9bd983e4', '6fea8edd4d22c23883958c455092a35c', 'a7ede2ff2ccf4946aa4176ff9577448d', 'primary/7e8c819935387606a72149@carbon.super', 'APPLICATION_USER', '2015-03-19 13:57:36', '300000', 'PRODUCTION', 'ACTIVE', 'NONE'


Can someone please help me to figure this out?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.