Laravel 5: Query Builder Helpers – locks

Query Builder: locks


Page content:


All example will use same controller method – as all the action would happen in model method:


General info

At any time, there is a lot of CRUD going on on your website.

In some cases you do not want anyone to perform any action with a record, you are working with.
I such case you may take possessiong of a given record using DB lock.

Laravel offers 2 methods to deal with pessimistic locking.
To find out more about what is pessimistic/optimistic locking – check this StackOverflow answer.

 


sharedLock

First, lets see what is a shared lock.

Shared lock is, when more than one transaction is granted read access to a given record.
One transaction gets shared lock on a record.
Then another transaction can request (and get) shared lock on same record.
All transactions using record under shared lock have read-only access to a record and they cannot do any changes, like writing, or deleting, until shared lock is released.
Locking prevents also – besides writing/deleting – reading records that are not ready (finished).

Lets see some example.
We want to see all tasks created today (lets assume today is 2011-11-11) untill time of this query.
So, you do not want to have anything that’s in the works and that can be added when your query is working on it.
I know, it is a silly example, but will do as explanation:

Above gives you this raw SQL code

NOTE:
Instead of using:

… you can use:

It is the same thing (alias).


lockForUpdate

A few words about, what lockForUpdates really is.

According to MySQL devman (once on that page, scroll down a bit):

What is means – in English 🙂 – is that you can lock a record, or more than one record..
You specify what you are locking in your where clause (see example).

You can use it, when reading to prevent the rows from being modified or from being selected with another shared lock.
Or you can lock some row(s) until your transaction finishes updating.

It is very useful, but overused can cause bottlenecks.

Read more about it in this StackOverflow example.

Some example code:

In above example, we are locking all records created after 2016-11-24.

Raw SQL looks like this:

NOTE:
Instead of using:

… you can use:

It is the same thing (alias).