Optimistic vs Pessimistic Locks

Test Content

Blah Blah

START TRANSACTION;
# write your queries
COMMIT;

Broken Transaction Example

Serializable Transaction Example

Pessimistic locking

Blah Blah

SELECT * FROM user_balance WHERE user_id=x FOR UPDATE

DB.StartTransaction()

balance = DB.Table("user_balance").
    SelectForUpdate("balance").
    Where("user_id = ?", userID)

balance = balance - 150

DB.Table("user_balance").
    Where("user_id = ?", userID).
    Update("balance = ?", balance)

DB.Commit()

Optimistic locking

Blah Blah

for {
    balance, updatedAt = DB.Table("user_balance").
        Select("balance", "updated_at").
        Where("user_id = ?", userID)

    balance = balance - 150

    affected_rows = DB.Table("user_balance").
        Where("user_id = ?", userID).
        Where("update_at = ?", updatedAt).
        Update({
            "balance":    balance,
            "updated_at": time.Now(),
        })

    if affected_rows > 0 {
        break
    }
}

Sources:

• Brightbox
• Sciencedirect
• Canertosuner