At what point is lockForUpdate unlocked?

If I need to keep some rows locked from being selected by other users in a DB transaction so they will not be used until the end of the transaction, at what point does lockForUpdate "unlocks" these rows again? Is it after the transaction is over or only after it finishes the select query?

Example:


try {
DB::beginTransaction();

DB::table('table_1')
        ->whereIn('value', $values)
        ->lockForUpdate()
        ->get();

DB::table('table_2')->insert($values);

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
}

Will the rows that contain $values on table_1 be locked until I finish the transaction, or only until the the select query from table_1 is finished?

LaryAI

3 years ago

Level 58

According to the Laravel documentation, the lockForUpdate method will lock the selected rows until the end of the transaction. This means that in the example provided, the rows that contain $values on table_1 will be locked until the transaction is either committed or rolled back.

Therefore, other users will not be able to select or modify these rows until the transaction is completed. The lock will be released automatically when the transaction is finished, regardless of whether or not a select query is executed on table_1.

Code Example:

N/A

1 like

Ligonsker

3 years ago

Level 8

The bot could not get a code example, because I probably used whereIn and the docs used where.

So I was wondering - is it possible to user whereIn, and it will really lock all the rows resulting in that whereIn?

newbie360

3 years ago

Level 24

@ligonsker

#1. open your terminal and the web browser side by side

#2. example controller code

try {
    DB::beginTransaction();

    DB::table('users')
            ->whereIn('id', [1, 2, 3])
            ->lockForUpdate()
            ->get();
    
    sleep(30);

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
}

#3. go to the route trigger the controller code and fast open Tinker paste this

DB::table('users')->whereIn('id', [1, 2, 3])->sharedLock()->get();

#4. you can see Tinker is waiting to show the result

#5. when the browser finish the process, you can see Tinker show the result at the same time

so it mean whereIn() is wroked

PS. if i set sleep(60), i got this error

Illuminate\Database\QueryException SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

1 like

Ligonsker

3 years ago

Level 8

@newbie360 Thank you, what you did was to check that the tinker query worked only after the web request query finished after sleep(30), meaning that it was locked indeed that's why it only showed up after the browser one finished?

newbie360

3 years ago

Level 24

@Ligonsker the simple answer is YES

lockForUpdate() is prevent another read/write

select <...> from table for update

sharedLock() is prevent another write

select <...> from table lock in share mode

and one more important note, i'm not sure is it true, may be someone can give an answer ;)

avoid to query non-indexed column with any lock method, because it will lock the table instead of lock the row

1 like

Please or to participate in this conversation.