Locking methods that don't work in web database applications

There are several locking paradigms that don't work in a web database application because of the statelessness of HTTP. Each approach fails, because there is either no guarantee or no possibility that the locked tables will be unlocked. If tables are locked indefinitely, other transactions can't proceed, and the DBMS will most likely need to be shut down and restarted.

Be careful with locking in web database applications. Remember the basic rule that all locks should be unlocked by the same script during the same execution of the script.

All web scripts that require locking should have the structure lock, query, update, and unlock. There must be no user interaction or intervening calls to other scripts that require input.

The following approaches to transactions and locking in a web database application should be avoided:

  • Failing to issue an UNLOCK TABLES on a locked persistent database connection opened with mysql_pconnect( ). The locks aren't released when the script terminates, and there is no guarantee that the script will be run in the future or that the same persistent connection will be used again.

    It isn't necessary to issue an UNLOCK TABLES if a nonpersistent connection opened with mysql_connect( ) is used. Locks are automatically released when the script finishes. However, it is good practice to include the UNLOCK TABLES statement.

  • Locking one or more tables during the first execution of a script, then querying or updating during a second or subsequent execution of the script. Remember that each database connection in a script is independent and is treated as a different user by MySQL. Such an approach queries and updates without locks unless, by chance, the same persistent connection that issued the locks is reused. A subsequent UNLOCK TABLES may fail.

  • Retrieving a value such as the next available primary key value, presenting this to the user, waiting for the user to enter further details, and then adding a row to the database with that identifier. Remember that another user may add a row while the first user is entering the required details, and locks should never be carried across several scripts or different executions of the same script.