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.