Sample Header Ad - 728x90

Using SELECT ... FOR UPDATE as a way to lock a whole table

0 votes
1 answer
549 views
I know I can lock a whole table using LOCK TABLE, but I am just curious if I can get the same result by using the FOR UPDATE statement, assuming that all transactions that update that table select a specific row from that table using FOR UPDATE at the beginning of their transaction (or maybe having a separate table called locks with one unique column named lock_name and using SELECT lock_name FROM locks WHERE lock_name = 'foo' FOR UPDATE to acquire a lock before each transaction)? I am asking this question because I have to save a tree data structure in a database table using either an adjacency list or a closure table. When multiple users try to add a new node or relocate a sub-tree, I have to select some rows and do some calculations and then update the table based on those calculations and this operation is not possible unless I lock the whole table.
Asked by Dante (177 rep)
Nov 8, 2024, 07:22 AM
Last activity: Nov 9, 2024, 01:32 AM