In general terms, a transaction is an action or series of actions that take the system from one consistent state to another. From the point of view of a developer who builds database-driven applications, a transaction can be thought of as an indivisible set of operations that brings the database from one consistent state to another.

This means that all the SQL statements within a transaction must complete successfully so that the entire transaction can be committed, making the changes made by all DML statements in the transaction permanent. Graphically, it might look like the following figure:

As you can see from the figure, the SQL statements composing the transaction take the data on which they operate from one consistent state to another. The transaction must be committed so that its effects can be applied to the database, thus bringing the data to the next consistent state. Otherwise, all the changes made by the SQL statements within the transaction are rolled back, thus bringing the data into the state that it was before the transaction took place.

If a severe error, such as a hardware failure, occurs during the transaction execution then the effects of the transaction are automatically rolled back. However, in some situations, you might want to manually roll back a transaction that has been successfully completed (but not yet committed), depending on a condition you specify. The following figure illustrates it graphically:

As you can see from the diagram, once all the statements composing the transaction have been successfully completed, you have the choice of either committing the transaction or rolling it back.

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. Any DBMS (database management system) that supports transactions must conform to the above characteristics. These are summarized in the following table:

The Oracle database supports all of the ACID properties listed in the table. So, when developing transactional applications on top of Oracle, you don't need to design custom schemas that will guarantee the consistency and integrity of your data; instead, it is always better to use Oracle transactions, thus letting the database worry about these problems.

This section provides a quick overview of Oracle transactions. For detailed information on how transactions work in Oracle see Oracle documentation: chapter Transaction Management in the Oracle Database Concepts manual.

In Oracle, you don't begin a transaction explicitly—it begins implicitly when you perform the first executable SQL statement. However, there are several situations that cause a transaction to end. The following table lists these situations:

As you can see in the table, a transaction is always either committed or rolled back, whether or not you commit it or roll it back explicitly.

However, note that it is always a good practice to explicitly commit or roll back transactions, rather than relying on the default behavior of Oracle. The fact is that the default transactional behavior of an application may vary depending on the tool the application uses to connect to Oracle.

For example, when it comes to PHP scripts that interact with Oracle via the OCI8 extension, you cannot rely on the fact that the active transaction on a connection will be automatically committed when you close that connection. In that case, the active transaction is rolled back when you close the connection, or when the script ends.

In contrast, if you disconnect from the database in SQL*Plus using a DISCONNECT statement, or connect as another user using a CONNECT statement, or close the SQL*Plus session with the help of the EXIT SQL*Plus command, then the active transaction on the connection will be committed.

As mentioned in the preceding section, in Oracle you can explicitly either commit a transaction or roll it back, using the COMMIT or ROLLBACK statements respectively. To perform these statements from PHP code, you don't need to use the oci_parse and oci_execute functions as you do it when it comes to performing other SQL statements, such as SELECT or INSERT. Instead, you use the oci_commit and oci_rollback OCI8 functions.

The following PHP script demonstrates how to explicitly commit or rollback a transaction from PHP when dealing with DML operations. What this script does is attempt to update those records in the employees table that represent employees whose job ID is ST_MAN (Stock Manager), increasing their salaries by 10 percent. If it fails to update one or more of these rows, then the entire transaction is rolled back, setting the updated salary fields back to their original values. The following steps summarize the process:

Now, let's look at the code for the script to see how the above steps can be implemented in PHP, using the OCI8 functions.

<?php
//File: trans.php
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$query = "SELECT count(*) num_rows FROM employees
WHERE job_id='ST_MAN'";

$stmt = oci_parse($dbConn,$query);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
oci_fetch($stmt);
$numrows = oci_result($stmt, 'NUM_ROWS'),
oci_free_statement($stmt);
$query = "UPDATE employees e
SET salary = salary*1.1
WHERE e.job_id='ST_MAN' AND salary*1.1
BETWEEN (SELECT min_salary FROM jobs j WHERE j.job_id=e.job_id)
AND (SELECT max_salary FROM jobs j WHERE j.job_id=e.job_id)";

$stmt = oci_parse($dbConn,$query);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Update failed: ' . $err['message'], E_USER_ERROR);
}
$updrows = oci_num_rows($stmt);

