Скачать или смотреть Exploring FOR UPDATE SKIP LOCKED Alternatives in MariaDB

Discover efficient and performant methods to handle job queueing in MariaDB without using `FOR UPDATE SKIP LOCKED`.
---
This video is based on the question https://stackoverflow.com/q/64271601/ asked by the user 'gustav' ( https://stackoverflow.com/u/8419900/ ) and on the answer https://stackoverflow.com/a/64271724/ provided by the user 'ysth' ( https://stackoverflow.com/u/17389/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Alternative to skip locked in mariaDB

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Alternatives to FOR UPDATE SKIP LOCKED in MariaDB

When it comes to job queueing in databases, one primary challenge developers face is how to handle record locking efficiently. For many, MariaDB's FOR UPDATE SKIP LOCKED functionality is a go-to solution. However, not all scenarios in MariaDB can utilize this feature, or it might not provide the level of performance needed. This raises the question: Is there an alternative to FOR UPDATE SKIP LOCKED in MariaDB? Let’s explore some effective strategies for managing job queues without running into locking issues.

Understanding the Challenge

The main issue with record locking in a job queue scenario arises when multiple workers or threads attempt to process the same record simultaneously. When a worker locks a record, other workers attempting to process the same record must wait, potentially leading to performance bottlenecks. Therefore, finding an approach that allows for parallel processing without sacrificing data integrity is vital.

Solution 1: Using a Processing Column

The Concept

Instead of relying on database locks to indicate processing status, you can implement a processing column within your queue table. Here’s a breakdown of how this works:

Implementation Steps

Add a Processing Column: Create a boolean or integer column named processing in your job queue table. Set its default value to 0 for new records.

Selecting A Record: In a transaction separate from the one that processes jobs, select a record where processing is 0.

Update the Record: Make an update to this record, changing processing to 1 to indicate it is being processed.

Record Metadata: Optionally, store metadata such as the processing time, thread ID, or server information for further tracking or debugging.

Monitor Incomplete Jobs: Implement a monitoring mechanism that identifies jobs marked as processing for too long, allowing you to address any potential failures.

Benefits

Improved Performance: This approach avoids locking, allowing other workers to access records simultaneously, significantly boosting throughput.

Fault Tolerance: By tracking metadata, you can regain control over failed tasks easily.

Solution 2: Using a Non-Database Message Queue

Alternatively, you can step outside the confines of your MariaDB setup altogether by leveraging an external message queue to handle new records.

Steps of Implementation

Set Up a Message Queue: Implement a separate, non-DB message queue tool like RabbitMQ, Kafka, or similar to handle notifications of new jobs added to the database.

Trigger Notifications: Whenever a new job is added, the application can post a message to the message queue.

Process and Update: Workers listen to the queue, pick up messages, and then update the records in the database as they complete processing.

Advantages

No Database Locking: This separates the processing logic from the database, avoiding any locking on database queries altogether.

Concurrency: You can maintain high concurrency and horizontal scalability without worrying about database transaction locks.

Important Note

While using a non-database queue can be favorable, it's important never to rely solely on the message queue. Always maintain a database table to back your tracking and state management since it ensures no jobs are lost in the event of system failures when using a message queue.

Conclusion

Finding alternatives to FOR UPDATE SKIP LOCKED can lead to more performant architectures within MariaDB. Whether you choose to implement a processing column or opt for a separate message queue, each method provides unique benefits that can enhance your job processing capabilities. Remember, the essence of a good job queueing strategy lies in avoiding locking, maintaining high performance, and ensuring a robust monitor