Mysql : select for update got result from 'not match criteria'

Viewed 52

I have table v and t with relation

v (one) -> t (many)

CREATE TABLE `v` (
  `i` int NOT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `t` (
  `i` int NOT NULL,
  `v_id` varchar(255) DEFAULT NULL,
  `t_status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO v (i,status) VALUES(1,'default'),(2,'default'),(3,'default');

INSERT INTO t (i,v_id,t_status) VALUES(1,1,'ON'),(2,1,'ON'),(3,1,'OFF')(4,2,'ON'),(5,2,'ON'),(6,3,'ON');

I need to select record from table v if t of v is have status = 'ON' and lock for update status

select * from v where v.i in (select v.i from v join t on v.i = t.v_id where v.status = 'default' group by v.i) limit 1 for update skip locked;

PS.

I need to select from selected due to I don't want to lock table T

Step 1 open session 1

start transaction;

Step 2 select first record for update

mysql> select * from v where v.i in (select v.i from v join t on v.i = t.v_id where v.status = 'default' group by v.i) limit 1 for update skip locked;
+---+---------+
| i | status  |
+---+---------+
| 1 | default |
+---+---------+
1 row in set (0.00 sec)

Step 3 create new session (try to be new application instance)

 start transaction;

Step 4 update record session one and commit it

update v set status = 'updated' where v.i = 1;
commit;

Step 5 try to select same query with session 2

mysql> select * from v where v.i in (select v.i from v join t on v.i = t.v_id where v.status = 
'default' group by v.i) limit 1 for update skip locked;
+---+---------+
| i | status  |
+---+---------+
| 1 | updated |
+---+---------+
1 row in set (0.00 sec)

I so confusing why the result is not match the criteria because I need status = default from table v

OR any suggestion for the query, I'm stuck for a while

Thank you for replied.

0 Answers
Related