| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0.29 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | deadlock, lock priority | ||
[4 Nov 2022 8:44]
Seunguck Lee
[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".
