Using Transactions to Protect Data Integrity


Sometimes, you must submit several "related" change operations to a database that should be completed together or not at all. For example, if you're moving money from your savings account to your checking account, you perform operations that debit the savings account and credit the checking account in one "atomic" operation. Atomic operations are those that cannot be broken down into smaller parts and still maintain integrity of the database and its data. In an atomic operation, if one operation succeeds and the other fails, the system "creates" money. This is frowned upon, unless you happen to work in Washington, D.C., where they do this all the time.

SQL Server helps manage atomic operations by tying two or more operations together in a "transaction." If any one of the operations in the transaction fails for any reason, SQL Server automatically executes commands to undo or "roll back" the changes so the database is returned to the same state as before the operations.

SQL Server can also permit you to intentionally roll back changes made in your action command simply by adding appropriate Transaction commands to the T-SQL query. This is one way to experiment with "what if" queries or changes. That is, you can begin a T-SQL query with a BEGIN TRANSACTION to mark the point where subsequent operations are not to be committed (written) to the databaseat least, not until committed. After this point, you can code action commands to change the data and SELECT statements to return the changed data, including summary totals, other aggregates, or expressions based on the changes. Once the first set of operations is complete, you can execute COMMIT TRANSACTION to save these changes to the database or ROLLBACK TRANSACTION to reset the database state.

Clearly, transactions can be expensive, since SQL Server must juggle two data statesone before the transaction began and another afterwhich reflect the changes. Transactions can also lock regions of the database and prevent changes until they are committed or rolled back. It's for this reason that I recommend that you have a firm understanding of how to manage transactions and their side-effects before you start using them with wonton abandon in your T-SQL queries.

Transaction state is managed on a connection basis. That is, if you establish a connection and execute a "BEGIN TRANSACTION", from that point forward, any changes you make to the database are not written to the database, but saved so they can be committed at a later time. If you disconnect (or get disconnected) from the server (as when the new janitor unplugs the server to recharge his MPEG player), the database is left in a limbo state until it reboots. When you discover the SQL Server is down, you "counsel" the janitor about unplugging stuff and reboot the server. During its initialization process, SQL Server checks the transaction log for any uncommitted changes or pending transactions. Since your changes were never committed, the server assumes (correctly) that these changes should be rolled back, and it does so. If the server was partway through the process of writing a batch of "committed" changes to the hard disk when the server went down, SQL Server completes the commit process before returning control of the database to any operations. This way, the database integrity remains intactautomatically.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net