Php – How to design an efficient moderation system for comments

ajaxasyncdatabase-designjqueryPHP

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!

Best Answer

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

Related Topic