Unlocking the Secrets of Database Locks: Yii2’s Approach to Reliable Transactions

In this post, we will explore a real-life problem caused by a lack of proper understanding of framework behavior and lock mechanisms. Around three years ago, we were working on a financial app where customers could deposit funds using different payment methods and utilize their balance for purchases simultaneously. Our application was developed in Yii2, which provided a solid foundation but also presented some unique challenges.

The Problem

Occasionally, we received complaints from customers that their balance was not reflecting correctly after making a deposit. This issue was random and did not affect all users. As expected, before any deposit or purchase, a database lock was applied to ensure data integrity. However, despite the locks, the problem persisted.

The developer responsible for this functionality assured us that all the code was working correctly, and he was puzzled by the sporadic nature of the issue.

Determined to get to the bottom of this, I decided to dig deeper into the problem. Upon reviewing the code and the locking mechanism, everything seemed perfect. Yet, the issue remained unresolved. After hours of investigation, I discovered that the framework we were using, Yii2, employed a shared-lock mechanism by default.

Shared-locking was insufficient for our scenario, where we had two types of users: salespersons making purchases and customers depositing funds. The shared-lock allowed multiple transactions to read the balance simultaneously, which caused inconsistencies when concurrent read and write operations occurred on the same balance row.

The Solution

To address this, we needed to apply exclusive-locking to the balance row. Unlike shared-lock, exclusive-locking ensures that both read and write operations are locked, preventing other transactions from accessing the data until the lock is released. The solution was to simply add “FOR UPDATE” to the SQL query, but its impact was huge, ensuring that the balance was accurately updated, regardless of concurrent transactions.

Here’s how you can manage shared-lock and exclusive-lock in Yii2:

Shared-Lock Example:

//Starting a transaction
$transaction = Yii::$app->db->beginTransaction();
try {
    // Applying shared lock
    $sql = "SELECT * FROM balance WHERE agencyID = :agencyID and balance=:balance";
    $balance = Yii::$app->db->createCommand($sql, [':agencyID' => $agencyID, ':balance' => $balance])->queryOne();
    // Remaining Code ...
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
    throw $e;
}

Exclusive-Lock Example:

// Starting a transaction
$transaction = Yii::$app->db->beginTransaction();
try {
    // Applying exclusive lock
    $sql = "SELECT * FROM balance WHERE agencyID = :agencyID and balance=:balance FOR UPDATE";
    $balance = Yii::$app->db->createCommand($sql, [':agencyID' => $agencyID, ':balance' => $balance])->queryOne();
    // Remaining Code ...
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
    throw $e;
}

In the exclusive-lock example, FOR UPDATE is used to apply an exclusive lock on the selected rows. This ensures that both read and write operations are locked, preventing other transactions from accessing the data until the lock is released. In the shared-lock example, no specific locking clause is applied, allowing multiple transactions to read the data simultaneously but preventing other transactions from writing to the data.

This experience underscored the importance of understanding the nuances of database locking mechanisms. By switching from shared-lock to exclusive-lock in Yii2, we were able to resolve the issue and restore the accuracy of our financial app’s balance updates. It’s crucial for developers to be aware of the locking strategies employed by their frameworks and to choose the appropriate one based on their specific use cases.

In future posts, I will cover the details of shared-lock and exclusive-lock in greater depth, providing proper illustrations and examples to help you understand when and how to use each locking mechanism effectively.

Stay tuned for the next post, where I’ll dive into more technical details and provide further insights into optimizing database transactions in Yii2.