CODEWITHSUNDEEP
mysqlsqlselectsql-update
Mateusz Bartkowiak
Mateusz Bartkowiak

Reputation: 154

Does 'for update' row blocking works also for joined tables?

I make 2 queries in a transaction: SELECT (containing JOIN clause) and UPDATE. It is required that data in selected rows don't change before the update is done, so i'm using FOR UPDATE clause. My question is: does the 'for update' works only for part of data selected from table specified in FROM clause or for data from joined tables also? My DBMS is MySql.

Upvotes: 11

Views: 4904

Answers (1)

DevDelivery
DevDelivery

Reputation: 447

The documentation simply says that the lock is on rows read without excepting joined tables, so it should be on all records on all the joined tables. If you want to lock only the rows in one of the tables, you can do that separately: 'SELECT 1 FROM keytable WHERE ... FOR UPDATE'.

That said, this is not needed to simply prevent an update between the SELECT and UPDATE. The read lock on the SELECT already does this. The purpose of the FOR UPDATE would be to prevent another transaction from reading the rows and thus potentially causing a deadlock because the UPDATE can not be applied until the other transaction releases its read lock.

Upvotes: 8

Related Questions