print "Tried to update ".$numrows." rows.<br />";
print "Managed to update ".$updrows." rows.<br />";
if ($updrows<$numrows) {
if (!oci_rollback($dbConn)) {

$err = oci_error($dbConn);
trigger_error('Failed to rollback transaction:
'.$err['message'], E_USER_ERROR);
}
print "Transaction is rolled back";
} else {
if (!oci_commit($dbConn)) {

$err = oci_error($dbConn);
trigger_error('Failed to commit transaction:
'.$err['message'], E_USER_ERROR);
}
print "Transaction is committed";
}
?>

In the above script, you define the query that will return the number of records representing stock managers. In the select list of the query, you use the count function to obtain the number of rows matching the criteria specified in the WHERE clause of the query. In this particular example, count(*) will return the number of records representing the employees whose job_id is ST_MAN.

In this example, you obtain the number of records representing stock managers from the result buffer, using the oci_fetch/oci_result function combination. You don't need to use a loop here because the query returns a single row containing only one field, namely num_rows.

Next, you perform the query that updates the salary column in the employees table, increasing salaries of stock managers by 10%. It updates the salary only if the value of the new salary is still between the minimum and maximum salary specified for the stock manager in the jobs table.

In this example, you execute the UPDATE statement in the OCI_DEFAULT execution mode. Doing so opens a transaction, which will allow you to explicitly commit or roll back the changes made by the UPDATE operation later in the script. It is interesting to note that the default execution mode is OCI_COMMIT_ON_SUCCESS in which the statement is committed automatically upon successful execution.

In the above script, you call the oci_num_rows function to get the number of rows affected by the UPDATE operation. Once you know the number of records representing stock managers and how many of them were actually updated, you can compare these numbers to find out if they are equal.

In this example, you simply roll back the transaction if the number of updated rows is less than the total number of records representing stock managers. This makes sense given that you don't want to have some stock managers' records updated and others not.

Having the changes rolled back in this situation is crucial—this makes it possible for you to make use of another script that will be able to update each stock manager record in a proper way. For example, in a real-world situation, you would probably want to set the salary of a stock manager to the maximum allowed value if a 10 percent raise exceeds that value.

If the UPDATE operation has affected all of the records representing stock managers, you commit the transaction with the help of the oci_commit function, making the changes made permanent.

Another thing to note here is the error handling mechanism used. If an error occurs during the execution of oci_rollback or oci_commit, you pass the connection identifier as the parameter to the oci_error function, which returns the error message describing the error that has occurred.

If you recall from Chapter 3, it is generally a good idea to have the key business logic of a PHP/Oracle application implemented inside the database. As discussed in that chapter, in simple cases, you don't even need to write PL/SQL code to move the data processing to the data—instead, you can design a complex SQL query that, when issued, instructs the database server to perform all the necessary data processing.

Turning back to the example discussed in the preceding section, you might modify the UPDATE statement used there so that it updates the records representing stock managers only if the new salary of each and every stock manager is still between the minimum and maximum salary specified for the stock manager in the jobs table, thus eliminating the need to perform a separate query that returns the number of stock manager records satisfying the above condition, and, therefore, reducing the amount of code that must be written to implement the desired behavior.

In essence, this new UPDATE combines all the three steps outlined at the beginning of the preceding section within a single statement. You don't even need now to explicitly commit or roll back the UPDATE operation. Instead, you can execute that UPDATE statement in the OCI_COMMIT_ON_SUCCESS mode, which guarantees that the operation is automatically committed upon successful execution, or rolled back otherwise.

The following script shows the new UPDATE statement in action:

<?php
//File: transQuery.php
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$jobno = 'ST_MAN';
$query = "
UPDATE (SELECT salary, job_id FROM employees WHERE
(SELECT count(*) FROM employees WHERE job_id=:jobid AND
salary*1.1 BETWEEN (SELECT min_salary FROM jobs WHERE
job_id=:jobid) AND
(SELECT max_salary FROM jobs WHERE
job_id=:jobid)) IN
(SELECT count(*) FROM employees WHERE job_id=:jobid)
) emp
SET emp.salary = salary*1.1
WHERE emp.job_id=:jobid";

$stmt = oci_parse($dbConn,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt, OCI_COMMIT_ON_SUCCESS)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
$updrows = oci_num_rows($stmt);
if ($updrows>0) {
print "Transaction is committed";
} else {
print "Transaction is rolled back";
}

?>

Here, you define the UPDATE statement that will update all the records representing stock managers, increasing their salaries by 10%, provided that each and every new salary doesn't exceed the maximum salary defined for the stock manager in the jobs table. If at least one new salary exceeds the maximum salary, the UPDATE statement will update no rows.

To achieve this functionality, rather than specifying the employees table in the dml_table_expression_clause of the UPDATE statement, you specify the SELECT statement that returns either all the records from the employees table or none of them, depending on whether or not all the records that satisfy the condition in the WHERE clause of the UPDATE statement (all records representing stock managers, in this case) can be updated so that the new salary in each of the records being updated does not exceed the maximum salary.

In this example, using the emp inline view in the UPDATE statement eliminates the need to separately perform the query that returns the number of records representing stock managers and then figure out whether that number is equal to the number of rows actually affected by the UPDATE statement. Now the script has to perform only one SQL statement to get the job done, thus reducing the script execution time significantly.

Also note how binding is performed in this example. You bind the jobno PHP variable to the jobid placeholder used in the UPDATE statement. It is interesting to note that the jobid placeholder appears in the statement more than one time.

Unlike the previous example where the UPDATE statement was executed in the OCI_DEFAULT mode, which explicitly opens a transaction, in this example you execute the statement in the OCI_COMMIT_ON_SUCCESS mode, thus committing the UPDATE operation automatically upon successful execution.

In the previous example, you still use the oci_num_rows function to obtain the number of rows affected by the UPDATE statement. However, this time you don't need to compare that number with the total number of records representing stock managers, as you did it in the preceding example. All you need to find out here is whether or not the number of rows affected by the UPDATE statement is greater than 0.

If the number of updated rows is greater than 0, this automatically means that the UPDATE operation has modified all the records representing stock managers and has been successfully committed. In this case, all you need to do is output a message informing the user that the transaction is committed.

If the number of updated rows is equal to 0, this means that the UPDATE operation did not affect any rows. In this case, all you have to do is to output a message informing the user that the transaction is rolled back. However, in reality the transaction has committed but no rows were affected by the UPDATE operation.

As you learned from the examples discussed earlier in this chapter, the oci_execute function allows you to execute an SQL statement in one of two modes—OCI_COMMIT_ON_SUCCESS mode and OCI_DEFAULT mode.

While statements are automatically committed when run in the OCI_COMMIT_ON_SUCCESS mode, you have to explicitly call oci_commit or oci_rollback to commit or roll back the transaction respectively, when specifying the OCI_DEFAULT mode.

However, it is interesting to note that a transaction created when a statement is executed in the OCI_DEFAULT mode may still be committed without calling oci_commit. To accomplish this, all you need to do is to execute a subsequent statement in the OCI_COMMIT_ON_SUCCESS mode.

The above technique can be applied when you're grouping two or more statements into a single transaction. To guarantee that the entire transaction will be rolled back when the execution of one of the statements within the transaction fails or you get results telling you that the transaction must be undone, you may simply stop the script execution by calling, say, the trigger_error function with E_USER_ERROR as the second parameter, thus rolling back the transaction without calling oci_rollback.

You may be wondering why we need a discussion on how to implicitly end an Oracle transaction from PHP, rather than explicitly calling oci_commit or oci_rollback. After all, the latter is the recommended method for ending transactions. Well, the main purpose of this discussion is to give you a better understanding of how Oracle transactions work in PHP scripts that interact with the database via the OCI8 extension.

The example discussed in this section uses the data structures that were defined in the An Example of When to Use a Stored Subprogram section in Chapter 3. However, before you proceed with the example, you need to alter these data structures as shown below. You can perform the SQL statements shown in the following listing via SQL*Plus when connected as usr/usr.

ALTER TABLE accounts
ADD (num_logons INT);
UPDATE accounts
SET num_logons = 0;
COMMIT;
DELETE logons;
ALTER TABLE logons
ADD CONSTRAINT log_time_day
CHECK (RTRIM(TO_CHAR(log_time, 'Day'))
NOT IN ('Saturday', 'Sunday'));

By issuing the ALTER TABLE statement in the above example, you add a num_logons column of INT to the accounts table. This column will accumulate the number of successful logons for each user account. For that, you will have to increase the number of logons stored in the num_logons field once the user is successfully authenticated.

Of course, you can still do without it, querying the logons table like this:

SELECT count(*) FROM logons WHERE usr_id='bob';

However, as the number of logons grows, the above would be a very expensive operation just to know how many logons a given user has performed.

Once you have added the num_logons column to the accounts table, you have to set the initial value for that column to 0. Alternatively, you might have issued the ALTER TABLE statement, using the DEFAULT clause for the num_logons column as follows:

ALTER TABLE accounts
ADD (num_logons INT DEFAULT 0);

In this example, you explicitly commit the transaction to make the changes made by the UPDATE operation permanent.

In the next step you delete all the rows in the logons table. This step is required to guarantee that the check constraint, which will be defined in the next step, is not violated. In this example, you may omit this step if the logons table contains no records created on Saturday or Sunday, and so the check constraint defined in the next step will not be violated. Otherwise, when trying to perform the ALTER TABLE, you will receive the following error message:

ERROR at line 2:
ORA-02293: cannot validate (USR.LOG_TIME_DAY) - check constraint violated

Here, you define the check constraint on the log_time column of the logons table. This constraint prevents inserting new rows into the logons table on Saturday or Sunday, which allows you to modify your authentication system so that it prevents each and every user from being able to log on on Saturdays and Sundays, thus allowing users to log on only on the working days. Later, you can always drop this constraint by issuing the following statement:

ALTER TABLE logons DROP CONSTRAINT log_time_day;

Turning back to the ALTER TABLE statement shown in the preceding page, note the use of the format'Day' specified as the second parameter of the TO_CHAR function. This format tells the TO_CHAR function to convert a date stored in the log_time field to a day of the week. Then, you use the NOT IN operator to exclude Saturdays and Sundays from the list of allowed days.

Bear in mind that in this case Oracle uses case-sensitive matching. So, if you have specified'Day' as the second argument of the TO_CHAR function, then you have to specify the days of the week in the expression list to the right of the NOT IN operator like this:'Saturday', 'Sunday'. It would be'SATURDAY', 'SUNDAY' if you have specified'DAY' as the second parameter of TO_CHAR.

Now that you have modified all the required database structures as needed, you can proceed with the example whose intent is to illustrate how to create a transaction by executing a DML statement in the OCI_DEFAULT mode and then how to implicitly end that transaction by executing the subsequent statement in the OCI_COMMIT_ON_SUCCESS mode.

In reality, of course, you might want to have more than just two statements in a transaction. To accomplish this, you might execute all of the statements (except for the last one) that you want to group into a single transaction in the OCI_DEFAULT mode, and then execute the last statement in the OCI_COMMIT_ON_SUCCESS mode to close the transaction.

Graphically, it might look like the following:

The following script demonstrates how the architecture shown in the figure can be implemented in PHP. Note that unlike the login function discussed in the An Example of When to Use a Stored Subprogram section in Chapter 3, the login function shown below stops execution and returns false when it fails to insert an audit record into the logons table. This makes sense, since you now insert a new record into the logons table not only to save the information about a logon, but to check if the inserted data adhere to the business rule, which says that no row in the logons table can contain a date whose day of the week is Saturday or Sunday in the log_time column.

<?php
//File: userLoginTrans.php
function login($usr, $pswd) {
if(!$rsConnection = oci_connect('usr', 'usr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$query = "SELECT full_name, num_logons FROM accounts
WHERE usr_id = :userid AND pswd = :passwd";
$stmt = oci_parse($rsConnection,$query);
oci_bind_by_name($stmt, ':userid', $usr);
oci_bind_by_name($stmt, ':passwd', $pswd);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
}
if (!$arr = oci_fetch_array($stmt, OCI_ASSOC)) {
print "Wrong user/password combination";
return false;
}
$num_logons=$arr['NUM_LOGONS']+1;
oci_free_statement($stmt);
$query = "UPDATE accounts SET num_logons = num_logons + 1";
$stmt = oci_parse($rsConnection,$query);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Update failed: '
. $err['message'], E_USER_WARNING);
return false;
}
oci_free_statement($stmt);
$query = "INSERT INTO logons VALUES (:userid, SYSDATE)";
$stmt = oci_parse($rsConnection,$query);
oci_bind_by_name($stmt, ':userid', $usr);
if (!oci_execute($stmt, OCI_COMMIT_ON_SUCCESS)) {
$err = oci_error($stmt);
trigger_error('Insertion failed: ' . $err['message'], E_USER_WARNING);
if ($err['code']=='02290'){
print "You cannot connect on Saturday or Sunday";
}
return false;
}
print "Hello, ".$arr['FULL_NAME']."<br/>";
print "You have visited us ".$num_logons." time(s)";
session_start();
$_SESSION['user']=$usr;
return true;
}
?>

As mentioned earlier, the num_logons column in the logons table holds the number of successful logons for each user account. In the script, you define the UPDATE statement that will increase the value of the num_logons field in the record representing the user whose credentials are being used for authentication.

By executing the statement in the OCI_DEFAULT mode, you create a new transaction. This makes sense, since you may need to roll back the changes made by this UPDATE operation if the subsequent insert into the logons table fails.

If the UPDATE operation fails, you exit the login function, returning false to the calling script. This tells the calling script that the authentication has failed.

Next, you define the INSERT statement that is executed once a user has been successfully authenticated and the counter of his or her successful logons has been incremented.

Executing the INSERT statement in the OCI_COMMIT_ON_SUCCESS mode in the script guarantees that the transaction will be committed on success or rolled back on failure, which means that either both the changes made by the INSERT and the effects of the UPDATE statement become permanent or both are undone.

If you recall, the oci_error function returns an associative array of two elements, namely, code, which contains the Oracle error code, and message, which contains the message string describing the error. In this particular example, you check to see if the Oracle error code is equal to 02290. If so, this indicates that a check constraint violation error occurred. Since you have only one check constraint defined on the logons table (the one that prevents inserting new rows into the logons table on Saturdays and Sundays), you may inform the user that he or she cannot connect on Saturday and Sunday.

In this example, if the INSERT fails, you exit the login function with false, thus telling the calling script that the authentication has failed. In the case of successful authentication, you take appropriate actions, such as displaying a welcome message and creating a new session.

Now, to see the newly created login function in action, you might use the following simple script:

<?php
//File: testLoginTrans.php
require_once "userLoginTrans.php";
if (login('bob','pswd')) {
if (isset($_SESSION['user'])) {
print '<p>'.'Your account name: '.$_SESSION['user'].'</p>';
} else {
print '<p>'.'Session variable representing the account name
is not set'.'</p>';
}
}else {
print '<p>'.'Authentication failed'.'</p>';
}
?>

If you run the testLoginTrans.php script shown in the above listing on Saturday or Sunday, you should see the following output:

You cannot connect on Saturday or Sunday
Authentication failed

On a working day, however, the output should be as follows:

Hello, Bob Robinson
You have visited us 1 time(s)
Your account name: bob

Each subsequent execution of the testLoginTrans.php script on a working day should increase the number of Bob Robinson's visits. However, if you execute the script on Saturday or Sunday, it will not increase that number. This proves that everything works as expected.

Now that you have a working transactional solution implemented mainly in PHP, it is time to think of how to minimize the amount of PHP programming, moving some of the business logic of the application to the database.

CREATE OR REPLACE TRIGGER logons_insert
BEFORE INSERT
ON logons
FOR EACH ROW
BEGIN
UPDATE accounts
SET num_logons = num_logons + 1
WHERE usr_id = :new.usr_id;
END;
/

$query = "UPDATE accounts SET num_logons = num_logons + 1";
$stmt = oci_parse($rsConnection,$query);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Update failed: ' . $err['message'],
E_USER_WARNING);
return false;
}
oci_free_statement($stmt);

