How to implement locking across a server farm?

Question

Are there well-known best practices for synchronizing tasks across a server farm? For example if I have a forum based website running on a server farm, and there are two moderators trying to do some action which requires writing to multiple tables in the database, and the requests of those moderators are being handled by different servers in the server farm, how can one implement some locking functionality to ensure that they can't take that action on the same item at the same time?

So far, I'm thinking about using a table in the database to sync, e.g. check the id of the item in the table if doesn't exsit insert it and proceed, otherwise return. Also probably a shared cache could be used for this but I'm not using this at the moment.

Any other way?

By the way, I'm using MySQL as my database back-end.

Answer 1

If all the state is in the (central) database then the database transactions should take care of that for you.

See http://en.wikipedia.org/wiki/Transaction_(database)

Answer 2

You may want to investigate a distributed locking service such as Zookeeper. It's a reimplementation of a Google service that provides very high speed distributed resource locking coordination for applications. I don't know how easy it would be to incorporate into a web app, though.

Answer 3

Having a table of locks is a OK way to do it is simple and works.

You could also have the code as a Service on a Single Server, more of a SOA approach.

You could also use the the TimeStamp field with Transactions, if the timestamp has changed since you last got the data you can revert the transaction. So if someone gets in first they have priority.

Answer 4

Your question implies data level concurrency control -- in that case, use the RDBMS's concurrency control mechanisms.

That will not help you if later you wish to control application level actions which do not necessarily map one to one to a data entity (e.g. table record access). The general solution there is a reverse-proxy server that understands application level semantics and serializes accordingly if necessary. (That will negatively impact availability.)

It probably wouldn't hurt to read up on CAP theorem, as well!