Pessimistic Locking in Laravel

A database transaction (DB transaction) is a unit of work that is either completed as a unit or undone as a unit [Source].

Laravel allows you to start database transactions using either the DB::transaction() or DB::beginTransaction() methods.

The DB::transaction() accepts a closure, and can optionally return a value:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use App\Models\Post;
use App\Models\Log;

// The transactio won't be comitted if an exception ocurr
$postId = 1;
$post = DB::transaction(function() use ($postId) {
		$post = Post::findOrFail($postId);

		$post = $post->update([
			'title' => $request->input('title'),
			'content' => $request->input('content'),
		]);

		Log::create([
			'post_id' => $post->id,
			'user_id' => Auth::id(),
			'action' => 'created',
		]);

		return $post;
));

echo "You created a post with id ".$post->id;

On the other hand, DB::beginTransaction() provides more flexibility, because you explicitly start, commit and rollback the transaction:

try {
		DB::beginTransaction();

		// Find and update the post


		// Log it

		DB::commit();
} catch (Throwable $exception) {
		DB::rollBack();

		throw $exception;
}

That covers the basics of transactions.

#What if...?

What if somebody else deletes the post while you are saving the log entry? The logging fails.

In a system with thousands of writing queries, this is a real possibility.

$post = DB::transaction(function() {
		// We get the post by id
		$post = Post::findOrFail($postId);

		// Update post

		// Somebody else deletes the post before logging it
		Log::create([]);

		return $post;
));

The transaction will fail and the log entry will not be saved.

#Use Pessimistic Locking

Pessimistic Locking prevents the selected rows from being modified until the transaction gets committed.

In this case, nobody else can modify the post until you are done with your transaction.

Read more about the differences between Optimistic Locking and Pessimistic Locking

You achieve this using the sharedLock method in Laravel:

$post = DB::transaction(function() use ($postId) {
		$post = Post::sharedLock()->findOrFail($postId);

		// ...
});

The sharedLock method tells the database to lock the rows until the transaction is complete.

You can also use lockForUpdate which prevents the selected records from being modified or from being selected with another shared lock.

Read more about Pessimistic Locking in Laravel.

#Summary

• Database transactions -- wrap related database operations into a single unit of work that either fully completes or fully rolls back. Use DB::transaction() for simple cases and DB::beginTransaction() when you need explicit control.
• Pessimistic Locking with sharedLock() -- prevents other processes from modifying the selected rows until your transaction commits. Use this when concurrent writes could corrupt your data.
• lockForUpdate() vs sharedLock() -- lockForUpdate is stricter: it blocks both modifications and shared lock selections on the affected rows.