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");
// ...do 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: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html