Database Transactions in Laravel
Laravel's documentation on Database Transactions describes wrapping our database calls within a closure. What if we need more power? Let's dig in to see what's going on behind the scenes, and what tools we have to work with Database Transactions in Laravel.
What are Database Transactions?
You may already know what a transaction is. However, let's review! A transaction gives you the ability to safely perform a set of data-modifying SQL queries (such as insertions, deletions or updates). This is made safe because you can choose to rollback all queries made within the transaction at any time.
For example, let's pretend we have an application which allows the creation of accounts. Each account can have one or more users associated with it. If this application creates an account and the first user at the same time, you need to handle what happens when the account was created successfuly, but the user is not.
In this sample code:
// Create Account
$newAcct = Account::create([
'accountname' => Input::get('accountname'),
]);
// Create User
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id,
]);Two situations can cause issues:
Account was not created.
If the account was not created, there's no id available to pass to the user for its account_id field. In this scenario, the account and user will fail to be created, so there isn't necessarily disparate data in the database. We just need to handle that situation in code (not shown above).
User was not created.
If, however, the account was created, but the user was not, then we run into issues. You now have an account with no available users, and there is disparity in the database data. You can either code around that, and every other possible data disparity edge-case in your application, or you can wrap this in a transaction and be done with it!
Our Transactional Toolset
Database transactions consist of three possible "tools":
- Creating a transaction - Letting the database know that next queries on a connection should be considered part of a transaction
- Rolling back a transaction - Cancelling all queries within the transaction, ending the transactional state
- Committing a transaction - Committing all queries within the transaction, ending the transactional state. No data if affected until the transaction is committed.
Table and/or row locking is important to know about as well, especially on high-traffic sites. However, I won't cover that here. See MySQL Transactional Locking with InnoDB and/or PostgreSQL transaction isolation. Perhaps read on about ACID and Concurrency Control.
The previous sample code can be pseudo-coded with transactions as such:
// Start transaction
beginTransaction();
// Run Queries
$acct = createAccount();
$user = createUser();
// If there's an error
// or queries don't do their job,
// rollback!
if( !$acct || !$user )
{
rollbackTransaction();
} else {
// Else commit the queries
commitTransaction();
}Basic Transactions in Laravel
The first way to run a transaction within Laravel is to put your queries within a closure passed to the DB::transaction() method:
DB::transaction(function()
{
$newAcct = Account::create([
'accountname' => Input::get('accountname')
]);
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id,
]);
});One thing that's not evident is the answer to this question: How does this code know to rollback or commit the transaction?
We can find out by looking at the code behind the scenes:
public function transaction(Closure $callback)
{
$this->beginTransaction();
// We'll simply execute the given callback within a try / catch block
// and if we catch any exception we can rollback the transaction
// so that none of the changes are persisted to the database.
try
{
$result = $callback($this);
$this->commit();
}
// If we catch an exception, we will roll back so nothing gets messed
// up in the database. Then we'll re-throw the exception so it can
// be handled how the developer sees fit for their applications.
catch (\Exception $e)
{
$this->rollBack();
throw $e;
}
return $result;
}
// try...catch
try {
// Transaction
$exception = DB::transaction(function() {
// Do your SQL here
});
if(is_null($exception)) {
return true;
} else {
throw new Exception;
}
}
catch(Exception $e) {
return false;
}Very simply, if an Exception of any kind is thrown within the closure, then the transaction is rolled back. This means that if there's a SQL error (one that would not normally fail silently), then the transaction is rolled back. More powerfully, however, this means that we can throw our own exceptions in order to rollback a transaction. Something like this:
DB::transaction(function()
{
$newAcct = Account::create([
'accountname' => Input::get('accountname')
]);
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id,
]);
if( !$newUser )
{
throw new \Exception('User not created for account');
}
});Advanced Transactions in Laravel
I recently found myself needing more control over handling transaction. My create() methods also handled validation by throwing a custom ValidationException if there was a validation issue. If this exception was caught, the server responded by redirecting the user with the error messages.
try {
// Validate, then create if valid
$newAcct = Account::create( ['accountname' => Input::get('accountname')] );
} catch(ValidationException $e)
{
// Back to form with errors
return Redirect::to('/form')
->withErrors( $e->getErrors() )
->withInput();
}
try {
// Validate, then create if valid
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id
]);
} catch(ValidationException $e)
{
// Back to form with errors
return Redirect::to('/form')
->withErrors( $e->getErrors() )
->withInput();
}Conversations about this use of Exceptions aside, how would I put this in a transaction if the ValidationExceptions were always caught? Simply putting this inside of a DB::transaction() call would guarantee it would never trigger a rollback if the validation failed on the creation of either account or user.
Looking more closely at the database code, however, we can see that we can manually call beginTransaction, rollback and commit! Putting the above code into a transaction was then as simple as:
// Start transaction!
DB::beginTransaction();
try {
// Validate, then create if valid
$newAcct = Account::create( ['accountname' => Input::get('accountname')] );
} catch(ValidationException $e)
{
// Rollback and then redirect
// back to form with errors
DB::rollback();
return Redirect::to('/form')
->withErrors( $e->getErrors() )
->withInput();
} catch(\Exception $e)
{
DB::rollback();
throw $e;
}
try {
// Validate, then create if valid
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id
]);
} catch(ValidationException $e)
{
// Rollback and then redirect
// back to form with errors
DB::rollback();
return Redirect::to('/form')
->withErrors( $e->getErrors() )
->withInput();
} catch(\Exception $e)
{
DB::rollback();
throw $e;
}
// If we reach here, then
// data is valid and working.
// Commit the queries!
DB::commit();Note that I also catch a generic Exception as a last-ditch maneuver to ensure data integrity, just in case any other exception other than a ValidationException is thrown. Because this strategy costs us our previously discusssed automatic protection against exceptions, it's prudent to add in this precaution.
That's it! We have full control over database transactions within Laravel!
