How to use update together with select...for update in mysql

Question

I have two MySql tables tableA and tableB, primary key "id" in tableA is used as a foreign key "parent_id" in tableB. I would like to update single row in tableB using select...for update so that other users can not access it while transaction is not over. My question is - how to correctly update selected row in one query? Here is my sample code:

START TRANSACTION;
SELECT b.reserved, b.owner FROM tableB b, tableA a 
WHERE b.parent_id = a.id AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180' FOR UPDATE;
UPDATE tableB SET...;
COMMIT;

Thank you!

Miroslav Glamuzina · Accepted Answer · 2019-02-19T12:07:53.007

1

Yes, this is possible. Please take a look at:

Here is an example of what your query may look like:

START TRANSACTION;
# Lock table using `FOR UPDATE`
SELECT 
  b.reserved, 
  b.owner 
FROM 
  tableB b,
  tableA a 
WHERE 
  b.parent_id = a.id 
  AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180' 
FOR UPDATE;

# Update query
UPDATE 
  tableA 
SET 
  tableA.column1=(
    SELECT 
      b.reserved 
    FROM 
      tableB b, 
      LEFT JOIN tableA a ON a.id=b.id 
    WHERE 
      b.parent_id = a.id 
      AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180'
  ) 
WHERE ... 
LIMIT 1;  

COMMIT;

Hope this helps,

edited Feb 19 '19 at 12:07

answered Feb 19 '19 at 11:34

Miroslav Glamuzina

4,472
2
19
33

thank you for answer. Can I ask where should the select..for update part goes into this? I need to make sure no one else has ability to write to the same row. – niks Feb 19 '19 at 12:00
So basically this means that I have to do two queries -one to select and lock the row and second to find it again and do the update? – niks Feb 19 '19 at 12:22
Yes, afaik. Is this what your looking for? – Miroslav Glamuzina Feb 19 '19 at 12:29
Yes, it is what I am looking for! Thank you! – niks Feb 20 '19 at 11:54

How to use update together with select...for update in mysql

1 Answers1