CREATE OR REPLACE TRIGGER logons_insert
BEFORE INSERT
ON logons
FOR EACH ROW
BEGIN
UPDATE accounts
SET num_logons = num_logons + 'str'
WHERE usr_id = :new.usr_id;
END;
/

Authentication failed

SELECT count(*) FROM logons;

CREATE OR REPLACE TRIGGER logons_insert
BEFORE INSERT
ON logons
FOR EACH ROW
BEGIN
UPDATE accounts
SET num_logons = num_logons + 'str'
WHERE usr_id = :new.usr_id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

Hello, Bob Robinson
You have visited us 3 time(s)
Your account name: bob

CREATE OR REPLACE TRIGGER logons_insert
BEFORE INSERT
ON logons
FOR EACH ROW
BEGIN
UPDATE accounts
SET num_logons = num_logons + 'str'
WHERE usr_id = :new.usr_id;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'Failed to update the counter'),
END;
/

Authentication failed

SET num_logons = num_logons + 1

If you recall from Chapter 2, section OCI8 Functions for Connecting to Oracle, the OCI8 PHP extension offers three different functions when it comes to establishing a connection to an Oracle database. These are the oci_connect, oci_new_connect, and oci_pconnect functions. These functions differ only in the type of connections they establish to the database.

Both oci_connect and oci_pconnect use a database connection cache, thus eliminating the cost of opening a database connection on every request. The difference between the two is that the connections created by oci_connect are released when the script execution ends, while oci_pconnect's connections persist across script executions.

