Database Reference
In-Depth Information
Figure 15.4
Read-Only
Transactions
Prevent Database
Changes.
example, you run a summary of sales from the beginning of the year up to
today and then (in the same transaction) run a detail report of sales activity.
If users are updating the SALES table between your first and second
reports, the two reports will not match. Use a read-only transaction to pre-
serve the state of the database when you begin the first report.
Note:
Setting read-only transactions can cause serious concurrency issues
for other users. Applications will not be able to respond properly when
other users preserve data for exclusive use. This type of activity is inadvis-
able because it could upset end users (your clients).
The default transaction setting is READ WRITE, which allows
changes and sees other users' changes immediately after being committed.
The current transaction can be completed using the COMMIT or
ROLLBACK commands.
Other options are transaction isolation levels, which can be set to SERI-
ALIZABLE or READ COMMITTED. The default mode is ISOLATION
LEVEL READ COMMITTED, where SQL will wait until any locks on
data it wants to modify are released. Using the SET TRANSACTION ISO-
LATION LEVEL SERIALIZABLE command, SQL commands handle
locking differently. If a problem is encountered, the SERIALIZABLE
option will cause a transaction to fail immediately without waiting. This
can be useful in a batch job that runs overnight, where it is preferable to
stop the entire batch job as opposed to risking the overnight job spilling
over into daytime hours.
