PHP Database Writing

Writing Operations

• Insertion
• Deletion
• Update

Sample Update Code

• update.php
• redirect.php (solves the reload problem, also LOCKs and UNLOCKs tables)
• update2.php
• db.inc

Typical Insertion Process

1. The user enters data into a form
2. The data is validated and then written to the SQL database using the INSERT statement
3. A receipt page is displayed

Typical Deletion Process

1. The user selects the record to delete from a form
2. The data is validated and then an appropriate DELETE statement is issued to the SQL database
3. A receipt page is displayed

Typical Update Process

1. Appropriate data is read from the database
2. The data is presented to the user for modification in a form
3. The data is validated and then written to the SQL database using the UPDATE statement
4. A receipt page is displayed

Concurrency Issues

• Lost update problem
• Dirty read problem
• Incorrect summary problem
• Unrepeatable read problem

No Lock Required

• Simple queries that don't use the results of a previous SELECT or data entered by the user
• Single user applications

Lock Required (Multi-User Applications)

• A script first reads a value from a database and later writes the value to a database
• A script first writes a value to a database and later reads it

Rules for Locks

1. Obtain a write lock if user needs to perform a write that is susceptible to concurrency issues
2. Obtain a read lock if user needs to perform a read that is susceptible to concurrency issues
3. If a lock is needed, all tables must be locked in a single LOCK statement
4. All locks must be released in a single UNLOCK statement

MySQL Example

LOCK TABLES table1 READ, table2 WRITE;

... issue MySQL statements ... 

UNLOCK TABLES;

Using Auxiliary Tables to Minimize Locks

LOCK TABLES identifiers WRITE;  // identifiers is an auxiliary table

SELECT id FROM identifiers;     // id is the primary key being managed

UPDATE identifiers SET id = id + 1;

UNLOCK TABLES;

INSERT INTO mainTable VALUES (id, ... );

Laboratory

Here is the link to today's assignment. If you are a Montana State student, demonstrate it during today's lab period. If you are a University of Leipzig student, either demonstrate it during today's lab period or e-mail the two relevant files to Sebastian (loewe AT uni-leipzig.de) no later than the end of the day.