0

suppose we have a query like this

SELECT * FROM WORKER,
   (
       SELECT worker_id, time_to_work FROM JOB 
       WHERE job_id = 7 AND time_to_work < now() 
   ) AS work
   WHERE WORKER.worker_id = work>worker_id AND WORKER.occupied = 0

after getting the free workers that can do job number 7 right now, we would want to

1- set returned workers occupied column to 1

2- set time_to_work of those jobs (I mean job 7 of returned workers) to 24h into the future.

now the problem is that we might return same worker to two different clients, as two transactions may access the same worker cause we did not use for update.

but where should I put the for update?

if I put for update in the inner select, would that solve anything?

if I put for update in the outer select or in both of them wouldn't that cause deadlocks?

governor
  • 11
  • 2
  • 1
    Instead of an inner `SELECT` why not a `JOIN`? – tadman Jun 07 '23 at 14:26
  • @tadman I would have done that from start but it is legacy code, I got it from someone else. I have no problem changing that if it solves the problem. but 1- does that actually lock rows from both tables? 2- for the sake of the question what happens if we use inner select? – governor Jun 07 '23 at 14:53
  • Unless you're doing updates you shouldn't have any locks. There might be other queries that create locks and this query fails because it can't read the table, leading to it failing because of an *apparent* deadlock. Keep an eye on `SHOW PROCESSLIST` to see what might be going on, and if you have locking issues `SHOW ENGINE STATUS` can help identify what is locked. – tadman Jun 07 '23 at 15:03
  • 1
    The documentation at https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html seems to suggest you would need the `for update` clause in both. "A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery." Your example doesn't seem significantly different than their example. – EdmCoff Jun 07 '23 at 19:53
  • Having said that, I wonder if maybe using one of the solutions at https://stackoverflow.com/questions/11477121/mysql-return-updated-rows could work for you, rather than using `for update`. – EdmCoff Jun 07 '23 at 19:54
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jun 08 '23 at 04:53
  • @tadman , @EdmCoff it turns out that using `FOR UPDATE` in `JOIN` only locks the rows in the intermediate table that MySQL creates(is it called view?) also when using nested `SELECT`, using `FOR UPDATE` in inner select locks rows of `JOB` table, but using `FOR UPDATE` in outer select is the same as the case of `JOIN` – governor Jun 08 '23 at 11:12

0 Answers0