Unlike oci_connect and oci_pconnect, the oci_new_connect function doesn't use a connection cache, always returning a fresh new connection. You use this function when you need to create two or more concurrent transactions within a script. The following example shows the oci_new_connect function in action.

In the following script, note the use of the oci_new_connect function to create a concurrent transaction. While you create the first connection in the script by oci_connect, you use oci_new_connect to create a fresh new connection.

<?php
//File: newConns.php
function select_emp_job ($conn, $jobno) {
$query = "SELECT employee_id, first_name, last_name, salary
FROM employees WHERE job_id =:jobid";
$stmt = oci_parse($conn,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Query failed: '
. $err['message'], E_USER_ERROR);
};
print '<table border="1">';
while ($emp = oci_fetch_array($stmt, OCI_ASSOC)) {
print '<tr>';
print '<td>'.$emp['EMPLOYEE_ID'].'</td>';
print '<td>'.$emp['FIRST_NAME'].'&nbsp;'.$emp['LAST_NAME'].
'</td>';
print '<td>'.$emp['SALARY'].'</td>';
print '</tr>';
}
print '</table>';
}
if(!$conn1 = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: '
. $err['message'], E_USER_ERROR);
};
if(!$conn2 = oci_new_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: '
. $err['message'], E_USER_ERROR);
};
$jobno = 'AD_VP';
$query = "UPDATE employees SET salary = 18000 WHERE job_id=:jobid";
$stmt = oci_parse($conn1,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
print "<h2>Transaction isolation testing!</h2>";
print "<h4>Transaction A on conn1:</h4>";
print "<p>(results after the update and before
the commit on conn1)</p>";
select_emp_job($conn1, $jobno);
print "<h4>Transaction B on conn2:</h4>";
print "<p>(results after the update and before
the commit on conn1)</p>";
select_emp_job($conn2, $jobno);
if (!oci_commit($conn1)) {
$err = oci_error($conn1);
trigger_error('Failed to commit transaction: '
.$err['message'], E_USER_ERROR);
}
print "<h4>Transaction B on conn2:</h4>";
print "<p>(results after the update and after
the commit on conn1)</p>";
select_emp_job($conn2, $jobno);
$query = "UPDATE employees SET salary = 17000 WHERE job_id=:jobid";
$stmt = oci_parse($conn1,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
?>

The following figure illustrates the output of this script. As you can see from the figure, the changes made by the UPDATE operation performed within the transaction on the conn1 database connection can be seen within that transaction immediately after the UPDATE takes place, and cannot be seen from the concurrent transaction on the conn2 connection until the first transaction is committed.

Turning back to the code for the newConns.php script discussed here, note that all the SQL statements used in this script are executed in the OCI_DEFAULT mode. This guarantees that the transactions are committed immediately.

Note that you create the first connection in this script with oci_connect. Since it's the first connection, the connection cache associated with the script is empty and so oci_connect will establish a fresh new connection to the database.

Then, to create a fresh, new, transactionally isolated connection in the script, you call oci_new_connect.

By executing the UPDATE statement in the OCI_DEFAULT mode on the conn1 connection, you create a transaction on this connection.

After the UPDATE has been executed, the effects of this operation can be seen for any other operation performed within the same transaction. To prove this point, you select the rows affected by the UPDATE within the same transaction, before it is committed. As seen from the previous figure, a SELECT returns the new values of the rows being updated.

However, you still will see the original values of the rows being updated when performing a SELECT within a concurrent transaction. This is because simultaneous transactions are isolated from the updates made by other uncommitted transactions. Once the transaction is committed, all its effects can be seen from the other transactions.

Finally, with the help of this UPDATE, you restore the original values of the updated rows.

As a developer, you have two primary concerns when designing an application that will modify database data in a multi-user environment:

While Oracle provides a broad set of features that can help you accomplish the above goals, it is your responsibility to make the correct use of these features. The following sections focus on some concurrent update issues that may be encountered in multi-user environments when using transactions incorrectly.

<?php
//File: updateSleep.php
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$jobno = 'AD_VP';
$query = "
UPDATE employees
SET salary = salary*1.1
WHERE job_id=:jobid";
$stmt = oci_parse($dbConn,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
$updrows = oci_num_rows($stmt);
print 'You just updated '.$updrows. ' row(s)'.'<br/>';
sleep(20);
oci_rollback($dbConn);
print 'Transaction is rolled back';
?>

Lost Updates

The preceding example demonstrated how a badly designed transactional application may lock database resources for long periods of time, preventing other concurrent transactions from accessing those resources in a timely manner. However, note that using a non‑blocking approach while modifying database data in a multi-user environment, may cause another issue—lost updates. To understand what a lost update problem is, consider the steps that an interactive application usually performs when it comes to modifying information stored in a database:

• Selects the data from the database

• Displays the data to the user

• Waits for feedback from the user

• Updates the data in the database

It should be fairly obvious from the above scenario that while the application waits for feedback from the user, another user may change the data. Then, if the first user proceeds to update the data, the changes made by the second user will be lost.

This may be better understood with the help of an example. Let's look at the updateQuickForm.php script that implements the above steps using PEAR's HTML_QuickForm package. When you run the script, it performs the following steps:

• Updates two rows in the employees table

• Produces the form asking the user to commit or roll back the changes

• Ends execution, rolling back the changes

On the form that the script produced, the user can choose either commit or roll back and then click the Submit button. Once the user clicks Submit, it invokes the script again, which this time performs the following steps:

• Updates the same two rows in the employees table

• Commits or rolls back the changes depending on the user's choice

• Ends execution

The following figure shows the form produced by the script.

However, before you run the updateQuickForm.php script, make sure you install the HTML_QuickForm PEAR package. Since the HTML_QuickForm depends on another package, called HTML_Common, you first have to install the latter. Given that you have the PEAR Installer installed and configured, you might issue the following command to download and install the HTML_Common package:

$ pear install HTML_Common

Once HTML_Common is installed, you can download and install the HTML_QuickForm package as follows:

$ pear install HTML_QuickForm

With all that done, you can run the updateQuickForm.php script whose code is shown below:

<?php
//File: updateQuickForm.php
require_once 'HTML/QuickForm.php';
if(!$dbConn = oci_connect('hr', 'hr', '//localhost/orcl')) {
$err = oci_error();
trigger_error('Could not establish a connection: ' .
$err['message'], E_USER_ERROR);
};
$jobno = 'AD_VP';
$query = "
UPDATE employees
SET salary = salary*1.1
WHERE job_id=:jobid";
$stmt = oci_parse($dbConn,$query);
oci_bind_by_name($stmt, ':jobid', $jobno);
if (!oci_execute($stmt, OCI_DEFAULT)) {
$err = oci_error($stmt);
trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
};
print '<h2>Update confirmation!</h2>';
$updrows = oci_num_rows($stmt);
$frm=new HTML_QuickForm('frm1', 'POST'),
$frm->addElement('header','msg1','You just updated '.$updrows. '
row(s). Do you want to commit changes?'),
$grp[] =& HTML_QuickForm::createElement('radio', null,
null,'commit', 'C'),
$grp[] =& HTML_QuickForm::createElement('radio', null,
null,'rollback', 'R'),
$frm->addGroup($grp, 'trans'),
$frm->setDefaults(array('trans' => 'C'));
$frm->addElement('submit','submit','Submit'),
if(isset($_POST['submit'])) {
if ($_POST['trans']=='C'){
oci_commit($dbConn);
print 'Transaction committed';
} elseif ($_POST['trans']=='R'){
oci_rollback($dbConn);
print 'Transaction rolled back';
} else {
$frm->display();
}
} else {
$frm->display();
}
?>

Looking at the form shown in the previous figure, you may think that once the script has updated two rows in the employees table, it waits for user feedback, keeping the transaction active. In fact, it works in a different way.

When you run the script, it actually updates two rows in the employees table, but then it rolls back the transaction. Doing so allows you to count the number of rows affected by the UPDATE, giving that information to the user. Once the user has selected either commit or rollback and then pressed the Submit button, the script performs the same UPDATE operation again, either committing the effects of the UPDATE or rolling them back, depending on the user's choice.

The advantage of the above technique is that the rows being processed are not blocked while the user is deciding which radio button to select: commit or rollback, thus allowing other transactions to operate on these rows during this time. However, this may cause another problem—a lost update, as outlined earlier in this section.

To prevent a lost update problem, you might use an optimistic locking strategy, making sure that the values of the fields being updated have not changed since the user began working with them.

Continuing with the preceding example, you might want to record attempts made to update the rows of the employees table. To accomplish this, you will need to create a table to hold audit records as well as a BEFORE UPDATE trigger on the employees table, which will insert a record into the audit table whenever someone updates a row in employees table.

To set up the above data structures, you can execute the following SQL statement:

CONN usr/usr
CREATE TABLE emp_updates(
emp_id NUMBER(6),
job_id VARCHAR2(10),
timedate DATE);
CONN /AS SYSDBA
GRANT INSERT on usr.emp_updates TO hr;
CONN hr/hr
CREATE OR REPLACE TRIGGER emp_updates_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id, :new.job_id, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'An error raised in the trigger'),
END;
/

With that done, you may run the updateQuickForm.php script discussed in the preceding section to check if your auditing mechanism works as expected. In the form generated by the script, choose rollback and then click Submit. Now, if you count the number of rows in the emp_updates table as follows:

CONN usr/usr;
SELECT * FROM emp_updates;

you should see that the emp_updates table still contains no rows:

no rows selected

This indicates that when you roll back an UPDATE operation, it rolls back its audit record as well. It is the expected behavior, since you cannot roll back some effects of a transaction—you can either commit all effects of it or roll them all back.

An attempt to commit only the INSERT statement performed within the trigger will fail because no transaction control statements are allowed in a trigger. So, if you try to recreate the emp_updates_trigger trigger as follows:

CREATE OR REPLACE TRIGGER emp_updates_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id,
:new.job_id, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/

You will get the following errors when running the updateQuickForm.php script:

Warning: oci_execute()[function.oci-execute]:ORA-04092: cannot ROLLBACK in a trigger ORA-06512: at "HR.EMP_UPDATES_TRIGGER", line 6 ORA-04092: cannot COMMIT in a trigger ORA-04088: error during execution of trigger 'HR.EMP_UPDATES_TRIGGER'
Fatal error: Query failed: ORA-04092: cannot ROLLBACK in a trigger ORA-06512: at "HR.EMP_UPDATES_TRIGGER", line 6 ORA-04092: cannot COMMIT in a trigger ORA-04088: error during execution of trigger 'HR.EMP_UPDATES_TRIGGER'

One way to solve the above problem is to make use of an autonomous transaction.

Employing an autonomous transaction in this example will allow you to commit the INSERT performed within the emp_updates_trigger trigger independently of the transaction created in the updateQuickForm.php script, thus creating a record in the emp_updates table even if the effects of the UPDATE operation that fired the trigger are rolled back.

The following example shows how to recreate the emp_updates_trigger trigger so that it uses an autonomous transaction.

CREATE OR REPLACE TRIGGER emp_updates_trigger
BEFORE UPDATE
ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO usr.emp_updates VALUES (:new.employee_id,
:new.job_id, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/

The above example shows an autonomous transaction implemented in a database trigger. Using an autonomous transaction here ensures that an audit record will be created in the emp_updates table, regardless of whether an UPDATE operation on the employees table is committed or rolled back.

To test the newly created trigger, run the updateQuickForm.php script again and submit the form produced by the script, having the rollback radio button selected. Then, select the emp_updates again as follows:

SELECT * FROM emp_updates;

This time, you should see results that might look like the following:

EMP_ID JOB_ID TIMEDATE
------- ---------- ---------
101 AD_VP 29-MAY-06
102 AD_VP 29-MAY-06
101 AD_VP 29-MAY-06
102 AD_VP 29-MAY-06

Note that although you attempted to update only two rows in the employees table, four audit records have been inserted into the emp_updates table. Recall that the updateQuickForm.php script actually performs the UPDATE twice—first, in order to count the number of rows to be updated, and then, to actually update these rows.