Bug #109005 Some "deadlock example" of manual does not yield deadlock anymore
Submitted: 4 Nov 2022 8:44 Modified: 9 Dec 2022 12:03
Reporter: Seunguck Lee Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: deadlock, lock priority

[4 Nov 2022 8:44] Seunguck Lee
Description:
After patch this bug 21356 (from MySQL 8.0.29),
First deadlock example of following maunal page does not yield deadlock anymore.

https://bugs.mysql.com/bug.php?id=21356
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html

=============================================
Session-A> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

Session-A> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

Session-A> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

Session-A> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+

Session-B> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

Session-B> DELETE FROM t WHERE i = 1;

Session-A> DELETE FROM t WHERE i = 1;
==> Deadlock
=============================================

How to repeat:
Need to remove this example from manual.

Suggested fix:
Remove or replace another deadlock example.
[9 Nov 2022 12:03] MySQL Verification Team
Hello Seunguck Lee,

Thank you for the report and feedback.
I quickly attempted the test case from manual and can confirmed that provided example is still valid. Could you please confirm this? Thank you.

- 8.0.31

- Client A

bin/mysql -uroot -S /tmp/mysql.sock --prompt='A>'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

A>create database test;
Query OK, 1 row affected (0.00 sec)

A>use test
Database changed
A>CREATE TABLE Animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

A>CREATE TABLE Birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

A>INSERT INTO Animals (name,value) VALUES ("Aardvark",10);
Query OK, 1 row affected (0.01 sec)

A>INSERT INTO Birds (name,value) VALUES ("Buzzard",20);
Query OK, 1 row affected (0.00 sec)

A>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

A>SELECT value FROM Animals WHERE name='Aardvark' FOR SHARE;
+-------+
| value |
+-------+
|    10 |
+-------+
1 row in set (0.01 sec)

- Client B

 bin/mysql -uroot -S /tmp/mysql.sock --prompt='B>'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

B>use test
Database changed
B>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

B>SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE;
+-------+
| value |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

- Client B

B>UPDATE Animals SET value=30 WHERE name='Aardvark';
Query OK, 1 row affected (1.54 sec)
Rows matched: 1  Changed: 1  Warnings: 0

- Client A
A>UPDATE Birds SET value=40 WHERE name='Buzzard';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
A>

--
SELECT ENGINE_TRANSACTION_ID as Trx_id,
    ->               OBJECT_NAME as `Table`,
    ->               INDEX_NAME,
    ->               LOCK_DATA,
    ->               LOCK_MODE,
    ->               LOCK_STATUS,
    ->               LOCK_TYPE
    ->         FROM performance_schema.data_locks WHERE LOCK_TYPE="TABLE";
+--------+---------+------------+-----------+-----------+-------------+-----------+
| Trx_id | Table   | INDEX_NAME | LOCK_DATA | LOCK_MODE | LOCK_STATUS | LOCK_TYPE |
+--------+---------+------------+-----------+-----------+-------------+-----------+
|   1311 | Animals | NULL       | NULL      | IX        | GRANTED     | TABLE     |
|   1311 | Birds   | NULL       | NULL      | IS        | GRANTED     | TABLE     |
+--------+---------+------------+-----------+-----------+-------------+-----------+
2 rows in set (0.01 sec)

regards,
Umesh
[10 Dec 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".