Скачать или смотреть How to Update a Random Row and Retrieve It in MySQL

Discover an easy solution to efficiently update a random row in your MySQL database and fetch the result while tackling concurrency issues.
---
This video is based on the question https://stackoverflow.com/q/63914144/ asked by the user 'malefstro' ( https://stackoverflow.com/u/13477683/ ) and on the answer https://stackoverflow.com/a/63914414/ provided by the user 'Akina' ( https://stackoverflow.com/u/10138734/ ) 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: update a random row and get the row result back in MySQL

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.
---
Introduction

In the ever-evolving world of database management, developers often find themselves needing to efficiently retrieve and update records. A common requirement is to fetch a random unused record from a table, mark it as used, and return it to the application layer. However, when multiple threads or connections are involved, this can become tricky due to possible race conditions.

In this guide, we will delve into a practical solution to the problem of updating a random row in MySQL while managing concurrency effectively. Let’s begin by examining the structure of our database and what we aim to accomplish.

Problem Overview

You have a table structure for product codes (pcodes) that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

The used column indicates whether a product code is still available (0 for unused, 1 for used). The challenge arises when multiple requests try to fetch an unused code simultaneously, leading to potential conflicts.

Desired Solution

We want the following workflow:

Select a random unused code for a specified product_id.

Mark that code as used.

Return the code information back to the application.

Steps to Accomplish the Task

To achieve our goal effectively, consider the following steps:

Select and Update in One Transaction: Utilize a method that allows you to select an unused code and update its status in a single operation.

Handle Concurrency: Implement a mechanism to avoid conflicts when multiple threads are trying to access the same resource.

Error Handling: Account for potential failures that could leave unused codes marked as selected.

Implementation

The following solution uses a stored procedure approach without defining multiple threads directly:

[[See Video to Reveal this Text or Code Snippet]]

Key Considerations

Prevent Indefinite Loops: To make sure you don’t get stuck in an infinite loop, you should have a counter that breaks the REPEAT cycle after a certain number of attempts.

Handling Connections: Be aware that a disconnection during the process can leave a code locked under a specific connection ID. To rectify this, implement a service procedure executed by an Event Scheduler that resets the used field for codes associated with non-active connections back to zero.

Conclusion

By following this method, you can efficiently fetch a random unused code, mark it, and return it in a robust way that handles multiple requests and potential errors. This approach enhances the reliability and performance of your database operations while catering to the needs of concurrent access.

Implementing this solution can save time and reduce complexity in managing your database transactions. Happy coding!