Add locking mechanism limitation to readme

[Swanty]

Using locking mechanisms requires usage of raw sql queries to bypass Lada Cache. https://laravel.com/docs/5.6/queries#pessimistic-locking

I'm using lockForUpdate, but it obviously won't work if there is caching in-between, because it is not possible to tell the DB to lock something if we never even reached the DB right? :smile_cat: The solution is simply resort to raw query i.e. using the \DB::xxx statements.

Here is an example that I use to bypass Lada Cache and then transform result array back into model object:

        $table = (new YourAmazingModel())->getTable();

        $items = \DB::table($table)
            ->lockForUpdate()
            ->where(' ... filter results ... ')
            ->orderBy('id', 'asc')
            ->take(15)
            ->get();

        // Transform array to YourAmazingModel
        foreach ($items as $key => $item) {
            $items[$key] = (new YourAmazingModel())->newFromBuilder((array)$item);
        }

        $items = collect($items);

        // Now you can loop $items and update them as necessary to release the row locks

Note: Do not forget to wrap your code in transaction so that you can revert the lock in case of exception and avoid deadlocks.

[Swanty]

P.S. In case someone wants to use JOINs in their SQL query then the previous example might give unexpected issues (at least for me with PostgreSQL) because DB is confused about which table is used for row locking.

Here is an example how to avoid that:

        $table = (new YourAmazingModel())->getTable();
        $table_alias = 'yam';

        $items = DB::table("{$table} AS {$table_alias}")
            ->select("{$table_alias}.*")
            ->leftJoin(' ... left JOIN some other table ... ')
            ->where(' ... filter results by JOINed table ... ')
            ->where(' ... filter results by main (yam) table ... ')
            ->orderBy("{$table_alias}.id", 'asc')
            ->take(15);

        // This is same thing as "lockForUpdate()", but to avoid conflicts with other
        // tables when using JOINs we specify which exact table is used for row locking.
        $sql = "{$items->toSql()} FOR UPDATE OF {$table_alias}";
        $items = $items->getConnection()->select($sql, $items->getBindings(), false);

        // Transform array to YourAmazingModel
        foreach ($items as $key => $item) {
            $items[$key] = (new YourAmazingModel())->newFromBuilder((array)$item);
        }

        $items = collect($items);

        // Now you can loop $items and update them as necessary to release the row locks

[spiritix]

Thanks for the explanations. We'll definitely need to implement support for locking mechanisms in Lada Cache. PRs are welcome.