Here's the job I want to do:

My project is a website where there will be a lot of comments. Those comments will be moderated: a moderator will connect, see comments and accept or refuse them.

I need those things:

  • the comments won't have any "moderator" associated. Which implies that when a moderator connects, the system "assigns" the 20 first comments to this moderator then sends them via an AJAX/JSON request
  • of course, this implies that those comments musn't be assigned to other moderators

How can I make a design so that I'll be sure "getting the 20 first comments for a moderator" can be an atomic operation? And there's another thing to think of (I don't know if it may have an impact or not on my problem): if there are say 10 moderators connected, one of them has 20 comments to validate, and this guy leaves his PC for the whole day. I may implement kindof a "timeout" for this moderator, then, when he tries to validate, a message will tell him "sorry this comment has already been moderated by another moderator", and the AJAX script will get a "fresh new 20 comments).

My first idea was the simplest idea:

  • a moderator connects
  • a jQuery AJAX script asks the server for 20 comments
  • the server (1) makes immediately a request of the 20 first free comments to the database
  • the server (2) "freezes" the 20 free comments
  • the server (3) makes up a JSON response and sends it

But the operations (2) and (3) are not atomic, what happens if, in-between, another operator asks for the 20 first free comments? They both will get the 20 first free comments. That's not a good solution. I think this is a classical problem of atomic operation on db. Any idea how to design this (or where to look)?

Thank you very much indeed!

  • Maybe I should do something like dev.mysql.com/doc/refman/5.0/en/update.html UPDATE => assign 20 first comments to moderator (a) then SELECT the 20 first comments that belong to moderator (a) AND which have no decision yet (I didn't know you could make an UPDATE with a LIMIT clause) – Olivier Pons Oct 15 '11 at 6:58
  • This sounds like more of a database/model question than a comment-system-design question. – TehShrike Oct 15 '11 at 7:23
  • I've bought "Building Web Reputation Systems" - when you're building sites like this, you really should take advantage of lessons learned. – MSalters Oct 15 '11 at 23:21
  • Why 20? Seems like you could put 10 on one screen. They could all be flagged for approval by default (if most comments tend to be approved) with a single click to submit. You should indicate who approved if you want to run any analysis of poor approvers. – JeffO Oct 17 '11 at 0:53
  • The number is not important. It could be 5 on my Sony Ericsson, or 40 on my huge 16:9 screen ;) The problem is about concurrency. Thank you for pointing this out. – Olivier Pons Oct 17 '11 at 6:54

You're planning to deal with much concurrent activity, so that means you've got to think in terms of atomic operations. That means using transactions.

Now, you don't want to hold a transaction for a long time as it blocks other transactions, so you have to think about using the transactions to protect minimal state changes and putting guards on the UPDATE operations so that only the things that are supposed to change actually do so.

What would you be wanting to UPDATE? Well, the first step has got to be to introduce an association between the comment and the moderator to whom it is assigned, but you've also got to set a time after which the moderation becomes invalid (so that a separate cleanup job can know to reset the association and let some other moderator have a go). Conceptually then, you're doing:

For the start of moderation:

  • In one transaction:
    • Pick 20 comments that are not assigned to a moderator.
    • For each of those, assign them to the “current” moderator and set an expiry time (e.g., in 15 minutes).

On performance of a moderation action for one comment:

  • In one transaction:
    • See whether the comment is still assigned to the “current” moderator.
    • See whether the expiry time has been reached.
    • If both conditions are true, update the comment with its new moderation status and remove the association with the moderator.
    • If either is false, moan!

As a separate cleanup task (performed every X seconds):

  • In one transaction:
    • Go through the table of assigned comments, and if the expiry time on an entry is in the past, remove the assignment.

Given the above, you'll probably want to have the relation between comment IDs, moderators and the expiry times in a separate table that is indexed by comment ID (which might even be reasonably the primary key).

  • Thank you, you gave me enough material to finish my admin panel. Thanks for your answer! – Olivier Pons Oct 16 '11 at 7:11

In the past when I have had to make moderation or admin panels i make a CMS that is made just for working on that website. The reason I do this is simple. It is annoying to try to build to correct admin interface to work with a new site It takes time and you dont wanna have to bring down the main site everytime you make a change to the admin panel. Another reason is the way you look at data on a site verse admin'ing it is different. I would go with Donal Fellows method and maybe in itws own cms.


And when I say i put it in its own cms i mean the new cms is only an admin panel. So there is no site just a admin panel.

  • Thank you for your answer. I've already done almost everything but the JSON exchange between the "moderators" and the server. I was looking for a good solution, a Donal gave me enough material to finish my admin panel. Thanks for your answer! – Olivier Pons Oct 16 '11 at 7:10
  • anytime :) good luck with your work let me +1 your questin if i have not already – WojonsTech Oct 16 '11 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.