Laravel

Database Transactions and Resource Locking in Laravel

Database Transactions and Resource Locking in Laravel


Database transactions and pessimistic locking are probably not the most used features, however, they can be extremely useful. Let’s take a brief look and then examine how Laravel’s database layer supports these features.




In this post, we don’t focus on the plain SQL implementation of database transactions or locking. After explaining shortly, we move on the Laravel implementation of these features. However, you can find links that are providing longer and better explanations in the summary.


Database Transactions


First, let’s take a look at database transactions in general. So what is a transaction exactly? Very briefly: Transactions are “wrappers”, that isolate operation “groups” from the global scope.

While we are performing a non-isolated operation, the changes are undoable. For example, we run an UPDATE operation, we can’t revert it, the changes are permanent. However using database transactions, the isolated operations can be reverted.

Just imagine a situation, when we need to perform a more complex operation, and somewhere it goes wrong. If we can’t revert the changes back, our data structure or the data itself can be corrupted. But if we isolated the operation in a database transaction, we can roll back the transaction.

Let’s move on the Laravel implementation of transactions. First of all, we can use automatic rollbacks. That means if any exceptions were thrown during the transaction, the transaction will be rolled back automatically. Laravel also offers a “deadlock” as a parameter, that represents the number of tryings before the transaction fails. If the given closure runs without any error, the transaction will be committed.


use Illuminate\Support\Facades\DB;

// Without deadlock
DB::transaction(function () {
DB::table('comments')->where('post_id', $post_id)->delete();

// Other operations...
});

// With deadlock: after 3 tryings it will fail and roll back
DB::transaction(function () {
DB::table('users')->updateOrInsert(
['email' => '[email protected]', 'name' => 'John'],
['votes' => '2']
);

// Other operations...
}, 3);


We also can perform transaction tasks manually. This way we can have complete control over the transactions.


// Begin the transaction
DB::beginTransaction();

// Roll back the transaction
DB::rollBack();

// Commit the transaction
DB::commit();


Locking Resources


First of all, there is a different type of lockings, but here we talk about pessimistic locking. But what does it mean exactly? When we lock a “resource” – selected rows – we disable any updates performed on the selected rows. For this, we can use the sharedLock() method. However, we can also prevent selecting the locked rows from another shared lock and not only prevent updating, with the help of lockForUpdate() method.


// "shared lock"
DB::table('comments')->where('post_id', $post_id)->sharedLock()->get();

// "lock for update"
DB::table('comments')->where('post_id', $post_id)->lockForUpdate()->get();


Summary


As we can see, these features are more like safety-first solutions. Probably these are not applicable for every project, but for sure there are some situations – for example, migrating databases and data structures – where these can be a life-saver solution.