Databases
StackOverflow2013
Postsdbo
POIs using a mySQL column a sane way to achieve global locking?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs using a mySQL column a sane way to achieve global locking?
    text
    copied!<p>I'm building an application that involves significant image processing. We're building it in a distributed manor, with an arbitrary number of rendering machines receiving requests to render images from a RabbitMQ exchange.</p> <p>Because a request for an image might come in while a rendering is already in process, and because I don't want to have two render servers duplicating work, I created a column in the mySQL images table called is_rendering as a boolean. </p> <p>When a render server receives a request for render it follows a series of steps that looks like this:</p> <ol> <li>Select for update the image row</li> <li>If is_rendering == true abort render request</li> <li>Set is_rendering == true and commit the transaction</li> <li>Render the image and store the thumbnail in a globally accessible store</li> <li>Set is_rendering == false and return</li> </ol> <p>It definitely works, but I'm worried that these frequent database updates seem a little bit silly. Furthermore I'm considering the edge case where a render server fails in the middle of a rendering and leaves is_rendering == true, preventing that image from ever being rendered. The solution I'm considering to this problem is to change the is_rendering column from a tinyint(1) to a datetime field, and storing the date of locking as the "true" value, and null as the "false" value. A periodic service health check could select all rows with a is_rendering value past a certain time period and release the lock in this case.</p> <p>Is this a sane approach to this problem, or are there other more elegant approaches I should be considering? </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload