|
|
In the case of database transactions, the group of actions that you attempt to execute (a process that either succeeds or fails—the all-or-nothing proposition) are SQL statements, and the system that needs to be kept in a consistent state is the database itself.
Luckily enough, SQL-99 has support for transactions, as do the major database systems. Otherwise, you would need to manually enforce the ACID rules, which would be a tough job!
In order to implement transactions, databases keep log files with everything that happens inside the transaction. When rolling back the transaction, all the successful operations are reversed based on the data in the log files, and the affected data is brought back to its previous state.
| Note |
The way log files work is considered an advanced topic and will not be covered in this book. Moreover, because each database system has its own particularities regarding this subject, you would be best off consulting the documentation for your database platform. |
SQL Server, Oracle, DB2, and MySQL do support transactions and the ACID rules. Access doesn't support transactions.
Transactions work differently with each Relational Database Management System (RDBMS), but there are some concepts and steps common to all transactions. We'll describe how transactions work and then give specific examples for each RDBMS, discussing the particularities of each database platform.
Transactions must have a clearly defined start and end point. The point the transaction starts is very important—it's the point you can roll back to in case some failure or problem occurs in any of its constituent SQL statements. In respect to the consistency rule, at the moment a new transaction is created, the data must be in a consistent state.
The SQL-99 standard specifies the START TRANSACTION statement, which should mark the point at which a new transaction starts, but this command isn't implemented in the database platforms covered in this book.
Oracle and DB2 start transactions automatically. In other words, a transaction automatically begins as soon as you execute the first SQL statement. With SQL Server, you use the BEGIN TRANSACTION statement, which can also accept a transaction name, and with MySQL you simply use BEGIN. You'll see more details in the examples later in this chapter.
Whether the statements execute correctly will determine whether the transaction is committed or rolled back. You'll usually need a mechanism that can test whether all the SQL statements ran without problems so that you can decide how to end the transaction (to commit it if everything went okay or to roll back if it didn't go okay).
Implementing a testing mechanism is important because, by default, the transaction is not rolled back if a noncritical error occurs. Let's take another look at the checking/savings accounts example:
UPDATE Checking SET Balance = Balance - 1000 WHERE Account = 'Sally'; UPDATE Savings SET Balance = Balance + 1000 WHERE Account = 'Sally';
If these two statements are part of a transaction, and one of them generates an error because it can't be executed successfully, the transaction will most likely not be rolled back by default. Instead, you need to manually test whether each statement performed successfully; if either of them didn't, you can roll back the transaction. Also, if everything runs okay, you need to manually to commit the transaction.
In this chapter's examples, you'll see how to test if any of the statements generated errors and how to react to them.
As you saw earlier, when problems occur inside the transaction, you can roll it back to its starting point or to a savepoint if the database system supports savepoints (we'll cover these in more detail a little later).
The SQL-99 syntax for rolling back transactions is as follows:
ROLLBACK [WORK] [TO SAVEPOINT savepoint_name]
This structure is fully supported by Oracle, DB2, and partially by MySQL, which doesn't support savepoints.
The SQL Server syntax is a bit different, but it serves the same purpose:
What's important to keep in mind is that rolling back a transaction brings the data it has affected to its previous state and closes the transaction, but the execution of the batch continues normally. For example, if you have a ROLLBACK statement in the middle of a stored procedure, the execution doesn't stop at ROLLBACK (so ROLLBACK isn't like a RETURN or a GOTO command, and it doesn't move the execution pointer). If you want the stored procedure to stop executing when you do a ROLLBACK, you need to manually handle this—you'll see how to accomplish this in the upcoming examples.
If all of the SQL commands in the transaction execute successfully (or in a way you consider to be okay), you issue a COMMIT command. This tells the database to persist the changes made by the transaction in the database. From this moment, the changes can't be undone using a ROLLBACK command.
The SQL-99 syntax for committing transactions is as follows:
COMMIT [WORK]
This syntax is supported by all major database vendors, but some of them also accept additional parameters. SQL Server supports COMMIT WORK but also has a COMMIT TRANSACTION command that accepts as a parameter the name of the transaction to be committed (SQL Server supports having transaction names).
You'll now look at some features that aren't supported by all database systems covered in this book or that are supported differently. First, we cover the concepts, and then we'll show how to apply them in a few examples.
A database that runs in autocommit mode will treat every SQL query as a separate transaction, without needing any additional SQL commands such as BEGIN, COMMIT, or ROLLBACK. After a SQL data modification statement is executed, the changes are automatically committed (so the results are considered final) by the database system.
| Note |
SQL Server and MySQL work by default in autocommit mode. |
With a database that works in autocommit mode, these two statements will be considered as two separate transactions:
UPDATE Checking SET Balance = Balance - 1000 WHERE Account = 'Sally'; UPDATE Savings SET Balance = Balance + 1000 WHERE Account = 'Sally';
While in autocommit mode, if you want to start a transaction formed by more than one SQL command, you must use a BEGIN command and then manually finish the transaction with either ROLLBACK or COMMIT. So if you want to have a single transaction containing the previous two SQL statements, you need to do something like this:
BEGIN WORK UPDATE Checking SET Balance = Balance - 1000 WHERE Account = 'Sally'; UPDATE Savings SET Balance = Balance + 1000 WHERE Account = 'Sally'; COMMIT WORK
However, note that here you didn't do any checking to see whether either of the two UPDATE statements executed successfully. In a real-world example, you'd need to implement some error handling mechanism and ROLLBACK the transaction in case an error happens.
If the database isn't in autocommit mode, it's said to be in automatic-transactions mode.
| Note |
Oracle and DB2 work by default in automatic-transactions mode. |
In this mode, a new transaction starts automatically with the first SQL query you type, but it's not automatically committed. In other words, the database system works as if you had already typed a BEGIN command and then waits for you to call COMMIT or ROLLBACK to finish the transaction. With a database working in automatic-transactions mode, you can integrate the previous two UPDATE commands in a transaction like this:
UPDATE Checking SET Balance = Balance - 1000 WHERE Account = 'Sally'; UPDATE Savings SET Balance = Balance + 1000 WHERE Account = 'Sally'; COMMIT WORK
SQL Server and MySQL work by default in autocommit mode while Oracle and DB2 work by default in automatic-transactions mode, but in all cases the default mode can be changed (although most developers prefer to keep the default mode of the database system).
For Oracle, you change the default mode with SET AUTOCOMMIT ON/OFF. With SQL Server you use SET IMPLICIT_TRANSACTIONS ON/OFF, and with MySQL you use SET AUTOCOMMIT=0/1. When working with DB2, you can handle this by selecting Command Center Ø Options from the main menu of the Command Center tool and then checking or unchecking the Automatically Commit SQL Statements box in the Execution tab as appropriate (see Figure 10-1).
| Caution |
It's possible that your data access provider (for example, JDBC or OLEDB) will be working in autocommit mode. If you want to issue SQL statements in automatic-transactions mode, then you may have to alter the default behavior of your provider as well. |
In general, because of the isolation property of transactions, other users will not be able to see your changes if you forget to send a COMMIT command. Be sure to always issue COMMIT statements after an update, insert, or delete when working in automatic-transactions mode.
A savepoint acts as a bookmark inside a transaction. You can roll back any actions that have been performed by a transaction to a certain savepoint, without actually closing the transaction. Rolling back to a certain savepoint brings the database to the status it was when the savepoint was created, but it doesn't end the transaction. A final ROLLBACK or COMMIT is still required.
| Note |
Savepoints are supported by Oracle, DB2, and SQL Server. MySQL supports transactions but doesn't support savepoints. |
SQL-99 specifies the SAVEPOINT command to create savepoints, and this command is implemented in Oracle and DB2. Because you can have multiple savepoints in a single transaction, you always need to supply a savepoint name:
SAVEPOINT <savepoint name>
SQL Server uses SAVE TRANSACTION instead of SAVEPOINT:
SAVE TRANSACTION <savepoint name>
This is how you would add a savepoint to the bank transactions example using the SQL Server syntax:
BEGIN TRANSACTION SAVE TRANSACTION BeforeChangingBalance UPDATE Checking SET Balance = Balance - 1000 WHERE Account = 'Sally'; ROLLBACK TRANSACTION BeforeChangingBalance UPDATE Savings SET Balance = Balance + 1000 WHERE Account = 'Sally'; COMMIT TRANSACTION
After executing this batch, Sally will have $1,000 more in her account. The first UPDATE statement is rolled back (but the transaction still remains active!), and finally the transaction is committed.
We'll see examples with SQL Server, Oracle, and DB2 savepoints in the following sections.
|
|
