Deadlock when running SELECT x WHERE y FOR UPDATE

I'm using MySQL version 8.3.0 from a Docker image, with the default configuration.

I simplified my use-case to a table with just 2 columns. What I want to achieve is to block simultaneous transactions from inserting records for the same "group" (in this case to the same product_sku), but to allow them to insert for other "groups".

By going through the docs at https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html I understood that by running SELECT x FROM y WHERE z FOR UPDATE I would only lock rows satisfying condition z, without locking all the other ones. However this doesn't seem to be the case.

My table:

CREATE TABLE `example` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_sku` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `example_product_sku_IDX` (`product_sku`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I run two MySQL sessions - AA and BB:

AA> BEGIN;
Query OK, 0 rows affected (0,00 sec)

AA> SHOW VARIABLES WHERE Variable_name='autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0,00 sec)

BB> BEGIN;
Query OK, 0 rows affected (0,00 sec)

BB> SHOW VARIABLES WHERE Variable_name='autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0,00 sec)

AA> SELECT COUNT(*) FROM example WHERE product_sku = 'abc' FOR UPDATE;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

BB> SELECT COUNT(*) FROM example WHERE product_sku = 'def' FOR UPDATE;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

AA> INSERT INTO example (product_sku) VALUES ('abc');
(this query hangs up...)

BB> INSERT INTO example (product_sku) VALUES ('def');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

(at this point query from AA executes):
Query OK, 1 row affected (4,02 sec)

From my understanding the two locks should be independent, as they should lock different range of rows. However they are not.

I tried the below changes, without success:

removing the example_product_sku_IDX index
changing SELECT COUNT(*) to SELECT *

I'm trying to understand whether I'm doing something wrong or MySQL has a bug. I found a similar question on MySQL board, but it's not very helpful in terms of explaining the problem - https://bugs.mysql.com/bug.php?id=96748

Solution

After some research I think I can answer my own question.

There are 2 things related to the question above.

First - one needs to set a proper transaction isolation level, as described here: https://dev.mysql.com/doc/refman/8.3/en/set-transaction.html

In this case, the queries should be as follows:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

SELECT COUNT(*) FROM example WHERE product_sku = 'abc' FOR UPDATE;
INSERT INTO example (product_sku) VALUES ('abc');

COMMIT;

Second - in the example above, MySQL won't create any lock if the SELECT x FROM y FOR UPDATE returns empty result. There must be at least one record satisfying the query conditions. So in case of an empty table, when multiple concurrent clients try to insert a row into the same "group", MySQL won't block them if one uses FOR UPDATE type of locking mechanism.