Database Locking to Solve Race Condition
Database Locking to Solve Race Condition
Example implementation of database locking in SQL: pessimistic locking and optimistic locking
Quick Links:
Opening, Race Condition & Database Locking
Hello, today we are going to discuss Database Locking and how to use it in real-world scenarios. This is an important subject, especially for software engineers when developing an application. In my opinion, this knowledge should be known by other stakeholders such as product managers and QA.
I saw a lot of beginner forget to put locking in important use case leading to bugs and errors. The impact that cause of forget to lock database can be severe, because it can cause the datas inconsistent and need a lot of time to fix the datas.
Before we go further with Database Locking, we need to understand the concept of a Race Condition. In the context of databases, a race condition occurs when multiple transactions attempt to access and modify the same data concurrently. If the transactions do not coordinate their access properly, one transaction's changes may be overwritten by another transaction's changes, leading to data inconsistencies and incorrect results.
To prevent race condition, Database Locking can be implemented. Database locking is a fundamental concept in database management systems that ensures the consistency and correctness of concurrent transactions. By implementing locking, it ensuring that only one transaction can access and modify a shared resource at a time.
Who Need to Know about Database Locking?
Before we go further, when and how to implement database locking, this knowledge is meant not only for Software Engineer.
For software engineer, they needs to be familiar with database locking to ensure that their code correctly manages transactions and avoids race conditions. They should be able to implement locking mechanisms, understand their performance implications, and choose the right type of lock for the specific use case.
In addition to software engineers, Product Managers also need to be aware of race condition and locking and understand the trade-offs of different locking strategies. By communicating this knowledge to their team, product managers can make product requirements more detailed and anticipate potential edge cases.
QA professionals also need to understand locking to ensure that the application behaves as expected under high concurrency and that it can handle conflicts correctly. They should be able to design tests that simulate high load and test the application's ability to handle concurrent requests.
When We Need Database Locking?
As we already mention before, database locking is needed to handle Race Conditions related to the database. Therefore, we can ask a question "When do race conditions potentially occur?"
Race conditions usually occur when multiple threads or processes access and modify shared resources concurrently, and the final outcome of the operations is dependent on the timing and order in which the operations are executed.
By knowing that, race conditions usually happens when modifying shared resources, we can safely assume that we need to be careful when developing an application or features that have a high intensity of updating the data (update heavy).
How to use Database Locking through SQL
To understand more about database locking, we are going to explore locking in SQL with a real-world scenario.
Here is the database schema we will be using to explore:
What happens when two transactions want to use the DISC60 code at the same time?
That's true, a race condition can occur and potentially make the remaining_limit become negative. In the e-commerce industry, this bug can lead to major losses. Just imagine if thousands of vouchers leak because of this bug, how much loss the company would have to suffer.
To solve this issue, we are going to explore two locking strategies that we can implement in SQL:
Optimistic Locking
Pessimistic Locking
Pessimistic Locking in SQL
Pessimistic locking is a locking strategy that is achieved by acquiring a lock on a database record or set of records before performing any modifications on it, and holding the lock until the transaction is complete.
SELECT ... FOR UPDATEIn SQL, pessimistic locking can be achieved through the statement above. This statement locks the selected rows in a table and prevents other transactions from modifying them until the current transaction is completed.
Let's implement pessimistic locking on the use case that we have mentioned before:
sql.Exec("BEGIN TRANSACTION")
var voucher_info = sql.Query(`
SELECT *
FROM voucher_code
WHERE code = 'DISC60'
FOR UPDATE
`)
if voucher_info.RemainingLimit > 0:
sql.Exec(`
UPDATE voucher_code
SET remaining_limit = remaining_limit - 1,
updated_at = NOW()
WHERE code = 'DISC60'
`)
sql.Exec("COMMIT")
Pessimistic locking has the advantage of completely avoiding conflicts, thereby you don’t have to deal with the situation where you have a conflict. This is the main benefit of using pessimistic locking.
On the other hands, the drawback of pessimistic locking:
Lack of flexibility, for example when there is a use case to wait for user input
Deadlock more potential to happen, therefore requires careful implementation and management to ensure that locks are acquired and released correctly
Pessimistic locking can lead to decreased concurrency and slower performance, as transactions must wait for each other to release locks on shared resources
Optimistic Locking in SQL
Unlike pessimistic locking, which acquires locks on data to prevent other transactions from modifying it, optimistic locking assumes that concurrent modifications are unlikely and allows multiple transactions to access the same data simultaneously.
In optimistic locking, each transaction reads the data it needs and then checks whether the data has been modified by another transaction before committing its changes. If the data has been modified by another transaction, the transaction will abort and retry the operation with the updated data.
Since SQL has ACID theorem, optimistic locking is often implemented using a version control mechanism. One way to achieve it is by adding version column where it is incremented each time the row is modified.
But we can used another way for version control, such as udpated_at column. For the use case that we have mentioned before, we can use remaining_limit because the usecase that we want to ensure no race condition is only when the limit become negative.
# Ensure the voucher_code exist and the remaining limit still > 0
SELECT *
FROM voucher_code
WHERE code = 'DISC60'
# Try to deduct the remaining_limit
UPDATE voucher_code
SET remaining_limit = remaining_limit - 1,
updated_at = NOW()
WHERE code = 'DISC60'
AND remaining_limit > 0
# if updated row_count is 0, either you return error or loop the processThe main benefit of optimistic locking is allows for high concurrency and scalability, as transactions do not block each other and can proceed concurrently. Also this locking avoids the overhead of acquiring and releasing locks and improved the performance as well as decreasing the potential of deadlock.
The downside of using optimistic locking is if a conflict does occur, you need to deal with your operation being aborted. This can be annoying, especially when your application has a complex logic.
Conclusion
Database locking is an important concept in database management systems that ensures the consistency and correctness of concurrent transactions. It is crucial to prevent race conditions that can lead to data inconsistencies and incorrect results
When there is update heavy when implementing features/application, we need to be careful and don't miss to analyze the potential of race condition. It is important for software engineers, product managers, and QA professionals to understand locking and its trade-offs to ensure the application behaves as expected under high concurrency and can handle conflicts correctly. By implementing database locking, you can prevent bugs and loss potential cause by race condition
Thank you for reading today's newsletter! If you find it valuable, here are some actions you can take:
✉️ Subscribe — if you aren’t already, consider becoming a paid subscriber.
❤️ Share — you can help spread the word by sharing the article with your team or someone who might find it useful