Search code examples
mysqlinnodb

Mysql 8 SELECT ... FOR UPDATE seems not working

Environment

Mysql 8.0.25

I have a table named my_table and there is one row, contains id 3

CREATE TABLE my_table (
    id bigint AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO my_table(id) VALUES (3);

What I did

I connected to mysql with two clients (one is pycharm, one is terminal)

(in pycharm)

SET AUTOCOMMIT = 0;
SELECT @@AUTOCOMMIT;  # 0
START TRANSACTION;
    SELECT * FROM my_table WHERE id = 3 FOR UPDATE;

(in terminal)

SELECT * FROM my_table WHERE id = 3;

Expected

If there is a lock, I shouldn't be able to select the row.

What Actually Happened

I was able to SELECT the row

mysql> SELECT * FROM my_table WHERE id = 3;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

What is the correct way to acquire a lock?

Solution

  • 'for update' will not block the query, but you cannot modify it In terminal。