When designing applications that use databases, we often encounter situations involving concurrent access to data. This can have various implications - the database state might become incorrect, or some data might be lost. To prevent such scenarios, we can use different methods of controlling access to resources, such as optimistic or pessimistic locking.
Let's imagine a situation where two users are trying to update the same record using some application:
As you can see in the diagram above, the first user retrieves a record from the account table and attempts to debit the account by $70 based on the received data. At the same time, the second user performs the same operation, based on the data fetched from database. After these two updates, the account balance will be negative, which we want to avoid for the purpose of this article. How can we prevent such situations at the database level?
In MySQL 8, we can achieve this in two ways: by using pessimistic or optimistic locking.
Pessimistic Locking
In this type of locking, we prevent other users from performing operations on data tables or rows until the transaction is completed. Unlike optimistic locking, this mechanism is built into the database, granting us exclusive access to a specific resource. We distinguish between two types of locks: shared and exclusive. Since the example above concerns individual rows in the database, I'll focus here only on locking rows, not entire tables. Worth to mention that both table locking and individual record locking can be used in parallel.
Types of Locks
As mentioned above, for row-level locking, we can use two types of locking - shared and exclusive.
Shared lock
Shared locking allows transactions that hold this type of lock to only read the same records. This means that other transactions cannot perform any operations on them, such as modifications or deletions.
We can acquire this type of lock by adding FOR SHARE at the end of the select query, for example:
START TRANSACTION;
SELECT * from accounts WHERE owner_id = 1 FOR SHARE;
# do something
COMMIT;
Ending the transaction, either with COMMIT or ROLLBACK, releases the lock on the rows. If another transaction tries to set an exclusive lock on the same record, it will have to wait until the shared lock is released.
Exclusive lock
This type of lock not only prevents modification but also prevents reading of locked records. If another transaction attempts to set a shared or exclusive lock on these rows, it will have to wait for the current transaction to release the lock.
We establish the exclusive lock by adding FOR UPDATE at the end of the select query, for example:
START TRANSACTION;
SELECT * from accounts WHERE owner_id = 1 FOR UPDATE;
# do something
COMMIT;
If we retrieve such a record in another transaction without using any locking, it will be returned, but no operations can be performed on it until the lock is released.
Locking Ranges of Rows
Above I showed how to lock individual records. MySQL also allows to lock entire ranges of data. For example, SELECT * FROM accounts WHERE id > 1 FOR UPDATE locks all records with an id greater than 1 and prevents new records from being inserted.
If we try to lock a range that has no records (max id = 10, and we try to lock id > 100), we can set a different lock on that range in another transaction.
We can also establish such a lock using criteria other than the identifier:
SELECT * FROM user WHERE first_name = 'John' FOR UPDATE
The above query locks all rows where first_name = John. Retrieving such a row or inserting a new one with the specified column value will not be permitted.
Other Locking Options
For the above queries, or more specifically, the FOR SHARE and FOR UPDATE clauses, we can add two more options. The first one is NOWAIT. This option prevents the query from waiting for locked rows to be released and throws an error if it fails to retrieve the data immediately. The second option is SKIP LOCKED. It causes the query to retrieve only those rows that are not currently locked by another transaction.
Examples of using these options are as follows:
SELECT * FROM user WHERE first_name = 'John' FOR UPDATE NOWAIT
SELECT * FROM user WHERE first_name = 'John' FOR UPDATE SKIP LOCKED
Dead locks
With pessimistic locking, you may encounter a situation where multiple threads attempt to retrieve and lock the same row. If the row is not released within a specified time (default is 50 seconds in MySQL), waiting transactions will throw an error:
[40001][1205] Lock wait timeout exceeded; try restarting transaction
Optimistic Locking
Optimistic locking is not a feature of MySQL. This is a strategy where we take the version number of a record and check if it has changed when the data is updated. For example, let's say our account table has an additional column - version.
| id | company_id | balance | version |
|---|---|---|---|
| 1 | 1 | 100 | 1 |
We retrieve a record from the database using a select query:
SELECT * FROM account WHERE id = 1;
Then, when we want to update the record, we use the version column, for example:
UPDATE account SET balance = balance - 70, version = version + 1 WHERE id = 1 AND version = 1
In the UPDATE clause, in WHERE section, we use the version number retrieved in the select query.
The above approach ensures that if another thread updated the record after we retrieved it from the database, our update will fail because the version number will be different. This is the essence of the optimistic locking - if the number of updated rows is 1, we can consider the operation successful. If no rows were updated, we can for example handle this situation by throwing an exception in our application or handling it differently.
The version doesn't necessarily have to be an integer. Other types, like dates or checksums, can be used for this purpose.
Which Locking to Choose?
When it comes to selecting the appropriate locking strategy in MySQL, the decision largely depends on the specific use case and requirements of the application.
Pessimistic locking may be a suitable choice in scenarios involving batch processing, particularly when multiple consumers are involved. In such cases, where each row needs to be processed, it is crucial to prevent multiple consumers from selecting the same row simultaneously. By using the FOR UPDATE SKIP LOCKED clause, you can ensure that only one consumer locks and processes a specific row, while others skip over it and proceed to the next available row. This approach helps maintain data integrity and prevents conflicts in batch processing scenarios.
On the other hand, optimistic locking may be a preferred strategy when there is no risk of repeating the operation causing side effects. It is particularly suitable when your application does not involve calling external APIs or sending emails during the operation. In such cases, conflicts resulting from optimistic locking are unlikely to have significant consequences. By allowing multiple transactions to access and modify the same data concurrently, optimistic locking improves efficiency and performance, especially in scenarios where conflicts are infrequent.
Conclusion
The choice between pessimistic and optimistic locking depends on factors such as the expected contention level, the criticality of data integrity, and the performance requirements of your application. Optimistic locking is suitable when conflicts are infrequent, and efficiency is a priority. On the other hand, pessimistic locking ensures data integrity but may impact performance in highly concurrent environments. Understanding the characteristics and demands of your application will guide you in selecting the appropriate locking strategy.
Top comments (0)