Confusing SELECT FOR UPDATE behavior in MySQL 8.0.x

Question

Mysql 8.0.x

We stuck on confusing different behevior of SELECT ... LIMIT 1 FOR UPDATE SKIP LOCKED depended on primary index field type.

Let's consider 2 cases of similar tables.

case 1:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

case 2:

CREATE TABLE `test` (
  `id` binary(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

They differ just in field type - INT and BINARY.

Insert 6 items with id 1 to 6.

Run 2 concurent transactions.

Transaction 1:

BEGIN;

SELECT id FROM test
LIMIT 1 
FOR UPDATE skip locked;

SELECT SLEEP(10);  #for test

COMMIT;

Transaction 2:

BEGIN;

SELECT id
FROM test
WHERE id = 4
FOR UPDATE;

COMMIT;

If id is an INT field select ... for update from transaction 2 executes without waiting of the first transaction commit.

If id is a BINARY filed select ... for update from transaction 2 executes after the second transaction commit

Part of SHOW ENGINE INNODB STATUS output for the second case:

---TRANSACTION 38229, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 12, OS thread handle 6068, query id 1532 localhost 127.0.0.1 root Sending data
SELECT id
FROM test
where id = 4
FOR UPDATE
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 4 n bits 80 index PRIMARY of table `test`.`test` trx id 38229 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 11; hex 3100000000000000000000; asc 1          ;;
 1: len 6; hex 000000009530; asc      0;;
 2: len 7; hex 80000000000000; asc        ;;

------------------
---TRANSACTION 38228, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 8, OS thread handle 12052, query id 1530 localhost 127.0.0.1 root User sleep
SELECT SLEEP(10)
-----------------------------

It was a big surprise...

Could anybody explain why mysql (innodb) behaves so different in these cases.

user_6248528 · Answer

What you are experiencing is the effect of (the need to do) autocasting, see Type Conversion in Expression Evaluation.

With a binary column, you are asking MySQL to compare an integer 4 with the binary (string) values in your table, and when you do that, MySQL does the best it can and tries to interpret those strings in your column as numbers.

Examples for values that MySQL will evaluate as equal to the integer 4 are e.g. the strings '4', '04', '000000004', ' 004' and even '04abc'.

MySQL cannot use an index lookup for it, but will have to check (and lock) all rows, as they might be a string that may evaluate to the integer 4. One of those rows is the one you locked in your first transaction, and thus it has to wait until the lock is released.

On the other hand, if your column is an integer, MySQL can use the index to directly jump to the row with id = 4, and unless it is by chance a row locked in your first transaction, the second transaction can proceed.

So to make your binary case behave the same as the integer case, compare the binary column to a binary value, e.g. use WHERE id = '4'. Then no casting has to be done.

Confusing SELECT FOR UPDATE behavior in MySQL 8.0.x

1 Answers