More about locks and in laravel in particular?

Question

MySQL

Good day to all!
I really carefully read the laravel documentation about locks ( https://laravel.com/docs/5.1/queries#pessimistic-l... ), but since I have never worked with locks before, it is hard to understand how to behave correctly in the application. Can you give any example? For example, when to use sharedLock and when to use lockForUpdate.
What if, for example, I have 2 related tables, for example, with a 1 to 1 relationship.
For example, there is a Book table (Book) and a table with detailed information about this book (BookInfo).
How can I make sure that there is only one entry about Book in BookInfo, so that while I check in the application, while I prepare the entry, at that moment another thread has not added anything to BookInfo about this book?
An example of tables:
book
id, name
book_info
id, book_id, year
I want to clarify right away: I understand that this example is unsuccessful, and that the problem is easy to avoid, but I want to consider the example specifically with locks.
Preferably, with an example of how to do this in laravel.

Answer 1

You need mutexes . It can be implemented in different ways. But if you are planning a distributed application, then you can make it based on mysql using GET_LOCK() and RELEASE_LOCK() .
Unfortunately, I did not find a framework ready for laravel, but you can do it yourself by looking at how others have done it .
The task is to achieve application execution in one thread. The mutex can be acquired before the action starts and released after execution.

mysql> SELECT GET_LOCK('name', 10); //первая сессия.
mysql> SELECT GET_LOCK('name', 5) //вторая сессия. будет висеть, пока не истечет таймаут в 10 секунд или пока первая сессия не выпустит SELECT RELEASE_LOCK('name');

Locks can be named as "controllerName:ActionName". This means that if the first thread took GET_LOCK("controllerName:ActionName", 10); then the second thread to the same action will hang and wait until the first thread releases the lock. Thus, we ensure that all requests to the server will be executed synchronously.
Also, you can reproduce the race condition and see what will happen with the application, without mutexes and with them. This can be achieved using curl in the terminal:
As a result, two simultaneous requests will be sent to the application.
Native sessions are also worth mentioning when it comes to PHP. Since they are implemented using files, one of the requests will hang, as it will not be able to get write access to the session file while another thread is working with it. This may give the developer the false impression that their code is synchronous and therefore not subject to race conditions, but this is not the case. Here you need to study the code in more detail, at what points the session file is locked, and at what points in the program the file lock is released.

Answer 2

he won't add it. protocol database

Answer 3

If you are using PostgreSQL, I would recommend using advisory locks (see https://www.postgresql.org/docs/current/static/exp... , at the end of the page). In essence, this is the implementation of mutexes at the database level.
The PHP trait that uses advisory locks can be found here: https://gist.github.com/dfbag7/75dcd78a33a6a6d8b61... This trait can be attached to model classes. See also scope implementation: https://gist.github.com/dfbag7/75a0a1ca843e5e14258... The code was tested (and actually used) in Laravel 4.2.