Menu
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.
224
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement. Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.
Update lock does lock entire table's all rows.
To query data from related tables, you often use the join clauses, either inner join or left join. In SQL Server, you can use these join clauses in the UPDATE statement to perform a cross-table update. In this syntax: First, specify the name of the table (t1) that you want to update in the UPDATE clause.
SQL Server only has the FOR UPDATE as part of a cursor. And, it only applies to UPDATE statements that are associated with the current row in the cursor. So, the FOR UPDATE has no relationship with INSERT .
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
