0

I will give a simple example to put forward the problem I am facing. Consider that there are two transactions trying to access some rows in a table using select for update clause as follows:-

Transaction 1-

START TRANSACTION;
SELECT * FROM maas.model_requests where id='test1' and model_request_status='QUEUED' order by priority asc limit 1 for update;

Transaction 2 -

START TRANSACTION;
SELECT * FROM maas.model_requests where id='test2' and model_request_status='QUEUED' order by priority asc limit 1  for update;
COMMIT;

As you can see, both the transactions are trying to access different rows of table model_requests id in both the where clauses being different. Also, I have not committed first transaction so that examine if the select for update is locking more rows than expected. Result of above two transactions getting executed simultaneously is transaction 2 can never get lock even though transaction 1 is supposed to lock only row having id as test1 .

Now, if I remove the order by clause from both queries, the problem goes away and transaction 2 gets completed even when transaction 1 is never committed. I tried some Googling around this and found similar issue posted here. As per the accepted answer, setting the parameters binlog_format to row and tx_isolation to read-committed is supposed to solve it. But even after setting these parameters, it's not getting solved for me.

One thing to note is both the columns in the where clause are part of an index and I have confirmed using explain clause that the query is using the same index. Please suggest how this can be solved. The Mysql version is '5.7.12' as per select version() query.

Output for show create table -

'CREATE TABLE `model_requests` (
  `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
  `client_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_request_status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_request_execution_status` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_sub_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `model_function_version` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `archetype_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `request_user` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `mpu_retry_total` int(11) DEFAULT ''0'',
  `client_retry_total` int(11) DEFAULT ''0'',
  `max_retry_client_count` int(11) DEFAULT ''1'',
  `max_retry_mpu_count` int(11) DEFAULT ''1'',
  `mpu_ack_timeout` bigint(20) DEFAULT ''10'',
  `client_ack_timeout` bigint(20) DEFAULT ''10'',
  `client_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sub_type_version` varchar(45) COLLATE utf8_unicode_ci DEFAULT ''1.0'',
  `is_synchronous` tinyint(4) DEFAULT ''0'',
  `mpu_expiry_time` datetime DEFAULT NULL,
  `client_expiry_time` datetime DEFAULT NULL,
  `routing_key` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  KEY `index_maas_request_id` (`id`),
  KEY `index_model_requests_status_arch_type_priority_created_at` (`model_request_status`,`archetype_name`,`priority`,`created_at`),
  KEY `index_model_requests_status_arch_type_rkey_priority_created_at` (`model_request_status`,`archetype_name`,`routing_key`,`priority`,`created_at`),
  KEY `index_model_requests_status_client_priority_created_at` (`priority`,`created_at`,`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY LIST  COLUMNS(model_request_status)
SUBPARTITION BY KEY (routing_key)
SUBPARTITIONS 5
(PARTITION cancelled VALUES IN (''CANCELLED'') ENGINE = InnoDB,
 PARTITION completed VALUES IN (''COMPLETED'') ENGINE = InnoDB,
 PARTITION in_progress VALUES IN (''IN_PROGRESS'') ENGINE = InnoDB,
 PARTITION notified_cancelled VALUES IN (''NOTIFIED_CANCELLED'') ENGINE = InnoDB,
 PARTITION queued VALUES IN (''QUEUED'') ENGINE = InnoDB,
 PARTITION notified VALUES IN (''NOTIFIED'') ENGINE = InnoDB,
 PARTITION acknoweleded VALUES IN (''ACKNOWLEDGED'') ENGINE = InnoDB) */'
Shadow
  • 33,525
  • 10
  • 51
  • 64
Sumit Desai
  • 1,542
  • 9
  • 22
  • 1
    I don't see an order by clause? – ysth Apr 21 '23 at 07:16
  • I had forgotten adding it. Added now – Sumit Desai Apr 21 '23 at 07:26
  • Please edit your question to show (as text) output of `show create table maas.model_requests` and `select version()` – ysth Apr 21 '23 at 07:40
  • I have added both – Sumit Desai Apr 21 '23 at 08:11
  • so id is not supposed to be a unique key? I'm not seeing the index with both id and model_request_status you mention; what did explain show? – ysth Apr 23 '23 at 05:26
  • note that 5.7.12 is 7 years old; you are missing many security patches and will have many publicly known vulnerabilities. consider upgrading to 8 (or at least the newest 5.7, though 5.7 will reach end of life and no longer get security fixes in just a few months); upgrading may even resolve this issue. – ysth Apr 23 '23 at 18:41
  • Yes. Agree. But the application we are using is quite huge and upgrading mysql version will required significant amount of time – Sumit Desai Apr 24 '23 at 13:50
  • Adding the index on all the three columns (2 in search criteria and 1 in order by clause) did not solve the problem. We are thinking to handle the locks in the application manually using some identifier instead of using Mysql's locking – Sumit Desai Apr 24 '23 at 13:51
  • Show the create table with the index you tried, and the explain with that index. – ysth Apr 24 '23 at 16:13
  • You can likely upgrade to 5.7.43 without breaking anything, but read through https://dev.mysql.com/doc/relnotes/mysql/5.7/en/ first. There's a decent chance it will fix your problem. – ysth Apr 24 '23 at 16:15

0 Answers0