MySQL: Prevent Two PHP Scripts from Overwriting One Another’s Table Updates CodeUnit 23 JUN 2010

From day one, the problem with database-driven applications is of course that the potential for two separate scripts to overwrite data being used by one another when run simultaneously is pretty huge.

One of the main ways of dealing with this is to use “locks”, basically allowing one script to lock a particular set of tables, meaning that other scripts can’t make use of those tables until the locks are released.

Using locks with MySQL and PHP is pretty simple if we are keeping things straightforward, and the implementation goes something like this:

mysql_query("LOCK TABLES `mytable` WRITE");
// all your insert queries on `mytable` here...
mysql_query("UNLOCK TABLES");

And it’s as simple as that.

There are two major types of locks available to you, namely WRITE and READ, where WRITE indicates that you wish to read and write to the table and that no one else should be allowed to either alter or read the data while you hold the lock. (Whilst a table is in a WRITE locked state, other requests block until the lock is released).

A READ lock indicates that you wish to read a table but not write to it, and thus by implication means that multiple scripts can all hold a READ lock at once – in fact a script can still read the READ locked table without first requiring a lock at all!

Of course, this is simply scratching the complexity which can be delved into when it comes to table locking, but for a straightforward simple implementation to ensure your precious data isn’t being overwritten when you are busy with a table, the above works perfectly fine!

In other words, nice! :)

Related Link:

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.

  • xxxbunker dot com

    great explanation.

    the whole read / write is a little counter intuitive as i initially though ‘write’ meant block all write request, and ‘read’ meant all read request.

    this straightened that out.