VISUAL QUICKSTART GUIDE SQL Second Edition [Electronic resources] نسخه متنی

Executing a Transaction

To learn how transactions work, you need to learn a few terms:

Commit .

Committing a transaction makes all data modifications performed since the start of the transaction a permanent part of the database. After a transaction is committed, all changes made by the transaction become visible to other users and are guaranteed to be permanent if a crash or other failure occurs.

Roll back .

Rolling back a transaction retracts any of the changes resulting from the SQL statements in the transaction. After a transaction is rolled back, the affected data are left unchanged, as though the SQL statements in the transaction were never executed.

Transaction log .
The

transaction log file, or just

log, is a serial record of all modifications that have occurred in a database via transactions. The transaction log records the start of each transaction, the changes to the data, and enough information to undo or redo the changes made by the transaction (if necessary later). The log grows continually as transactions occur in the database.

Although it's the DBMS's responsibility to ensure the

physical integrity of each transaction, it's your responsibility to start and end transactions at points that enforce the

logical consistency of the data, according to the rules of your organization or business. A transaction should contain only the SQL statements necessary to make a consistent changeno more and no fewer. Data in all referenced tables must be in a consistent state before the transaction begins and after it ends.

When you're designing and executing transactions, some important considerations are:

• Transaction-related SQL statements modify data, so your database administrator might need to grant you permission to run them.

• Transaction processing applies to statements that change data or database objects (INSERT, UPDATE, DELETE, CREATE, ALTER, DROPthe list varies by DBMS). For production databases, every such statement should be executed as part of a transaction.

• A committed transaction is said to be

  durable, meaning that its changes remain in place permanently, persisting even if the system fails.

• A DBMS's data-recovery mechanism depends on transactions. When the DBMS is brought back online following a failure, the DBMS checks its transaction log to see whether all transactions were committed to the database. If it finds uncommitted (partially executed) transactions, it rolls them back based on the log. You must resubmit the rolled-back transactions (although some DBMSes can complete unfinished transactions automatically).

• A DBMS's backup/restore facility depends on transactions. The backup facility takes regular snapshots of the database and stores them with (subsequent) transaction logs on a backup disk. Suppose that a crash damages a production disk in a way that renders the data and transaction log unreadable. You can invoke the restore facility, which will use the most recent database backup and then execute, or

  roll forward, all

  committed transactions in the log from the time the snapshot was taken to the last transaction preceding the failure. This restore operation brings the database to its correct state before the crash. (Again, you'll have to resubmit uncommitted transactions.)

• For obvious reasons, you should store a database and its transaction log on separate physical disks.

For a transaction to be executed in all-or-nothing fashion, the transaction's boundaries (starting and ending points) must be clear. These boundaries let the DBMS execute the statements as one atomic unit of work. A transaction can start

implicitly with the first executable SQL statement or

explicitly with the START TRANSACTION statement. A transaction ends explicitly with a COMMIT or ROLLBACK statement (it never ends implicitly). You can't roll back a transaction after you commit it.

Oracle and DB2 transactions always start implicitly, so those DBMSes have no statement that marks the start of a transaction. In Microsoft Access, Microsoft SQL Server, MySQL, and PostgreSQL, you can (or must) start a transaction explicitly by using the BEGIN statement. SQL:1999 introduced the START TRANSACTION statementlong after these DBMSes already were using BEGIN to start transactions, so the extended BEGIN syntax varies by DBMS. MySQL and PostgreSQL support START TRANSACTION (as a synonym for BEGIN).

To start a transaction explicitly

To commit a transaction




To roll back a transaction




Figure 14.2. Result of Listing 14.1. The results of the SELECT statements show that the DBMS cancelled the transaction.




SUM(pages) AVG(price)
---------- ----------
5107    18.3875 
SUM(pages) AVG(price)
---------- ----------
0    36.7750 
SUM(pages) AVG(price)
---------- ----------
5107    18.3875 

Listing 14.1. Within a transaction block, UPDATE operations (like INSERT and DELETE operations) are never final. See Figure 14.2 for the result.






Listing 14.2. Use a transaction to delete publisher P04 from the table publishers and delete P04's related rows in other tables.






Specifying a Foreign Key with FOREIGN KEY" in Chapter 11.)

ACID ACID is an acronym that summarizes the desirable properties of a transaction: Atomicity . Either all of a transaction's data modifications are performed, or none of them are. Consistency . A completed transaction leaves all data in a consistent state that maintains all data integrity. Isolation . A transaction's effects are isolated from those of all other transactions. See the sidebar "Concurrency Control" earlier in this chapter. Durability . After a transaction completes, its effects are permanent and persist even if the system fails.

Tips




• Don't forget to end transactions explicitly with either COMMIT or ROLLBACK. A missing endpoint could lead to huge transactions with unpredictable results on the data or, on abnormal program termination, rollback of the last uncommitted transaction. Keep your transactions as small as possible because they can lock rows, entire tables, indexes, and other resources for their duration. COMMIT or ROLLBACK releases the resources for other transactions.

• You can nest transactions. The maximum number of nesting levels depends on the DBMS.

• It's faster to UPDATE multiple columns with a single SET clause than to use multiple UPDATEs. For example, the query

  
  UPDATE mytable
  SET col1 = 1
  col2 = 2
  col3 = 3
  WHERE col1 <> 1
  OR col2 <> 2
  OR col3 <> 3;
  

  is better than three UPDATE statements because it decreases logging (although it increases locking).

• By default, DBMSes run in

  autocommit mode unless overridden by either explicit or implicit transactions (or turned off with a system setting). In this mode, each statement is executed as its own transaction. If a statement completes successfully, the DBMS commits it; if the DBMS encounters any error, it rolls back the statement.

• For long transactions, you can set arbitrary intermediate markers, called

  savepoints, to divide a transaction into smaller parts. Savepoints let you roll back changes made from the current point in the transaction to a location earlier in the transaction (provided that the transaction hasn't been committed). Imagine a session in which you've made a complex series of uncommitted INSERTs, UPDATEs, and DELETEs and then realize that the last few changes are incorrect or unnecessary. You can use savepoints to avoid resubmitting every statement.

  Microsoft Access doesn't support savepoints. For

  Oracle, DB2, MySQL, and

  PostgreSQL, use the statement

  
  SAVEPOINT 
  savepoint_name; 
  

  For

  Microsoft SQL Server, use the statement

  
  SAVE TRANSACTION 
  savepoint_name ;
  

  See your DBMS documentation for information about savepoint locking subtleties and how to COMMIT or ROLLBACK to a particular savepoint.

• In

  Microsoft Access, you can't execute transactions in a SQL View window or via DAO; you must use the Microsoft Jet OLE DB Provider and ADO.

  Oracle and

  DB2 transactions begin implicitly. To run Listings 14.1 and 14.2 in Oracle and DB2, omit the statement BEGIN TRANSACTION;.

  To run Listings 14.1 and 14.2 in

  MySQL, change the statement BEGIN TRANSACTION; to START TRANSACTION; (or to BEGIN;).

  MySQL supports transactions through InnoDB and BDB tables; search the MySQL documentation for

  transactions .

  Microsoft SQL Server, Oracle, MySQL, and

  PostgreSQL support the statement SET TRANSACTION to set the characteristics of the upcoming transaction.

  DB2 transaction characteristics are controlled via server-level and connection initialization settings.