User's Guide
PART 2. Relational Database Concepts
CHAPTER 15. Using Transactions and Locks
Adaptive Server Anywhere supports transaction processing, which ensures that logically related commands are executed as a unit. Transactions are fundamental to maintaining the accuracy of your data. Transactions are essential to data recovery in the event of system failure and to the successful interweaving of commands from concurrent users.
All developers must be concerned with correctness and concurrency. Even single-user databases must be protected against data loss and may have multiple applications connected to them, or may have multiple connections from a single application. Understanding transactions will allow you to make better use of the facilities provided by Sybase Adaptive Server Anywhere database engines.
Transactions and locks are both needed to ensure that the data stored in a database is entered accurately and stays correct. You will no doubt wish to guard even a small personal database against corruption. If instead you are building or administering a database for a large corporation, it may contain the work of a thousand people and contain records, such as customer information, vital to the livelihood of the corporation.
To ensure data integrity it is essential that you can identify states in which the information in your database is stable and consistent. Regular backups are essential, but you must be sure that the backup contains a consistent set of information if it is to be of any use at a later date. You don't want to make a backup when through someone's changes are only half inserted.
Likewise, the identification of consistent states is essential to managing the work of concurrent users of your database. The database engine must be able to execute the commands of multiple users simultaneous to give all prompt service. Identifying distinct pieces of work facilitates the task of interleaving the commands of the various users.
A transaction is a logical unit of work. Each is a sequence of logically related commands which accomplish one task and transform the database from one consistent state into another.
Transactions are atomic. Adaptive Server Anywhere executes all the statements within a transaction as a unit. At the end of each transaction, you commit your changes to make them permanent. If for any reason all the commands in the transaction do not process properly, then any intermediate changes are undone, or rolled back.
Transactions are key to both the management of concurrent users and to the protection of the database from media and system failures. Transactions break the work of each user into small blocks. These blocks may be safely interleaved and the completion of each block marks a point at which the information is self-consistent
For information about database backups and data recovery, see Backup and Data Recovery.
For further information about concurrent database usage, see Introduction to concurrency
Suppose you worked for a bank and wished to transfer $1000 between two people's accounts. You can accomplish the transfer by completing the following two operations:
Debit the first person's account.
Credit the second person's account.
Under ordinary circumstances, these two operations will work perfectly, but suppose that while you were in the middle of transferring the money, the database system suddenly failed. You hope that either both commands were executed and the money was transferred successfully, or that neither command was executed. If only the first command completed, then the information in the database would be inconsistent because $1000 would be missing. It is unacceptable to leave the debit in the database without recording the credit. Either both the debit and the credit must be processed, or neither. In case of failure, the debit needs to be undone.
Transaction processing ensures that each transaction is processed in its entirety or not at all. Transaction processing is fundamental to ensuring that a database contains correct information. It addresses two distinct, yet related, problems: data recovery and database consistency.
In the case of the transfer of funds, you can guard against corrupting the bank records by grouping all the necessary commands in one transaction.
Using transactionsAdaptive Server Anywhere expects you to group your commands into transactions. Knowing which commands or actions signify the start or end of a transaction lets you take full advantage of this feature.
Transactions start with one of the following events:
The first statement following a connection to a database
The first statement following the end of a transaction
Transactions complete with one of the following events:
A COMMIT statement makes the changes to the database permanent.
A ROLLBACK statement undoes all the changes made by the transaction.
A statement with a side effect of an automatic commit is executed: Database definition commands, such as ALTER, CREATE, COMMENT, and DROP all have the side effect of an automatic commit.
A disconnection from a database performs an implicit rollback.
Interactive SQL provides you with two options which let you control when and how transactions end:
If you set the option AUTO_COMMIT to ON, Interactive SQL automatically commits your results following every successful statement and automatically perform a ROLLBACK after each failed statement.
The setting of the option COMMIT_ON_EXIT controls what happens to uncommitted changes when you exit Interactive SQL. If this option is set to ON (the default), Interactive SQL does a COMMIT; otherwise it undoes your uncommitted changes with a ROLLBACK statement.
Adaptive Server Anywhere also supports Transact-SQL commands, such as BEGIN TRANSACTION, for compatibility with Sybase Adaptive Server Enterprise. For further information, see Transact-SQL Compatibility
You may identify important states within a transaction and return to them selectively using SAVEPOINTS. These are discussed further in section Savepoints within transactions.
Transactions and data recoverySuppose that a system failure or power outage suddenly takes your database engine down. Adaptive Server Anywhere is carefully designed to protect the integrity of your database in such circumstances. It provides you with a number of independent means of restoring your database. For example, it provides you with a log file which you may store on a separate drive so that should the system failure damage one drive, you still have a means of restoring your data.
In such circumstances, transaction processing allows the database server to identify states in which your data is in a consistent state. Transaction processing ensures that if, for any reason, a transaction is not successfully completed, then the entire transaction is undone, or rolled back. The database is left entirely unaffected by failed transactions.
Adaptive Server Anywhere's transaction processing ensures that the contents of a transaction are processed securely, even in the event of a system failure in the middle of a transaction.
For a detailed description of data recovery mechanisms, see chapter Backup and Data Recovery.
The remainder of this chapter is devoted to concurrency and consistency of transactions.