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
TABLESon 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
TABLESif 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
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 TABLESmay 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.